SQL Server Performance

Calculate two rows column

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

  1. immad Member

    Dear All,

    i have a problem

    my actual data display like this:

    --------------date-----------employee code---------time in---------------------------time out----------------------spend time
    2013-01-08 00:00:00.000----17090--------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000---------03:20:00
    2013-01-08 00:00:00.000----17090-------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000----------01:19:00
    2013-01-09 00:00:00.000----17090-------2013-01-08 09:00:00.000----2013-01-08 06:00:00.000-------09:00:00

    i want this data to display in report:

    --------------date-----------employee code---------time in---------------------------time out--------------------spend time
    2013-01-08 00:00:00.000-----17090--------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000------04:39:00
    2013-01-08 00:00:00.000------17090------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000----
    2013-01-09 00:00:00.000------17090------2013-01-08 09:00:00.000----2013-01-08 06:00:00.000-------09:00:00

    i want to sum spend time with same date and show in one row

    i am using this query

    select
    t.date,
    t.employeecode
    , t.[Time in]
    , t.[Time out]
    , CONVERT(VARCHAR(8), DATEADD(ss, DATEDIFF(ss, t.[Time in], t.[Time out]), 0), 108) AS SpendTime
    FROM trans t
    inner JOIN rooaster se ON t.employeecode = se.eid
    inner join shift s on s.cid = se.cid
    and s.bid = se.bid
    and s.shift = se.d2
    where employeecode=17090
  2. davidfarr Member

    Perhaps it's just me, but I do not understand your question.

    What are you trying to sum (add together) ?
    Your have said you want to sum "spend time" with "same date", but what is "same date" in this context ? Is it a field in a table, or a computed value ?

    Your two data output grids display similar row values. The top grid is showing correct "spend time" values and the lower grid is not.
    Are you just looking for a query syntax that correctly calculates the "spend time" value ?

    The query that you provided is referencing several other tables or objects, such as [rooaster], [shift] and [trans].
    Without knowing the data and structure of those objects it is difficult to advise you on their relevance in the query and the expected result.
  3. immad Member

    i am making a software name attendance system

    employee come in factroy 9:00 and time out 6:00 and go home.this is fine

    --------date-------------------eid-------------tmiein----------------timeout--------------------------spendtime
    2013-01-09 00:00:00.000---17090---2013-01-08 09:00:00.000----2013-01-08 06:00:00.000-------09:00:00

    but some times employee go out from a factory and come back
    for example
    employee come 9:17am and for some reason he timeout 12:37pm
    then he come 17:21pm and timeout for go home at 18:40pm

    so in that situation data display like this

    --------------date----------------eid------------timein---------------------------timeout--------------------spendtime
    2013-01-08 00:00:00.000----17090----2013-01-08 09:17:00.000----2013-01-08 12:37:00.000------03:20:00
    2013-01-08 00:00:00.000-----17090----2013-01-08 17:21:00.000----2013-01-08 18:40:00.000-----01:19:00

    in above example two same dates 2013-01-08 00:00:00.000 calculate spent time and show in different row
    i want to sum 03:20:00 + 01:19:00 and show in first row =04:39:00
    just like below data . shift and rooaster forget this tables its not related to spend time

    here is the query.

    select
    t.date,
    t.employeecode
    , t.[Time in]
    , t.[Time out]
    , CONVERT(VARCHAR(8), DATEADD(ss, DATEDIFF(ss, t.[Time in], t.[Time out]), 0), 108) AS SpendTime
    FROM trans t
    where employeecode=17090

    this data come from trans table
    i want a syntax that calculate same dates spend time and show in one row

    i want this type of data

    --------------date--------------------timein---------------------------timeout--------------------spendtime
    2013-01-08 00:00:00.000------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000----04:39:00
    2013-01-08 00:00:00.000------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000----
  4. FrankKalis Moderator

    What about something like this?

    Code:
    DECLARE @t TABLE (eid int, mytime time(0))
    
    INSERT  INTO @t
            (eid, mytime)
    VALUES
            (17090, '03:20:00'),
            (17090, '01:19:00')
    
    SELECT
        eid,
        SUM(DATEDIFF(MINUTE, '00:00:00', mytime)) AS WorkedTimeInMinutes
    FROM
        @t T
    GROUP BY
        eid;
    
    ;
    WITH    MyCTE
              AS (SELECT
                    eid,
                    SUM(DATEDIFF(MINUTE, '00:00:00', mytime)) AS mytime
                  FROM
                    @t T
                  GROUP BY
                    eid)
        SELECT
            eid,
            REPLACE(STR(mytime / 60, 2), ' ', '0')
            + ':'
            + REPLACE(STR(mytime % 60, 2), ' ', '0') AS WorkedTimeInHours
        FROM
            MyCTE
    
    eid         WorkedTimeInMinutes
    ----------- -------------------
    17090       279
    (1 row(s) affected)
    
    eid         WorkedTimeInHours
    ----------- -----------------
    17090       04:39
    (1 row(s) affected)
       
    
  5. immad Member

    well the this query give me error other wise the query that give 279 worked time minutes are fine but i want worked time in hour query result

    Code:
    SELECT
    eid,
    REPLACE(STR(mytime / 60, 2), ' ', '0') + ':' + REPLACE(STR(mytime % 60, 2), ' ', '0')
    AS
    WorkedTimeInHours
    FROM
    MyCTE
    
    Msg 260, Level 16, State 3, Line 2 Disallowed implicit conversion from data type datetime to data type int, table 'ta', column 'mytime'. Use the CONVERT function to run this query.
  6. davidfarr Member

    How about something like this;

    Code:
    select [date], min([Time in]) as First_Record, sum(DATEDIFF(minute, [Time in], [Time out])) as Time_Minutes
    into #temp1 from trans
    where employeecode=17090
    group by [date]
    GO
    select t.[date], t.employeecode, t.[Time in], t.[Time out],
    CONVERT(VARCHAR(8), DATEADD(minute, t2.Time_Minutes, 0), 108) AS SpendTime
    FROM trans t
    left join #temp1 t2 on t.[date]=t2.[date] and t.[Time in] = t2.First_Record
    where t.employeecode=17090
    order by t.[date], t.[Time in]
    
    There is probably another way to achieve the result using nested queries within joins instead of a #temp table, but I find this way to be cleaner and more efficient.
    immad likes this.
  7. immad Member

    Wonder Full :) You Are a Life Saver
  8. immad Member

    ok query is working fine i remove go and #temp Thanks
  9. immad Member

    sir if i want to calculate excess short column just like spend time column
    like this

    this is my actual data
    -date-----------------------------eid---------------------timein------------------------timeout--------------------spend-------excess
    2013-01-04 00:00:00.000---26446---2013-06-11 09:44:00.000----2013-06-11 13:20:00.000--------08:06:00------5:24
    2013-01-04 00:00:00.000---26446---2013-06-11 13:56:00.000----2013-06-11 18:26:00.000---------NULL---------4:30

    i want this type data
    -date-----------------------------eid---------------------timein---------------------timeout--------------------spend---------excess
    2013-01-04 00:00:00.000--26446--2013-06-11 09:44:00.000-----2013-06-11 13:20:00.000-----08:06:00-------00:54
    2013-01-04 00:00:00.000--26446--2013-06-11 13:56:00.000----2013-06-11 18:26:00.000---------NULL---------NULL

    i calculate excess from timein and time out

    please implement on this query

    select
    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(ss, [Timein], [Timeout])) as Time_In_Seconds
    into #temp1 from ATend
    where eid=26446
    group by [date]
    GO
    select
    t.[date],
    t.eid,
    t.[Timein] timein,
    t.[Timeout]timeout,
    CONVERT(VARCHAR(8), DATEADD(ss, Time_In_Seconds, 0), 108) AS SpendTime,
    CAST (ABS( convert(varchar(10),540,108) - DATEDIFF (MINUTE, t.Timein, t.timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
    CAST (ABS (convert(varchar(10),540,108) - DATEDIFF (MINUTE, t.Timein, t.timeout ) ) % 60 as varchar ) as excesshorttime
    FROM ATend t
    left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record
    where eid=26446
    order by t.[date], t.[Timein]

    Thanks for the help
  10. davidfarr Member

    You can try 2 options;

    Code:
    --drop table #temp1
    select
    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from Atend
    where eid = 26446
    group by [date]
    GO
    select
    t.[date],
    t.eid,
    t.[Timein] as timein,
    t.[Timeout] as timeout,
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
    CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,
    case when (540 - Time_Minutes) > 0 then 'Short'
    when (540 - Time_Minutes) < 0 then 'Excess'
    else NULL end as ExcessShort
    FROM Atend t
    left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
    where eid = 26446
    order by t.[date], t.[Timein]
    
    ...or you can try this;

    Code:
    --drop table #temp1
    select
    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from Atend
    where eid = 26446
    group by [date]
    GO
    select
    t.[date],
    t.eid,
    t.[Timein] as timein,
    t.[Timeout] as timeout,
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
    case when (540 - Time_Minutes) > 0 Then '- ' else '+ ' end
    +CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime
    FROM Atend t
    left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
    where eid = 26446
    order by t.[date], t.[Timein]
    
    immad likes this.
  11. immad Member

    Thanks LIFE SAVER :) UR AWSOME
  12. immad Member

    hi

    i want to caluclate half day

    my actual data is like this

    date-----------------------------eid---------------timein---------------------------timeout-----------spend-----excessshort------excess
    2013-03-21 00:00:00.000---26446---2013-06-13 09:13:00.000--2013-06-13 3:46:00.000----06:33:00---- 02:27:00-------Short

    i want this data

    date-----------------------------eid---------------timein---------------------timeout-----------spend-----excessshort-excess----Remarks
    2013-03-21 00:00:00.000--26446--2013-06-13 09:13:00.000--2013-06-13 3:46:00.000-06:33:00--02:27:00---Short------HALFDAY

    employee timing is 9am to 6pm if he leaves from factory at 4pm or before 4 pm then remarks say halfday

    becouse we have 2 hours half day mean if he go for some work and didnt come back for 2 hours or more then 2 hours and didnt come back then half day implemented

    please implement on this query

    drop table #temp1
    select
    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from attend_LOG
    where eid = 17090
    group by [date]
    GO
    select
    t.[date],
    t.eid,
    t.[Timein] as timein,
    t.[Timeout] as timeout,
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,

    case when (540 - Time_Minutes) > 0 Then '- ' else '+ ' end
    +CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime

    FROM attend_LOG t
    left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
    where eid = 17090
    order by t.[date], t.[Timein]

    please help me out
    thanks for the help
  13. davidfarr Member

    Perhaps this ?
    I have assumed that any employee that works a total of 7 hours or less is regarded as "HalfDay".

    Code:
    --drop table #temp1
    select
    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from Atend
    where eid = 26446
    group by [date]
    GO
    select
    t.[date],
    t.eid,
    t.[Timein] as timein,
    t.[Timeout] as timeout,
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
    CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,
    case when (540 - Time_Minutes) > 0 then 'Short'
    when (540 - Time_Minutes) < 0 then 'Excess'
    else NULL end as ExcessShort,
    case when (540 - Time_Minutes) >= 120 then 'HalfDay' else '' end as Remarks
    FROM Atend t
    left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
    where eid = 26446
    order by t.[date], t.[Timein]
    
    immad likes this.
  14. immad Member

    Thank You LIFE SAVER :)
  15. immad Member

    if i want to merge this query

    Code:
    select
    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from Atend
    where eid = 26446
    group by [date]
    GO
    select
    t.[date],
    t.eid,
    t.[Timein] as timein,
    t.[Timeout] as timeout,
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
    CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,
    case when (540 - Time_Minutes) > 0 then 'Short'
    when (540 - Time_Minutes) < 0 then 'Excess'
    else NULL end as ExcessShort,
    case when (540 - Time_Minutes) >= 120 then 'HalfDay' else '' end as Remarks
    FROM Atend t
    left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
    where eid = 17090
    order by t.[date], t.[Timein]
    into this query

    Code:
    DECLARE @MinDate datetime,
    @MaxDate datetime
      
    SELECT @MinDate = MIN([Date]),
    @MaxDate = MAX([Date])
    FROM attend_log
    
    SELECT p.Date,
    p.eid,
    q.TimeIn,
    q.TimeOut,
    case when q.timein is null and q.timeout is null then 'Absent' else '' end remarks
    --q.shift,
    
    FROM
    (
    SELECT f.[Date],eid
    FROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) f
    CROSS JOIN (SELECT DISTINCT eid FROM attend_log) t
    )p
    LEFT JOIN attend_log q
    ON q.eid = p.eid
    AND q.[Date] = p.[Date]
    where p.eid=17090
    order by date,eid,timein,timeout
    
    
    i want this result

    -----date-------------------------eid-------------timein---------------------timeout------------------------spendtime--excesshsort----excess
    2013-01-04 00:00:00.000---17090--2013-06-12 09:29:00.000--2013-06-12 18:47:00.000--09:18:00----00:18:00----Excess
    2013-01-05 00:00:00.000---17090--2013-06-12 09:08:00.000--2013-06-12 13:34:00.000--07:41:00----01:19:00----Excess

    2013-01-06 00:00:00.000---17090---------------null-----------------------------null---------------------null-------------null----null

    actullay the second query give me sundays and absent results the null is absent i want to show this result

    i hope u under stand
  16. davidfarr Member

    What days and dates are in CalendarTable ?
    Does CalendarTable only contain holidays and Sundays ? ..or does it contain every day of the year ?

    You do not need to keep the EID column in CalendarTable, because that table applies to all employees.
    You will not need any 'CROSS JOIN' query with the EID column.

    If CalendarTable only has Sundays and holidays then you can try this;

    Code:
    select
    [date] as mdate,
    min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from Atend
    where eid = 17090
    group by [date]
    GO
    select
    t.[date] as [Date],
    t.eid,
    t.[Timein] as timein,
    t.[Timeout] as timeout,
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
    CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,
    case when (540 - Time_Minutes) > 0 then 'Short'
    when (540 - Time_Minutes) < 0 then 'Excess'
    else NULL end as ExcessShort,
    case when (540 - Time_Minutes) >= 120 then 'HalfDay' else '' end as Remarks
    FROM Atend t
    left join #temp1 t2 on t.[date] = t2.[mdate] and t.[Timein] = t2.First_Record
    where eid = 17090
    UNION ALL
    select distinct [Date],'17090' as eid,null,null,null,null,null,null
    from CalendarTable where [Date] < getdate()
    and [Date] >= (select min([Date]) from #temp1)
    order by [Date], [Timein]
    
  17. immad Member

    i have a calendar table and its have date column only and its show whole year dates
    from 1/1/2013 to 12/31/2013
    this query is not working
    actullay i want a query that when sunday or public holiday comes its give me OFF DAY IN remarks column and show O in shift COLUMN
    or when employee absent. query give me absent on remarks
    please give me the accurate query Thanks
    please implement on your query that u give me last time
    this is your query.this is query give me accurate result

    drop table #temp1
    select
    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from attend_log
    where eid = 17090
    group by [date]
    GO
    select
    t.[date],
    t.eid,
    t.[Timein] as timein,
    t.[Timeout] as timeout,
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
    CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,
    case when (540 - Time_Minutes) > 0 then 'Short'
    when (540 - Time_Minutes) < 0 then 'Excess'
    else NULL end as ExcessShort,
    case when (540 - Time_Minutes) >= 120 then 'HalfDay' else '' end as Remarks
    FROM attend_log t
    left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
    where eid = 17090
    order by t.[date], t.[Timein]

    YOUR QUERY GIVE ME THAT RESULT

    date------------------timein------------------timeout----------------------------spend-----------------shift--------------------------excess/short--------------remarks
    2-May-2013------9:20AM-------------------6:17PM--------------------------08:57-----------------G----------------------------00:03------------------------
    3-May-2013------9:09AM--------------------2:01PM--------------------------07:08----------------G----------------------------01:52-----------------------------NULL
    3-May-2013------3:56PM---------------------6:12PM---------------------------NULL-------------NULL------------------------NULL-----------------------------NULL
    4-May-2013------9:03AM--------------------10:41AM--------------------------08:53----------------G----------------------- ---00:07---------------------------- Short
    4-May-2013------10:51AM--------------------12:10PM------------------------NULL---------------NULL----------------------NULL-------------------------------NULL
    4-May-2013------12:15PM---------------------6:11PM-------------------------NULL---------------NULL----------------------NULL--------------------------------NULL
    6-May-2013--------9:17AM--------------------6:23PM----------------------09:06-------------------G-------------------------00:06-------------------------------NULL-
    7-May-2013--------9:11AM--------------------10:21AM--------------------08:46-------------------G--------------------------00:14------------------------------NULL-
    7-May-2013--------10:30AM------------------11:05AM-------------------NULL--------------------G--------------------------NULL-------------------------------NULL
    7-May-2013--------11:11AM-------------------6:12PM--------------------NULL--------------------G---------------------------NULL----------------------------NULL
    8-May-2013--------9:30AM---------------------6:24PM-------------------08:54---------------------G---------------------------00:06-----------------------------------
    9-May-2013--------9:10AM---------------------6:22PM-------------------09:12---------------------G---------------------------00:12---------------------------------------

    I WANT THIS TYPE OF RESULT

    date------------------timein------------------timeout----------------------------spend-----------------shift--------------------------excess/short--------------remarks
    1-May-2013------- NULL--------------------NULL-----------------------------NULL------------------O----------------------------NULL----------------------------OffDay
    2-May-2013------9:20AM-------------------6:17PM--------------------------08:57-----------------G----------------------------00:03---------------------------
    3-May-2013------9:09AM--------------------2:01PM--------------------------07:08----------------G----------------------------01:52-----------------------------NULL
    3-May-2013------3:56PM---------------------6:12PM---------------------------NULL-------------NULL------------------------NULL-----------------------------NULL
    4-May-2013------9:03AM--------------------10:41AM--------------------------08:53----------------G----------------------- ---00:07----------------------------
    4-May-2013------10:51AM--------------------12:10PM------------------------NULL---------------NULL----------------------NULL-------------------------------NULL
    4-May-2013------12:15PM---------------------6:11PM-------------------------NULL---------------NULL----------------------NULL--------------------------------NULL
    5-May-2013--------NULL-------------------------NULL---------------------------NULL-----------------O---------------------------NULL------------------------------ OffDay
    6-May-2013--------9:17AM--------------------6:23PM----------------------09:06-------------------G-------------------------00:06-------------------------------NULL-
    7-May-2013--------9:11AM--------------------10:21AM--------------------08:46-------------------G--------------------------00:14------------------------------NULL-
    7-May-2013--------10:30AM------------------11:05AM-------------------NULL--------------------G--------------------------NULL-------------------------------NULL
    7-May-2013--------11:11AM-------------------6:12PM--------------------NULL--------------------G---------------------------NULL----------------------------NULL
    8-May-2013--------9:30AM---------------------6:24PM-------------------08:54---------------------G---------------------------00:06---------------------------
    9-May-2013--------9:10AM---------------------6:22PM-------------------09:12---------------------G---------------------------00:12---------------------------------------
    10-May-2013-------NULL-------------------------NULL----------------------NULL---------------------G---------------------------NULL------------------------------ Absent
    11-May-2013--------NULL------------------------NULL----------------------NULL---------------------G------------------------------------------------------------------- Absent
    12-May-2013-----------------------------------------NULL--------------------NULL----------------------O------------------------------NULL-----------------------------OffDay

    1ST MAY IS LABOUR DAY
    i hope u understand thanks
  18. davidfarr Member

    If the CalendarTable has every day of the year then you will need some kind of marker in the CalendarTable to indicate which days are holidays.
    I suggest you add a column to the CalendarTable to mark the holidays with an 'O', something like this;

    CalendarTable;
    [Date]-----------------------------------------[DayType]
    12-May-2013----------------------------------W
    13-May-2013----------------------------------W
    14-May-2013----------------------------------O
    15-May-2013----------------------------------W

    After you have the table above then you can try this;
    
    Code:
    --drop table #temp1
    select  [date], min([Timein]) as First_Record,
    sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
    into #temp1 from Atend
    where eid = 26446
    group by [date]
    GO
    --drop table #temp2
    select t.[date], t.eid, t.[Timein] as timein, t.[Timeout] as timeout,
    CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
    CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,
    case when (540 - Time_Minutes) > 0 then 'Short'
    when (540 - Time_Minutes) < 0 then 'Excess'
    else NULL end as ExcessShort,
    case when (540 - Time_Minutes) >= 120 then 'HalfDay' else '' end as Remarks
    into #temp2
    FROM Atend t
    left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
    where eid = 26446
    --order by t.[date], t.[Timein]
    GO
    select t1.[Date],t2.eid,t2.timein,t2.timeout,t2.spendtime,t2.excesshorttime,t2.excessshort,
    case when t1.daytype = 'O' then 'OffDay' else t2.Remarks end as Remarks
    from dbo.CalendarTable t1
    left join #temp2 t2 on t1.[Date] = t2.[Date]
    where t1.[Date] < getdate() and t1.[Date] >= (select min([Date]) from #temp1)
    order by t1.[Date], t2.timein
    
  19. immad Member

    Thats fine sir
  20. immad Member

    oh i am sory sir i did that thats why i remove it.thanks any way

Share This Page