August 9, 2007 at 2:42 pm
Hi guys, I'm having an issue joining to a table based on time. I have a table named ShiftIntervals created as follows:
CREATE
Table ShiftIntervals (iShiftIntervalId INT IDENTITY(0,1) PRIMARY KEY,dtShiftStartTime DATETIME)DECLARE @dtTime as datetime
SET @dtTime = dateadd(dd, datediff(dd, 0, getdate()), 0)
SET @iInterval = 0
WHILE @iInterval <= 1440 BEGIN
INSERT INTO ShiftIntervals Select convert(varchar(30),dateadd(mi,@iInterval,@dtTime),114)
SET @iInterval = @iInterval + 1 END
I also have a table with the following structure:
CREATE
TABLE [dbo].[Staging_TimePunch](
[PersonNum] [varchar]
(15) NOT NULL, [PunchInDate] [datetime] NOT NULL, [PunchOutDate] [datetime] NOT NULL, [PeriodEndDate] [datetime] NULL, [Minutes] [int] NULL
Given the following example records:
PersonNum PunchInDate PunchOutDate PeriodEndDate Minutes
001014 2007-06-28 00:00:00.000 2007-06-28 00:58:00.000 2007-06-30 00:00:00.000 58
001014 2007-06-28 01:51:00.000 2007-06-28 03:56:00.000 2007-06-30 00:00:00.000 125
001014 2007-06-28 08:07:00.000 2007-06-28 12:07:00.000 2007-06-30 00:00:00.000 240
001014 2007-06-28 21:41:00.000 2007-06-29 00:00:00.000 2007-06-30 00:00:00.000 139
And the following Query:
SELECT
s.PersonNum,s.punchindate, punchoutdate,convert(varchar(30),s.PunchInDate,114), si.dtShiftStartTime
FROM Staging_TimePunch s INNER JOIN ShiftIntervals si
ON dateadd(mi,-1,convert(varchar(30),s.PunchInDate,114)) < si.dtShiftStartTime
AND CASE WHEN convert(varchar(30),s.PunchOutDate,114)='00:00:00.000' THEN '23:59:00.000' ELSE convert(varchar(30),s.PunchOutDate,114) END > si.dtShiftStartTime
where Cast(Convert(VarChar(10),s.PunchInDate,101) AS DateTime) = '2007-06-28'
AND s.PersonNum = '001014'
The Query above picks up each minute for the first three time punch shifts listed, but not the very last shift from 2007-06-28 21:41:00.000 to 2007-06-29 00:00:00.000.
I have no idea why. Because the PunchOutDate is is 6-29 and the punchindate is 6-28? I'm kind of stuck on this one. Any pros out there straighten me out? Thanks guys! Any help is much appreciated!
August 9, 2007 at 4:04 pm
It's because you are changing midnight to 11:59 pm, I should think. Your query is bound to go a bit pear-shaped if you arbitrarily chop a minute off some of your data.
I haven't got a SQL instance handy so I can't test it, but try replacing:
AND CASE WHEN convert(varchar(30),s.PunchOutDate,114)='00:00:00.000' THEN '23:59:00.000' ELSE convert(varchar(30),s.PunchOutDate,114) END > si.dtShiftStartTime
with:
AND CASE WHEN convert(varchar(30),s.PunchOutDate,114)='00:00:00.000' THEN cast(1 as datetime) ELSE convert(varchar(30),s.PunchOutDate,114) END > si.dtShiftStartTime
or with:
AND convert(varchar(30),dateadd(mi, -1, s.PunchOutDate),114) >= si.dtShiftStartTime
(I assume that a shift can never straddle two days, and that the times are specified only to the nearest minute. The 'minutes' column should probably be replaced witha calculated column as ptherwise you have redundant - and therefore potentially inconsistent - data.)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
August 9, 2007 at 4:23 pm
Thanks!!!!
The last suggestion worked:
AND convert(varchar(30),dateadd(mi, -1, s.PunchOutDate),114) >= si.dtShiftStartTime
Appreciate the help big time!
August 9, 2007 at 4:33 pm
This requires a bit of a redesign but worked fairly well for me in the past doing shift calculations. We stored our time data in an int column. We then wrote functions to add it back to the date column (all dates were 00:00:00) and another to pull the time off of the date and create the int.
The biggest benefit we found was that we could put an index on the Time column and get a list of everyone who worked the 8am-5pm shift during a given date range fairly quickly.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 9, 2007 at 4:45 pm
Did the first suggestion fail? I imagine the case statement didn't like the mixed data types - though that could easily be avoided...
Regarding the perennial problem of separating date and time portions, you might consider an (indexed) calculated column which holds the time portion only.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply