DateDiffrence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DateDiffrence

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-25 : 14:27:21

Dear All,

Today is my first day in SQL and i am strugglicg alot.I have one case where i have tow column Entry_Time & Exit_Time datatype is datetime.Case is I have to take Entry_Time & Exit_Time and update one column nhy.For updation i have to check that working then how much hour person is working withing 22:00 to 06:00.

Example :
Now first row is mentioning that person has logged in 21:34 and logout at 07:00 now as per above situation NHY should 10:6 means 8 hrs.In second row he has started work at 23:00 so based on 10:6 ration NHY will be 7.

Entry_Time Exit_Time
1900-01-01 21:34:00.000 1900-01-02 07:00:00.000
1900-01-01 23:00:00.000 1900-01-02 08:10:00.000
1900-01-01 21:58:00.000 1900-01-02 07:00:00.000
1900-01-01 18:49:00.000 1900-01-02 04:06:00.000
1900-01-01 20:57:00.000 1900-01-02 06:13:00.000
1900-01-01 18:30:00.000 1900-01-02 04:00:00.000
1900-01-01 21:39:00.000 1900-01-02 07:00:00.000
1900-01-01 17:31:00.000 1900-01-02 04:48:00.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 14:37:45

sorry can you elaborate on that 10:6 thingy? how you got 8hrs in first case?

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-25 : 14:44:40

This person has login at 21:34 and logout at 07:00 and i have to check how much hour he has worked in betwen 10:00 to 06:00.
So diff is 8 hrs.
Suppose if have started work at 23:00 and finished by 03:00 so as per 10:6 rule my result should be 4 hrs.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 15:17:05

Oh ok..i think thats easy. just use


SELECT CASE WHEN Entry_Time> '1900-01-01 22:00' AND Exit_Time<'1900-01-02 06:00' THEN DATEDIFF(mi,Entry_Time,Exit_Time)/60
            ELSE (DATEDIFF(mi,Entry_Time,Exit_Time)/60)-((DATEDIFF(mi,Entry_Time,'1900-01-01 22:00')+DATEDIFF(mi,'1900-01-02 06:00',Exit_Time))/60)
       END
FROM Yourtab

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-25 : 15:25:14

i have 1000 rows how should i write

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-25 : 15:33:00

Actually, I had something slightly different:


declare @t table (Entry_Time datetime, Exit_Time datetime)
insert @t
select '1900-01-01 21:34:00.000', '1900-01-02 07:00:00.000' union all
select '1900-01-01 23:00:00.000', '1900-01-02 08:10:00.000' union all
select '1900-01-01 21:58:00.000', '1900-01-02 07:00:00.000' union all
select '1900-01-01 18:49:00.000', '1900-01-02 04:06:00.000' union all
select '1900-01-01 20:57:00.000', '1900-01-02 06:13:00.000' union all
select '1900-01-01 18:30:00.000', '1900-01-02 04:00:00.000' union all
select '1900-01-01 21:39:00.000', '1900-01-02 07:00:00.000' union all
select '1900-01-01 17:31:00.000', '1900-01-02 04:48:00.000' union all
select '1900-01-01 23:00:00.000', '1900-01-02 03:00:00.000'

select Entry_time
       ,Exit_time
       ,[Hours_10-6] = datediff(hour, 
              case when entry_time < '1900-01-01 22:00:00.000' then '1900-01-01 22:00:00.000' else entry_time end,
              case when exit_time > '1900-01-02 06:00:00.000' then '1900-01-02 06:00:00.000' else exit_time end)
from   @t

OUTPUT:
Entry_time              Exit_time               Hours_10-6
----------------------- ----------------------- -----------
1900-01-01 21:34:00.000 1900-01-02 07:00:00.000 8
1900-01-01 23:00:00.000 1900-01-02 08:10:00.000 7
1900-01-01 21:58:00.000 1900-01-02 07:00:00.000 8
1900-01-01 18:49:00.000 1900-01-02 04:06:00.000 6
1900-01-01 20:57:00.000 1900-01-02 06:13:00.000 8
1900-01-01 18:30:00.000 1900-01-02 04:00:00.000 6
1900-01-01 21:39:00.000 1900-01-02 07:00:00.000 8
1900-01-01 17:31:00.000 1900-01-02 04:48:00.000 6
1900-01-01 23:00:00.000 1900-01-02 03:00:00.000 4

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources