## Problem

 Author Message immaduddinahmed Old Hand Group: General Forum Members Points: 385 Visits: 420 SELECT eid,ename,remarks,CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SpendtimeFROM(SELECT g.eid,e.ename,remarks,DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedateFROM attend_log g left join employee e on g.eid=e.eidwhere g.eid=17090 and date >'20130101' and date <'20130131'group by g.eid,e.ename,g.remarks)ti want this type of resulteid------ename----------late---------spendtime17090----abc-------------2-----------163:54if remark show 2 lates then late column show 2 Bala' Right there with Babe Group: General Forum Members Points: 750 Visits: 644 Send sample data ..With create table and insert ..specify the business rules Kingston Dhasian SSChampion Group: General Forum Members Points: 11353 Visits: 5331 You have been here for some time now.I expect you to help us with some DDL of the tables involved, some sample data and the expected results based on the sample data.This will help us test our solutions before we post it in the thread and will thereby save a lot of our timeIf you are not sure on how to do this, please read the link in my signature Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Modenhttp://www.sqlservercentral.com/articles/Best+Practices/61537/ immaduddinahmed Old Hand Group: General Forum Members Points: 385 Visits: 420 sir that problem is solve by my selfthe big problem is that this is my querySELECT EID,NAME,Late,Halfday,--Absent,--OFFDAY,CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIMEFROM(SELECT g.EID,e.ename [NAME],sum(case when g.Remarks = 'Late' then 1 else 0 end) as LATE,sum(case when g.Remarks = 'HALF DAY' then 1 else 0 end) as [LATE AND HALFDAY],--sum(case when g.Remarks = 'OFF DAY' then 1 else 0 end) as OFFDAY,DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedateFROM attend_log g left join employee e on g.eid = e.eidwhere g.eid=17074 and date >'20130101' and date <'20130131' group by g.eid,e.ename)ti want this type of resulteid-------name--late--halfday------spendtime-----absent----offday17074----abc-----3------1----------204:35----------------1------------4but in my data absent and off days are not definedthis is my attend_log data sampleeid--------------date---------------------------------spendtime---------------------remarks17074-----2013-01-01 00:00:00.000------1900-01-01 09:25:00.000--------null17074-----2013-01-02 00:00:00.000------1900-01-01 08:50:00.000--------null17074-----2013-01-03 00:00:00.000------1900-01-01 09:15:00.000--------late17074-----2013-01-04 00:00:00.000------1900-01-01 04:45:00.000--------halfday17074-----2013-01-05 00:00:00.000------1900-01-01 09:25:00.000--------late17074-----2013-01-08 00:00:00.000------1900-01-01 09:14:00.000--------null17074-----2013-01-09 00:00:00.000------1900-01-01 09:55:00.000--------lateu can see sir that 6 and 7 dates is not showingbecouse 6 jan was sunday and on 7th jan employee was absentthats the problem i hope u under stand thanks for the help Bala' Right there with Babe Group: General Forum Members Points: 750 Visits: 644 You can calculate the absent details by the code below, then you can incorporate your code `CREATE TABLE Attend_log (Eid INT,EName VARCHAR(50),LogDate DATETIME,SpendTime Datetime,Remarks VARCHAR(50))INSERT INTO Attend_log (Eid,EName,LogDate,SpendTime,Remarks)SELECT '17074','ABCEmployee','2013-01-01 00:00:00.000','1900-01-01 09:25:00.000','null' UNIONSELECT '17074','ABCEmployee','2013-01-02 00:00:00.000','1900-01-01 08:50:00.000','null' UNIONSELECT '17074','ABCEmployee','2013-01-03 00:00:00.000','1900-01-01 09:15:00.000','late' UNIONSELECT '17074','ABCEmployee','2013-01-04 00:00:00.000','1900-01-01 04:45:00.000','halfday' UNIONSELECT '17074','ABCEmployee','2013-01-05 00:00:00.000','1900-01-01 09:25:00.000','late' UNIONSELECT '17074','ABCEmployee','2013-01-08 00:00:00.000','1900-01-01 09:14:00.000','null' UNIONSELECT '17074','ABCEmployee','2013-01-09 00:00:00.000','1900-01-01 09:55:00.000','late' SELECT * FROM Attend_log;WITH AllDateCTEAS (SELECT Eid,EName,LogDate,SpendTime,Remarks FROM Attend_logUNION ALL SELECT Eid,EName,DATEADD(DD,-1,LogDate) AS LogDate,NULL AS SpendTime,CONVERT(VARCHAR(50),'Absent' )as Remarks FROM AllDateCTEWHERE DATEADD(DD,-1,LogDate) NOT IN ( SELECT LogDate FROM Attend_log)AND DATEADD(DD,-1,LogDate) BETWEEN '2013-01-01 00:00:00.000' and '2013-01-09 00:00:00.000'AND DATENAME(weekday,DATEADD(DD,-1,LogDate)) <> 'SUNDAY' )SELECT * FROM AllDateCTEORDER BY LogDate` immaduddinahmed Old Hand Group: General Forum Members Points: 385 Visits: 420 SIR ITS GIVING ME THE SAME RESULT I DID SOME CHANGES;WITH AllDateCTEAS (SELECT Eid,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074UNION ALL SELECT Eid,DATEADD(DD,-1,Date) AS Date,NULL AS SpendTime,CONVERT(VARCHAR(50),'Absent' )as Remarks FROM AllDateCTE WHERE DATEADD(DD,-1,Date) NOT IN ( SELECT Date FROM Attend_log )AND DATEADD(DD,-1,Date) BETWEEN '2013-01-01 00:00:00.000' and '2013-01-09 00:00:00.000'AND DATENAME(weekday,DATEADD(DD,-1,Date)) <> 'SUNDAY' )SELECT * FROM AllDateCTEORDER BY Date Bala' Right there with Babe Group: General Forum Members Points: 750 Visits: 644 I meant ,the previous select will add the absent rows alone you have to include your calculation as below , `;WITH AllDateCTEAS (SELECT Eid,EName,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074UNION ALL SELECT Eid,EName,DATEADD(DD,-1,Date) AS Date,NULL AS SpendTime,CONVERT(VARCHAR(50),'Absent' )as Remarks FROM AllDateCTE WHERE DATEADD(DD,-1,Date) NOT IN ( SELECT Date FROM Attend_log )AND DATEADD(DD,-1,Date) BETWEEN '2013-01-01 00:00:00.000' and '2013-01-09 00:00:00.000'AND DATENAME(weekday,DATEADD(DD,-1,Date)) <> 'SUNDAY' )SELECT EID,EName,Late,[HALFDAY],Absent,--OFFDAY,CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIMEFROM(SELECT g.EID,EName ,sum(case when g.Remarks = 'Late' then 1 else 0 end) as LATE,sum(case when g.Remarks = 'HALF DAY' then 1 else 0 end) as [HALFDAY],sum(case when g.Remarks = 'Absent' then 1 else 0 end) as Absent,DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedateFROM AllDateCTE g where g.eid=17074 and date >'20130101' and date <'20130131' group by g.eid , EName )t` immaduddinahmed Old Hand Group: General Forum Members Points: 385 Visits: 420 sir its not giving me off day we have only one off day and that day is sunday Bala' Right there with Babe Group: General Forum Members Points: 750 Visits: 644 `;WITH AllDateCTEAS (SELECT Eid,EName,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074UNION ALL SELECT Eid,EName,DATEADD(DD,-1,Date) AS Date,NULL AS SpendTime,CASE WHEN DATENAME(weekday,DATEADD(DD,-1,Date)) ='SUNDAY' THEN CONVERT(VARCHAR(50),'OffDay' )ELSE CONVERT(VARCHAR(50),'Absent' )END as Remarks FROM AllDateCTE WHERE DATEADD(DD,-1,Date) NOT IN ( SELECT Date FROM Attend_log )AND DATEADD(DD,-1,Date) BETWEEN '2013-01-01 00:00:00.000' and '2013-01-09 00:00:00.000')SELECT EID,EName,Late,[HALFDAY],Absent,OffDay,CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIMEFROM(SELECT g.EID,EName ,sum(case when g.Remarks = 'Late' then 1 else 0 end) as LATE,sum(case when g.Remarks = 'HALF DAY' then 1 else 0 end) as [HALFDAY],sum(case when g.Remarks = 'Absent' then 1 else 0 end) as Absent,sum(case when g.Remarks = 'OffDay' then 1 else 0 end) as OffDay,DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedateFROM AllDateCTE g where g.eid=17074 and date >'20130101' and date <'20130131' group by g.eid , EName )t` immaduddinahmed Old Hand Group: General Forum Members Points: 385 Visits: 420 SIR I DID THAT BEFOREWHEN DATENAME(weekday,DATEADD(DD,-1,Date)) ='SUNDAY' BUT OFF DAY NOT SHOWING AND ABSENT ALSO NOT SHOWING