Problem

  • SELECT

    eid,

    ename,

    remarks,

    CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Spendtime

    FROM

    (

    SELECT

    g.eid,

    e.ename,

    remarks,

    DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate

    FROM attend_log g

    left join employee e on g.eid=e.eid

    where g.eid=17090 and date >'20130101' and date <'20130131'

    group by g.eid,e.ename,g.remarks

    )t

    i want this type of result

    eid------ename----------late---------spendtime

    17090----abc-------------2-----------163:54

    if remark show 2 lates then late column show 2

    immad

  • Send sample data ..With create table and insert ..specify the business rules

  • 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 time

    If you are not sure on how to do this, please read the link in my signature


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • sir that problem is solve by my self

    the big problem is that this is my query

    SELECT

    EID,

    NAME,

    Late,

    Halfday,

    --Absent,

    --OFFDAY,

    CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIME

    FROM

    (

    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 AggTimedate

    FROM attend_log g

    left join employee e on g.eid = e.eid

    where g.eid=17074 and date >'20130101' and date <'20130131'

    group by g.eid,e.ename

    )t

    i want this type of result

    eid-------name--late--halfday------spendtime-----absent----offday

    17074----abc-----3------1----------204:35----------------1------------4

    but in my data absent and off days are not defined

    this is my attend_log data sample

    eid--------------date---------------------------------spendtime---------------------remarks

    17074-----2013-01-01 00:00:00.000------1900-01-01 09:25:00.000--------null

    17074-----2013-01-02 00:00:00.000------1900-01-01 08:50:00.000--------null

    17074-----2013-01-03 00:00:00.000------1900-01-01 09:15:00.000--------late

    17074-----2013-01-04 00:00:00.000------1900-01-01 04:45:00.000--------halfday

    17074-----2013-01-05 00:00:00.000------1900-01-01 09:25:00.000--------late

    17074-----2013-01-08 00:00:00.000------1900-01-01 09:14:00.000--------null

    17074-----2013-01-09 00:00:00.000------1900-01-01 09:55:00.000--------late

    u can see sir that 6 and 7 dates is not showing

    becouse 6 jan was sunday and on 7th jan employee was absent

    thats the problem i hope u under stand

    thanks for the help

    immad

  • 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' UNION

    SELECT '17074','ABCEmployee','2013-01-02 00:00:00.000','1900-01-01 08:50:00.000','null' UNION

    SELECT '17074','ABCEmployee','2013-01-03 00:00:00.000','1900-01-01 09:15:00.000','late' UNION

    SELECT '17074','ABCEmployee','2013-01-04 00:00:00.000','1900-01-01 04:45:00.000','halfday' UNION

    SELECT '17074','ABCEmployee','2013-01-05 00:00:00.000','1900-01-01 09:25:00.000','late' UNION

    SELECT '17074','ABCEmployee','2013-01-08 00:00:00.000','1900-01-01 09:14:00.000','null' UNION

    SELECT '17074','ABCEmployee','2013-01-09 00:00:00.000','1900-01-01 09:55:00.000','late'

    SELECT * FROM Attend_log

    ;WITH AllDateCTE

    AS

    (

    SELECT Eid,EName,LogDate,SpendTime,Remarks FROM Attend_log

    UNION ALL

    SELECT Eid,EName,DATEADD(DD,-1,LogDate) AS LogDate,NULL AS SpendTime,CONVERT(VARCHAR(50),'Absent' )as Remarks

    FROM AllDateCTE

    WHERE 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 AllDateCTE

    ORDER BY LogDate

  • SIR ITS GIVING ME THE SAME RESULT I DID SOME CHANGES

    ;WITH AllDateCTE

    AS

    (

    SELECT Eid,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

    UNION 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 AllDateCTE

    ORDER BY Date

    immad

  • I meant ,the previous select will add the absent rows alone

    you have to include your calculation as below ,

    ;WITH AllDateCTE

    AS

    (

    SELECT Eid,EName,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

    UNION 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 SPENDTIME

    FROM

    (

    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 AggTimedate

    FROM AllDateCTE g

    where g.eid=17074 and date >'20130101' and date <'20130131'

    group by g.eid , EName

    )t

  • sir its not giving me off day

    we have only one off day and that day is sunday

    immad

  • ;WITH AllDateCTE

    AS

    (

    SELECT Eid,EName,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

    UNION 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 SPENDTIME

    FROM

    (

    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 AggTimedate

    FROM AllDateCTE g

    where g.eid=17074 and date >'20130101' and date <'20130131'

    group by g.eid , EName

    )t

  • SIR I DID THAT BEFORE

    WHEN DATENAME(weekday,DATEADD(DD,-1,Date)) ='SUNDAY'

    BUT OFF DAY NOT SHOWING AND ABSENT ALSO NOT SHOWING

    immad

  • I am getting the result of the query as below based on the sample insert input

    EID ENameLateHALFDAYAbsentOffDaySPENDTIME

    17074ABCEmployee301151:24

  • I AM SORRY SIR BUT THIS QUERY GIVE ME THAT RESULT

    EID-------LATE----HALFDAY---ABSENT--OFFDAY--SPENDTIME

    17074-----1---------1----------0--------0----------204:35

    immad

  • Send the sample data in queries ..

  • sir this is my whole data

    eid-------date---------------timein------------------timeout--------------spendtime-----------------excessshort--------remarks

    17074--2013-01-01-------08:39:00.000-------18:04:00.000---------------09:25:00----------------00:25:00-------Late

    17074--2013-01-02-------08:42:00.000-------09:56:00.000---------------08:50:00---------------10:00.000--------null

    17074--2013-01-02-------10:09:00.000-------12:23:00.000---------------null---------------------null--------------null

    17074--2013-01-02-------12:32:00.000--------3:14:00.000---------------null---------------------null--------------null

    17074--2013-01-02-------13:30:00.000-------18:10:00.000---------------null---------------------null--------------null

    17074--2013-01-03-------08:40:00.000-------17:55:00.000---------------09:15:00.000----------00:15:00.000------null

    17074--2013-01-04-------08:32:00.000-------10:53:00.000---------------08:45:00.000----------00:15:00.000------null

    17074--2013-01-04-------11:09:00.000-------12:57:00.000---------------null----------------------null-------------null

    17074--2013-01-04-------13:06:00.000-------13:18:00.000---------------null----------------------null-------------null

    17074--2013-01-04-------13:45:00.000-------18:09:00.000---------------null----------------------null-------------null

    17074--2013-01-05-------08:47:00.000-------18:01:00.000------------ ---09:14:00.000------------00:14:00.000---halfday

    17074--2013-01-08-------08:36:00.000-------10:25:00.000------------ ---09:55:00.000------------00:55:00.000-----null

    17074--2013-01-08-------10:49:00.000-------13:02:00.000---------------null-----------------------null-------------null

    17074--2013-01-08-------13:35:00.000-------15:24:00.000---------------null-----------------------null-------------null

    17074--2013-01-08-------15:42:00.000-------19:46:00.000---------------null-----------------------null-----------------null

    this is your query

    ;WITH AllDateCTE

    AS

    (

    SELECT Eid,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

    UNION ALL

    SELECT Eid,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-08 00:00:00.000'

    )

    SELECT

    EID,

    Late,

    [HALFDAY],

    Absent,

    OffDay,

    CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIME

    FROM

    (

    SELECT

    g.EID,

    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 AggTimedate

    FROM AllDateCTE g

    where g.eid=17074 and date >'20130101' and date <'20130108'

    group by g.eid

    )t

    and this query gives me this result

    eid--------late--halfday---absent--offday----spendtime

    17074------1------1-------0-------0--------204:35

    immad

  • Send the data as Insert scripts .

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply