SQL Server Performance

Sum Spend Time

Discussion in 'ALL SQL SERVER QUESTIONS' started by immad, Jun 25, 2013.

  1. immad Member

    SELECT
    CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Result
    FROM
    (
    SELECT
    DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
    FROM attend_log where eid=17090 and date >'20130101' and date <'20130131'
    )t

    this is my query

    i want to show this type of result

    eid-----------result
    17090--------163:54

    please help me out
  2. davidfarr Member

    Keep in mind that your [SpendTime] column is a varchar data type, and so it will be difficult to use that column for any kind of SUM or mathematical function.

    Does your attend_log table still contain the [TimeIn] and [TimeOut] columns from your previous question thread ?
    It will be easier and more accurate to use [TimeIn] and [TimeOut] columns for your query;
    Maybe like this;
    Code:
    select eid,
    convert(varchar(10),convert(int,(sum(DATEDIFF(minute, [TimeIn], [TimeOut]))/60))) + ':'
    +convert(varchar(10),sum(DATEDIFF(minute, [TimeIn], [TimeOut])) - convert(int,(sum(DATEDIFF(minute, [TimeIn], [TimeOut]))/60))*60) as Result
    FROM attend_log
    where eid=17090
    and [date] >= '20130101' and [date] < '20130201'
    GROUP BY eid
    
    Also notice that I have changed your date comparissons;
    If your dates are date >'20130101' and date <'20130131' then you might be excluding the 1st and 31st day of the month, which is probably not your intention.
  3. immad Member

    sir your query gives me accurate result can u caluclate off day and absent for me in my data absent and off day are not availabe

    Code:
    SELECT
    EID,
    LATE,
    [HALF DAY],
    ABSENT,
    [OFF DAY],
    CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS [SPEND TIME]
    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 [HALF DAY],
    sum(case when g.Remarks = 'ABSENT' then 1 else 0 end) as ABSENT,
    sum(case when g.Remarks = 'OFF DAY' then 1 else 0 end) as [OFF DAY],
    DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
    FROM ATTEND_LOG g
    where g.eid=17074 and date >'20130101' and date <'20130228'
    group by g.eid
    )t
    this is my 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

Share This Page