clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249293 fiddles created (32219 in the last week).

drop table if exists Attndate; create table Attndate (EMPCODE int ,EMPNAME varchar(30) ,[DATE] date ,InTime time ,LateMins int); insert into AttnDate (EMPCODE, EMPNAME,[DATE],InTime,LateMins) values (1,'John' ,'08/01/2017','9:17', 7), (2,'David','08/01/2017','9:18', 8), (3,'Kumar','08/01/2017','9:12', 2), (4,'Jack' ,'08/01/2017','9:18', 8), (1,'John' ,'08/26/2017','9:27',17), (2,'David','08/26/2017','9:20',10), (3,'Kumar','08/26/2017','9:17', 7), (4,'Jack' ,'08/26/2017','9:13', 3), (5,'Rose' ,'08/28/2017','9:31',21), (2,'David','08/29/2017','9:28',18), (3,'Kumar','08/30/2017','9:23',23), (4,'Jack' ,'08/31/2017','9:59',49), (1,'John' ,'09/01/2017','9:30',20), (2,'David','09/01/2017','9:25',15), (3,'Kumar','09/01/2017','9:12', 2), (4,'Jack' ,'09/01/2017','9:15', 5), (5,'Rose' ,'09/02/2017','9:36',26), (2,'David','09/02/2017','9:18', 8), (3,'Kumar','09/02/2017','9:13', 3), (4,'Jack' ,'09/02/2017','9:51',41), (1,'John' ,'09/03/2017','9:30', 4), (3,'Kumar','09/03/2017','9:30',13);
22 rows affected
 hidden batch(es)


with rollup as (select EMPCODE, EMPNAME, day([DATE]) as dom, sum(LateMins) as lm from Attndate where [Date] between '2017-08-01' and '2017-09-30' group by EMPCODE, EMPNAME, day([DATE])) select EMPCODE, EMPNAME, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31], (select sum(r2.lm) from rollup r2 where r2.EMPCODE=pt.EMPCODE) as [Total Late Mins] from (select EMPCODE, EMPNAME,lm,dom from rollup r1) as dt pivot (sum(lm) for dom in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) ) as pt order by 1,2
EMPCODE EMPNAME 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Total Late Mins
1 John 27 4 17 48
2 David 23 8 10 18 59
3 Kumar 4 3 13 7 23 50
4 Jack 13 41 3 49 106
5 Rose 26 21 47
 hidden batch(es)