Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with query 5

Status
Not open for further replies.
shannanl

shannanl

IS-IT--Management
Joined
Apr 24, 2003
Messages
1,071
Location
US
I have a table that has two fiels, Time_in and Time_out. I need to figure out some information about the entries in those fields. They would look like this:

Time_in Time_out
7/13/2005 8:00:00 AM 7/13/2005 5:00:00 PM 7/14/2005 8:00:00 AM 7/14/2005 5:00:00 PM 7/15/2005 8:00:00 AM 7/15/2005 5:00:00 PM 7/16/2005 8:00:00 AM 7/16/2005 5:00:00 PM 7/17/2005 8:00:00 AM 7/17/2005 5:00:00 PM 7/20/2005 8:00:00 AM 7/20/2005 5:00:00 PM 7/21/2005 8:00:00 AM 7/21/2005 5:00:00 PM 7/22/2005 8:00:00 AM 7/22/2005 5:00:00 PM 7/23/2005 8:00:00 AM 7/23/2005 5:00:00 PM

Lets take the first entry. I need to know how many of the total minutes between Time_in and Time_out were worked between the hours of 7:01 am and 5:00 pm, 5:01 pm and 11:00 pm and 11:01 pm and 7:00 am.

I used this to get the minutes elapsed between the two:

SELECT Time_in, Time_out, DATEDIFF([minute], Time_in, Time_out) AS MINUTES, DATEDIFF([Minute], Time_in, '5:00:00 PM 7 / 13 / 2005 ') AS FIRSTSHIFT
FROM Timeattendance_T

This does return the total minutes worked and the total elapsed between Time_in and 5:00 pm, but if the day is not 7/13/2005 it messes it up and it does not give me the minutes from 7-5, only Time_in to 5:00. I guess I need to replace the 7/13/2005 with a date part of Time_out? Anyway, I am rambling. I would like the output something like this:

Time_in Time_out 1st Shift 2nd Shift 3rd Shift
08:00 AM 8:00 PM 9 3 0
06:00 AM 3:00 PM 8 0 1

I am at a loss as to how to do this. ANY HELP would be greatly appreciated.

Thanks,

Shannan
 
Sort by date Sort by votes
Following are the data I used for test, it's better choose more random data for the date.

Code: 
Time_in                        Time_out   
-------------------------     -------------------------- 
2005-07-13 08:00:00            2005-07-13 18:00:00
2005-07-13 08:00:00            2005-07-13 18:00:00
2005-07-14 08:00:00            2005-07-14 20:00:00
2005-07-15 08:00:00            2005-07-15 17:00:00
2005-07-16 08:00:00            2005-07-16 19:00:00
2005-07-17 08:00:00            2005-07-17 17:00:00
2005-07-20 08:00:00            2005-07-20 17:00:00
2005-07-21 08:00:00            2005-07-21 17:00:00
2005-07-22 08:00:00            2005-07-22 19:00:00
2005-07-23 08:00:00            2005-07-23 21:00:00

Create a user defined function as following:

Code: 
CREATE FUNCTION gettime(@dt1 smalldatetime, @dt2 smalldatetime, @start smalldatetime, @end smalldatetime)
RETURNS int
AS
begin
declare @i as int 
 set @i = (
  case when datediff(mi, @dt2, @start) >= 0 then 0
       when datediff(mi, @dt2, @start) < 0 and datediff(mi, @dt2, @end) > 0 then datediff(mi, @start, @dt2)
       when datediff(mi, @dt2, @start) < 0 and datediff(mi, @dt2, @end) <=0 then datediff(mi, @start, @end)
  end)
return @i
end

run following query to get the result

[code]
select '7:01 am and 5:00 pm', 
       sum(master.dbo.gettime( time_in, time_out, convert(varchar(10), time_in, 21 ) + ' 07:01:00', convert(varchar(10), time_in, 21 ) + ' 17:00:00' ) )
from works
union
select '5:01 pm and 11:00 pm', 
       sum(master.dbo.gettime( time_in, time_out, convert(varchar(10), time_in, 21 ) + ' 17:01:00', convert(varchar(10), time_in, 21 ) + ' 23:00:00' ) )
from works
union
select '11:01 pm and 7:00 am', 
       sum(master.dbo.gettime( time_in, time_out, convert(varchar(10), time_in, 21 ) + ' 23:01:00', convert(varchar(10), dateadd(dd, 1, time_in), 21 ) + ' 07:00:00' ) )
from works
[/code]
 
Upvote 0 Downvote

shannan said:
I need to know how many of the total minutes between Time_in and Time_out were worked between the hours of 7:01 am and 5:00 pm, 5:01 pm and 11:00 pm and 11:01 pm and 7:00 am.
Click to expand...

I thought you want the total based on the whole table, so above SQL should do the job.

But from your SQL, it looks like you want list three shift for each row, then you can use the user defined function defined above:

Code: 
 select Time_in, Time_out, 
 master.dbo.gettime( time_in, time_out, convert(varchar(10), time_in, 21 ) + ' 07:01:00', convert(varchar(10), time_in, 21 ) + ' 17:00:00' ) as shift1,
master.dbo.gettime( time_in, time_out, convert(varchar(10), time_in, 21 ) + ' 17:01:00', convert(varchar(10), time_in, 21 ) + ' 23:00:00' ) as shift2,
master.dbo.gettime( time_in, time_out, convert(varchar(10), time_in, 21 ) + ' 23:01:00', convert(varchar(10), dateadd(dd, 1, time_in), 21 ) + ' 07:00:00' ) as shift3
from  Timeattendance_T
 
Upvote 0 Downvote
I tried both and the last item is the closest to what I am looking for but it does not appear to figure the correct time. For instance, your 2nd query ran on one entry:

Time_in Time_out
7/13/2005 10:00:00 AM 7/13/2005 11:00:00 PM

... should yield:

10:00 AM to 5:00 PM = 7 hours or 420 minutes (1st shift)
5:00 PM to 11:00 PM = 6 hours or 480 minutes (2nd shift)
for a total of 13 hours
...but your query returns :

Shift 1 = 599
Shift 2 = 359
Shift 3 = 0

Thanks,

Shannan
 
Upvote 0 Downvote

Shannan,

I need clarify the question, for the example you mentioned:

Time_in Time_out
7/13/2005 10:00:00 AM 7/13/2005 11:00:00 PM

The period between time_in and time_out shouldn't cover the period between 11:01 PM and 7:00AM which is shift3, why 0 is not correct? thanks
 
Upvote 0 Downvote
Maswien,

Actually, shift 3 is correct, its shift 1 and 2 that are not correct.

Thanks,

Shannan
 
Upvote 0 Downvote


shannan said:
I need to know how many of the total minutes between Time_in and Time_out were worked between the hours of 7:01 am and 5:00 pm, 5:01 pm and 11:00 pm and 11:01 pm and 7:00 am.
Click to expand...

So the shift1 is: from 7:01AM to 5:00PM
shift2 is: from 5:01AM to 11:00PM

The period from 7/13/2005 10:00:00 AM to 7/13/2005 11:00:00 PM have the overlap with shift1 is:

from 7:01AM to 5:00PM = 10 * 60 - 1 = 599 min

The period from 7/13/2005 10:00:00 AM to 7/13/2005 11:00:00 PM have the overlap with shift2 is:

from 5:01AM to 11:00PM = 6 * 60 - 1 = 359 min
 
Upvote 0 Downvote
Time_in Time_out
7/13/2005 10:00:00 AM 7/13/2005 11:00:00 PM

should produce these results:

10:00 AM to 5:00 PM = 7 hours or 420 minutes (1st shift)
5:00 PM to 11:00 PM = 6 hours or 480 minutes (2nd shift)
no time for 3rd shift = 0 hours or 0 minutes (3rd shift)

If I run your query on the above Time_in, Time_out entry it produces:

Shift 1 = 599
Shift 2 = 359
Shift 3 = 0

They did not actually start working (Time_in) until 10:00 a.m. not 7:00 a.m. which would yield 600 minutes.
 
Upvote 0 Downvote
For:

Time_in Time_out
7/13/2005 10:00:00 AM 7/13/2005 11:00:00 PM

should be:

shift1: 7 hours = 420 mins
shift2: 5 hours 59 min = 359 mins
shift3: 0 hours

right?
 
Upvote 0 Downvote
Yes, working from 10 am to 11 pm would yield:

10 am - 5 pm (1st shift) = 7 hours or 420 minutes
5 pm - 11 pm (2nd shift) = 6 hours or 360 minutes
(3rd shift) = 0 hours / 0 minutes

Thanks,

Shannan
 
Upvote 0 Downvote


sorry shannan, the user defined function I posted indeed has bug in it, I fixed it, it should be:

Code: 
CREATE FUNCTION gettime(@dt1 smalldatetime, @dt2 smalldatetime, @start smalldatetime, @end smalldatetime)
RETURNS int
AS
begin
declare @i as int 
 set @i = (
  case when datediff(mi, @dt2, @start) >= 0 or datediff(mi, @end, @dt1) >=0 then 0
       when datediff(mi, @start, @dt1) >= 0 and datediff(mi, @dt2, @end) >=0 then datediff(mi, @dt1, @dt2)
       when datediff(mi, @dt1, @start) >= 0 and datediff(mi, @end, @dt2) >=0 then datediff(mi, @start, @end)
       when datediff(mi, @start, @dt1) >= 0 and datediff(mi, @end, @dt2) >=0 and datediff(mi, @dt1, @end) >=0 
            then datediff(mi, @dt1, @end)
       when datediff(mi, @dt1, @start) >= 0 and datediff(mi, @dt2, @end) >=0 and  datediff(mi, @start, @dt2) >=0 
            then datediff(mi, @start, @dt2)
       end)
return @i
end

Test it using the example you mentioned: 7/13/2005 10:00:00 AM 7/13/2005 11:00:00 PM, run following script

Code: 
declare @time_in as smalldatetime
declare @time_out as smalldatetime
set @time_in = '2005-07-13 10:00:00' 
set @time_out = '2005-07-13 23:00:00'
select @Time_in, @Time_out, 
 master.dbo.gettime( @time_in, @time_out, convert(varchar(10), @time_in, 21 ) + ' 07:01:00', convert(varchar(10), @time_in, 21 ) + ' 17:00:00' ) as shift1,
 master.dbo.gettime( @time_in, @time_out, convert(varchar(10), @time_in, 21 ) + ' 17:01:00', convert(varchar(10), @time_in, 21 ) + ' 23:00:00' ) as shift2,
 master.dbo.gettime( @time_in, @time_out, convert(varchar(10), @time_in, 21 ) + ' 23:01:00', convert(varchar(10), dateadd(dd, 1, @time_in), 21 ) + ' 07:00:00' ) as shift3
 
Upvote 0 Downvote
It says 1 row affected by last query and never return anything?

Shannan
 
Upvote 0 Downvote


shannan,

I revised the user defined function a bit, let it only handles shift1 and shift2, the time for
shift3 can be calculated by : total minutes - shift1 - shift2 - 1

so drop that function and recreate it like following:

Code: 
CREATE FUNCTION gettime(@dt1 smalldatetime, @dt2 smalldatetime, @type tinyint)
RETURNS int
AS
begin
declare @start as smalldatetime
declare @end as smalldatetime
declare @i as int

set @start = case when @type = 1 then convert(varchar(10), @dt1, 21 ) + ' 07:01:00'
                  when @type = 2 then convert(varchar(10), @dt1, 21 ) + ' 17:01:00' 
             end

set @end = case when @type = 1 then convert(varchar(10), @dt1, 21 ) + ' 17:00:00'
                  when @type = 2 then convert(varchar(10), @dt1, 21 ) + ' 23:00:00' 
             end

set @i = (
 case when datediff(mi, @dt2, @start) >= 0 or datediff(mi, @end, @dt1) >=0 then 0
       when datediff(mi, @start, @dt1) >= 0 and datediff(mi, @dt2, @end) >=0 then datediff(mi, @dt1, @dt2)
       when datediff(mi, @dt1, @start) >= 0 and datediff(mi, @end, @dt2) >=0 then datediff(mi, @start, @end)
       when datediff(mi, @start, @dt1) >= 0 and datediff(mi, @end, @dt2) >=0 and datediff(mi, @dt1, @end) >=0 
            then datediff(mi, @dt1, @end)
       when datediff(mi, @dt1, @start) >= 0 and datediff(mi, @dt2, @end) >=0 and  datediff(mi, @start, @dt2) >=0 
            then datediff(mi, @start, @dt2)
       end)

