Results 1 to 4 of 4

Thread: Problem

  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Unanswered: Problem

    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
    Last edited by immad; 06-26-13 at 04:25.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We already spend considerable time addressing this exact same issue on your thread here:
    http://www.dbforums.com/microsoft-sq...8-off-day.html
    ..at the end of which you claim to have solved the problem yourself. Now you post another thread with virtually the same question?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    It's an off day for him.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    but in my data absent and off days are not defined
    Of course they're not defined in the output! You've commented them out of the SELECT clause. You also don't have anything in the FROM clause that could provide them.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •