Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Test1
CREATE TABLE sec_Login( UserName varchar(50) NOT null, SalesId int NOT null, description VARCHAR(50) NULL); CREATE TABLE ventven( dtfact DATETIME NOT NULL, CODREP int NOT Null, nofact int NOT NULL, codnat varchar(1) NULL ); INSERT INTO sec_Login VALUES ('user1', 1, 'user1'); INSERT INTO sec_Login VALUES ('user2', 2, 'user2'); INSERT INTO sec_Login VALUES ('user3', 3, 'user3'); INSERT INTO ventven VALUES('2015/01/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/01/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/01/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/01/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/01/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/01/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/02/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/02/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/02/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/02/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/02/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/02/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/03/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/03/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/03/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/03/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/03/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/03/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/04/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/04/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/04/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/04/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/04/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/04/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/05/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/05/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/05/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/05/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/05/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/05/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/06/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/06/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/06/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/06/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/06/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/06/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/07/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/07/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/07/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/07/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/07/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/07/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/08/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/08/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/08/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/08/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/08/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/08/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/09/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/09/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/09/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/09/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/09/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/09/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/10/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/10/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/10/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/10/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/10/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/10/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/11/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/11/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/11/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/11/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/11/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/11/01', 3, 0,'G'); INSERT INTO ventven VALUES('2015/12/01', 1, 1,'G'); INSERT INTO ventven VALUES('2015/12/01', 1, 0,'G'); INSERT INTO ventven VALUES('2015/12/01', 2, 1,'G'); INSERT INTO ventven VALUES('2015/12/01', 2, 0,'G'); INSERT INTO ventven VALUES('2015/12/01', 3, 1,'G'); INSERT INTO ventven VALUES('2015/12/01', 3, 0,'G'); select DATEADD(m, DATEDIFF(m, 0, dtfact), 0) as [Date], (select s.userName from sec_Login s where s.SalesID=v.CODREP ) As Username, count(v.nofact) as [COR encoded], --(convert(float,count(v.nofact))/(sum(count(v.nofact)) over (partition by DATEADD(m, DATEDIFF(m, 0, dtfact), 0)))) as Fraction convert(float,count(v.nofact))/(SELECT t1.rowNumber FROM(SELECT DISTINCT DATEADD(m, DATEDIFF(m, 0, a.dtfact), 0) AS dt, ( SELECT COUNT(*) FROM ventven AS counter WHERE counter.dtfact = a.dtfact AND counter.dtfact >= a.dtfact ) AS rowNumber FROM ventven a ) t1 where t1.dt = DATEADD(m, DATEDIFF(m, 0, v.dtfact), 0) ) AS Fraction2 from ventven v where v.codnat='G' group by CODREP,DATEADD(m, DATEDIFF(m, 0, dtfact), 0) order by [Date],Username;
run
|
edit
|
history
|
help
0
af
Microsoft SQL Server T-SQL in 10 mn ~ Lesson 13 Subqueries vers. #5
Hack_this
Tucha Alexander DB QA Test Tasks Answers
FIGURA5.1
service_delivery_task
Calculation of a status life duration
db_hotel29102020
index
Usage of joins