return @i
end

then run following query to get the result:

Code: 
declare @time_in as smalldatetime
declare @time_out as smalldatetime
set @time_in = '2005-07-13 10:00:00' 
set @time_out = '2005-07-13 23:00:00'
select @Time_in, @Time_out, 
 master.dbo.gettime( @time_in, @time_out, 1 ) as shift1,
 master.dbo.gettime( @time_in, @time_out, 2 ) as shift2,
 datediff(mi, @time_in, @time_out) - master.dbo.gettime( @time_in, @time_out, 1 )
                                   - master.dbo.gettime( @time_in, @time_out, 2 ) - 1 as shift3
 
Upvote 0 Downvote
Maswien,

I am sorry but I still can't get this to work. When I run the query it says that one row was affected by the query. Kind of like when you update a table. It returns nothing however.

Thanks,

Shannan
 
Upvote 0 Downvote
Try something like this:

Code: 
/* in this table you can define your shifts */
CREATE TABLE Shifts ( 
						Time_start char(8),
					  	Time_end char(8),
						Day_shift integer,
						Shift_order integer
					)

-- shift 1
INSERT INTO Shifts VALUES( '07:01:00', '17:00:00', 0, 1 )
-- shift 2
INSERT INTO Shifts VALUES( '17:01:00', '23:00:00', 0, 2 )
-- shift 3
INSERT INTO Shifts VALUES( '23:01:00', '07:00:00', 1, 3 )	-- 05:00:00 of next day, so Day_shift is 1


/* this is special table, it contains only numbers from 0 to 365+ ( enough for one year ) */
CREATE TABLE Numbers ( Number integer )

/***************************************************************************/
/* just only for populating table Numbers, this needs to be done only once */
INSERT INTO Numbers VALUES( 0 )
INSERT INTO Numbers VALUES( 1 )
INSERT INTO Numbers VALUES( 2 )
INSERT INTO Numbers VALUES( 3 )
INSERT INTO Numbers VALUES( 4 )
INSERT INTO Numbers VALUES( 5 )

INSERT INTO Numbers
	SELECT Numbers.Number + cnt.value
		FROM Numbers
			CROSS JOIN ( SELECT COUNT(*) AS value FROM Numbers ) AS cnt

INSERT INTO Numbers
	SELECT Numbers.Number + cnt.value
		FROM Numbers
			CROSS JOIN ( SELECT COUNT(*) AS value FROM Numbers ) AS cnt
INSERT INTO Numbers
	SELECT Numbers.Number + cnt.value
		FROM Numbers
			CROSS JOIN ( SELECT COUNT(*) AS value FROM Numbers ) AS cnt

INSERT INTO Numbers
	SELECT Numbers.Number + cnt.value
		FROM Numbers
			CROSS JOIN ( SELECT COUNT(*) AS value FROM Numbers ) AS cnt

INSERT INTO Numbers
	SELECT Numbers.Number + cnt.value
		FROM Numbers
			CROSS JOIN ( SELECT COUNT(*) AS value FROM Numbers ) AS cnt

INSERT INTO Numbers
	SELECT Numbers.Number + cnt.value
		FROM Numbers
			CROSS JOIN ( SELECT COUNT(*) AS value FROM Numbers ) AS cnt
/***************************************************************************/


/* this is my testing table, I make this commented because you should have one */
/*
CREATE TABLE Timeattendance_T( Time_in datetime,
			                 Time_out datetime )


INSERT INTO Timeattendance_T VALUES (
'2005-07-13 08:00:00', '2005-07-13 18:00:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-14 08:00:00', '2005-07-14 20:00:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-15 08:00:00', '2005-07-15 17:00:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-16 08:00:00', '2005-07-16 19:00:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-17 08:00:00', '2005-07-17 17:00:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-20 08:00:00', '2005-07-20 17:00:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-21 08:00:00', '2005-07-21 17:00:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-22 08:00:00', '2005-07-22 19:00:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-23 08:00:00', '2005-07-23 21:00:00')

-- that one you was talking about in previous posts
INSERT INTO Timeattendance_T VALUES (
'2005-07-25 10:00:00', '2005-07-25 23:00:00')

-- some special workers :-)
INSERT INTO Timeattendance_T VALUES (
'2005-07-26 22:30:00', '2005-07-27 07:50:00')
INSERT INTO Timeattendance_T VALUES (
'2005-07-27 08:00:00', '2005-07-28 06:00:00')

*/


