Test1, Sql Server - rextester

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

λ

                                      .NET NoSQL database for rapid development