SQL Server Performance Forum – Threads Archive
Date time sum
Hi all I posted a question a few weeks ago regarding the following Employee ID date in date out Total=5 10/03/2003 09:00AM 10/03/2003 05:00PM 16Hrs
5 10/03/2003 09:00AM 10/03/2003 05:00PM 16Hrs
5 10/02/2003 08:00AM 10/02/2003 05:00PM 15hrs
5 10/02/2003 09:00AM 10/02/2003 05:00PM
6 10/03/2003 09:00AM 10/03/2003 05:00PM 16Hrs
6 10/03/2003 09:00AM 10/03/2003 05:00PM 16Hrs
6 10/02/2003 08:00AM 10/02/2003 05:00PM 15hrs
6 10/02/2003 09:00AM 10/02/2003 05:00PM Now what I want to do is find out what the difference is between the time difference from a days out date / time and the next day’s date in date / time and place that figure into a new field.
Steve Stallard DBA, MCP
Hello, based on what you have given on the web, I came out with the following codes. Let me know if the cursor is too slow (if your table is big). In this case, we can use temporary table. ———————————————————————————– declare @EmplID as int
declare @InDate as datetime
DECLARE time_cursor CURSOR
FORselectEmplID,
max(InDate)
fromYourTable
group by EmplID OPEN time_cursor FETCH NEXT FROM time_cursor
[email protected],
@InDate WHILE @@FETCH_STATUS = 0
BEGIN UPDATE YourTable
SETTotalHour = datediff(hh, OutDate, @InDate)
WHEREInDate = (Select max(InDate)
from YourTable
where InDate < @InDate
andEmplID = @EmplID )
ANDEmplID = @EmplID FETCH NEXT FROM time_cursor
INTO @au_id, @au_fname, @au_lname END CLOSE time_cursor
DEALLOCATE time_cursor
Hi Tikus,
I have approx 4000 users in the system and this number will get bigger each month so think the best option would be to use a temp table.
Many thanks for your help
Steve Steve Stallard DBA, MCP
Since you want to store this in a column, you’d only be working with one days data at a time right? Not sure if that is true, but this should work as a base.
I used your data (and added some more) and wrote this small select. The datediff would be the number of hours between logins.
I picked a fix date because I used your data, which was mostly oct 2& 3. So I ran it as if you were populating the values for anyone who signed on Oct 3. I also added one person who didnt sign on Oct 2 to show it can handle that. Its a select, but you could make it an update (I also display the date/time fields used so you can see better what went on). So with data that looks like this:
employeeid datein dateout
———– ————————— —————————
5 2003-10-03 09:00:00.000 2003-10-03 17:00:00.000
5 2003-10-03 09:00:00.000 2003-10-03 17:00:00.000
5 2003-10-02 08:00:00.000 2003-10-02 17:00:00.000
5 2003-10-02 09:00:00.000 2003-10-02 17:00:00.000
6 2003-10-03 09:00:00.000 2003-10-03 17:00:00.000
6 2003-10-03 09:00:00.000 2003-10-03 17:00:00.000
6 2003-10-02 08:00:00.000 2003-10-02 17:00:00.000
6 2003-10-02 09:00:00.000 2003-10-02 17:00:00.000
8 2003-10-01 09:00:00.000 2003-10-01 15:00:00.000
7 2003-10-02 09:00:00.000 2003-10-02 15:00:00.000
7 2003-10-03 09:00:00.000 2003-10-03 17:00:00.000
8 2003-10-03 09:00:00.000 2003-10-03 17:00:00.000
I get results that look like this
employeeid Overnighttime lastout firstin
———– ————- ————————— —————————
5 16 2003-10-02 17:00:00.000 2003-10-03 09:00:00.000
6 16 2003-10-02 17:00:00.000 2003-10-03 09:00:00.000
7 18 2003-10-02 15:00:00.000 2003-10-03 09:00:00.000
8 42 2003-10-01 15:00:00.000 2003-10-03 09:00:00.000
declare @workdate datetime
set @workdate = ‘oct 3, 2003 00:00:00’ select t1.employeeid, Overnighttime = datediff(hh,lastout, firstin), lastout, firstin
from (select employeeid, lastout = max(dateout) from temp1 where dateout < @workdate group by employeeid) as t1
join (select employeeid, firstin = min(datein) from temp1 where datein > @workdate group by employeeid) as t2 on t1.employeeid = t2.employeeid
where firstin > @workdate
]]>