/* and this statement should give you results you are want */
SELECT 
	   Timeattendance_T.Time_in,
	   Timeattendance_T.Time_out,
	   SUM( CASE WHEN Shifts.Shift_Order = 1
				 THEN DATEDIFF( minute, 
								CASE WHEN Time_in > CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start THEN Time_in ELSE CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start END,
								CASE WHEN Time_out < CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end THEN Time_out ELSE CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end END
							  )
			     ELSE 0 END 
		  ) AS Shift1,

	   SUM( CASE WHEN Shifts.Shift_Order = 2
				 THEN DATEDIFF( minute, 
								CASE WHEN Time_in > CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start THEN Time_in ELSE CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start END,
								CASE WHEN Time_out < CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end THEN Time_out ELSE CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end END
							  )
			     ELSE 0 END 
		  ) AS Shift2,

	   SUM( CASE WHEN Shifts.Shift_Order = 3 
				 THEN DATEDIFF( minute, 
								CASE WHEN Time_in > CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start THEN Time_in ELSE CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start END,
								CASE WHEN Time_out < CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end THEN Time_out ELSE CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end END
							  )
			     ELSE 0 END 
		  ) AS Shift3
	FROM Timeattendance_T
		CROSS JOIN Numbers
		INNER JOIN ( SELECT CONVERT( char(10), MIN( Time_in ), 21 ) AS Range_start,
							DATEDIFF( day, MIN( Time_in ), MAX( Time_out ) ) + 1 AS Number_of_Days
						FROM Timeattendance_T
				   ) AS Date_range ON Date_range.Number_of_Days > Numbers.Number
		CROSS JOIN Shifts

	WHERE CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start BETWEEN Time_in AND Time_out
			OR
		  CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end BETWEEN Time_in AND Time_out
			OR
		  Time_in BETWEEN CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start AND CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end
			OR
		  Time_out BETWEEN CONVERT( char(10), DATEADD( day, Numbers.Number, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_start AND CONVERT( char(10), DATEADD( day, Numbers.Number + Shifts.Day_shift, Date_range.Range_start ), 21 ) + ' ' + Shifts.Time_end
	GROUP BY
		   
		   Timeattendance_T.Time_in,
		   Timeattendance_T.Time_out
	ORDER BY 
		   Timeattendance_T.Time_in,
		   Timeattendance_T.Time_out

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
 
Upvote 0 Downvote
shannan,

Did you drop the function and recreate it in master DB?
You get '1 row was affected' when you run the query:

Code: 
declare @time_in as smalldatetime
declare @time_out as smalldatetime
set @time_in = '2005-07-13 10:00:00' 
set @time_out = '2005-07-13 23:00:00'
select @Time_in, @Time_out, 
 master.dbo.gettime( @time_in, @time_out, 1 ) as shift1,
 master.dbo.gettime( @time_in, @time_out, 2 ) as shift2,
 datediff(mi, @time_in, @time_out) 
    - master.dbo.gettime( @time_in, @time_out, 1 )
    - master.dbo.gettime( @time_in, @time_out, 2 ) 
    - 1 as shift3
 
Upvote 0 Downvote

sorry for missing question mark ->

You get '1 row was affected' when you run the query?
 
Upvote 0 Downvote
You deserve a star for perseverance :)

Regards,
AA
 
Upvote 0 Downvote
Maswien,

Sorry for the delay in getting back to you. Yes, when I run the query after creating the function in the master database, it just says the one row was affected by the query and it does not return any rows.

Thanks,

Shannan
 
Upvote 0 Downvote

That's a little bit weird to me, if you run this command:

select getdate()

then you get message ' 1 row was affected' ?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
902
Chriss Miller
C
f0rg3tfu1
  • Locked
  • Question Question
PLSQL Package Help
Replies
0
Views
728
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question Question
split one row into multiple rows... help pls
Replies
12
Views
2K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question Question
Error in Select query
Replies
14
Views
1K
Andrzejek
Andrzejek
gussy81
  • Locked
  • Question Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top