HELP Eliminating this cursor?
-
I have data that resembles the following:
PersonNum varchar(15), ShiftStartDate datetime, ShiftEndDate datetime
'001', 01/01/2007 1:00:00:00, 01/01/2007 2:00:00:00
I need to break each record per day into 5 minute buckets (288 possible 5 buckets per 24 hr. day) based on their shiftstartdate and shiftenddate times. I need to insert one record per 5 minute bucket into a table for detailed comparisons to separate data that need to be made afterwards. So for 01/01/2007, if someone worked from '1:00:00' until '2:00:00', I would place 12 separate records into a table (structure below) as:
PersonNum, Date, Interval
'001', '01/01/2007', 12.....(this represents 1:00:00:00 the 12th 5 min bucket of a 24 hr. day)
'001', '01/01/2007', 13......up until
'001', '01/01/2007', 24......(the 24th 5 min. bucket of the day)
I was thinking of something like the following, but wondered if there was a better way to do so. If I can clear anything up, let me know. I really appreciate any help with this. Thanks!
/*Create Temp Table for Schedule data*/
CREATE
TABLE #ScheduleIntervals (PersonNum VARCHAR(15), ComplianceDate DATETIME, Interval int)
/*Shift Cursor to break down Schedule data into 5 minute Increments*/
DECLARE
@PersonNum AS varchar(15),
DECLARE
@ComplianceDate AS DATETIME,
DECLARE
@ShiftStartDate AS DATETIME,
DECLARE
@ShiftEndDate AS DATETIME,
DECLARE
@StartInterval AS int,
DECLARE
@EndInterval AS int,
DECLARE
@IntervalNums AS int,
DECLARE
@Interval AS int
DECLARE shift_cursor CURSOR FOR
SELECT
Personnum
,Cast(Convert(VarChar(10),cp.ShiftStartDate,101) AS DateTime),ShiftStartDate,ShiftEndDate
FROM Staging_Schedules
OPEN shift_cursor
FETCH NEXT FROM shift_cursor
INTO PersonNum, @ComplianceDate, @ShiftStartDate, @ShiftEndDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @StartInterval = (DATEDIFF(m,'00:00:00',Cast(Convert(VarChar(8),ShiftStartDate,108) As DateTime))/5)*12
SET @IntervalNums = (DATEDIFF(m,Cast(Convert(VarChar(8),@ShiftStartDate,108) As DateTime),Cast(Convert(VarChar(8),@ShiftEndDate,108) As DateTime))/5)*12
SET @EndInterval = @StartInterval + @IntervalNums
SET @Interval = @StartInterval
WHILE @Interval <= @EndInterval
BEGIN
INSERT INTO #ScheduleIntervals (PersonNum, ComplianceDate, Interval)
SELECT @PersonNum, @ComplianceDate, @Interval
SET @Interval = @Interval + 1
END
SET @StartInterval = 0
SET @IntervalNums = 0
SET @EndInterval = 0
SET @Interval = 0
-- Get the next shift
FETCH NEXT FROM shift_cursor
INTO @PersonNum, @ComplianceDate, @ShiftStartDate, @ShiftEndDate
END
CLOSE shift_cursor
DEALLOCATE shift_cursor
-
Start with this:
--create function dbo.fn_BucketNum(@InDate datetime)
--returns smallint
--as begin
-- return (select isnull(nullif((datepart(hh,@InDate) * 12) + ceiling(datepart(mi,@InDate) / 5.),0),288))
--end
declare @tablenums table (bucket smallint)
declare @shiftdata table (EmpId int, ShiftStart datetime, ShiftEnd datetime)
insert @shiftdata values (10001, '2007-06-01 01:00:00', '2007-06-01 02:00:00')
insert @shiftdata values (10002, '2007-06-01 01:00:00', '2007-06-01 08:00:00')
declare @testdt datetime,
@loopcnt smallint
set @loopcnt = 1
while @loopcnt <= 288
begin
insert @tablenums values(@loopcnt)
set @loopcnt = @loopcnt + 1
end
set @testdt = '2007-06-05 12:01:01 AM'
--select isnull(nullif((datepart(hh,@testdt) * 12) + ceiling(datepart(mi,@testdt) / 5.) /* + datepart(mi,@testdt) % 5,0 )*/,288)
select isnull(nullif((datepart(hh,@testdt) * 12) + ceiling(datepart(mi,@testdt) / 5.),0),288)
select * from @tablenums
select * from @shiftdata
select
EmpId,
dateadd(dd,datediff(dd,0,ShiftStart),0) as ComlianceDate,
bucket
from
@shiftdata sd
cross join @tablenums tm
where
tm.bucket between dbo.fn_BucketNum(ShiftStart) and dbo.fn_BucketNum(ShiftEnd)
order by
EmpId, bucket
I was doing this using SQL 2005, but I think it should still work using SQL 2000.
-
Lynn beat me to it... but since I typed all this up I'll add it anyway...
Here is another for you:
-------------------------------------------------------------------------
-- FIRST WE NEED A TABLE THAT HAS ALL 288 POSSIBLE 5 MINUTE INTERVALS
-------------------------------------------------------------------------
DECLARE @iInterval INT
IF EXISTS (Select 1 from sysobjects where id = OBJECT_ID(N'tblShiftIntervals'))
DROP TABLE tblShiftIntervals
CREATE Table tblShiftIntervals (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 tblShiftIntervals Select convert(varchar(30),dateadd(mi,@iInterval,@dtTime),114)
SET @iInterval = @iInterval + 5
END
-------------------------------------------------------------------------
-- OK NOW WE HAVE A TABLE WITH ALL 288 POSSIBLE 5 MINUTE INTERVALS
-------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-- NOW WE NEED SOME SAMPLE DATA, THIS REPRESENTS AN EXAMPLE OF WHAT YOU ARE TRYING TO ACCOMPLISH
-----------------------------------------------------------------------------------------------------
IF EXISTS(Select 1 from sysobjects where id = OBJECT_ID(N'tblShifts'))
DROP TABLE tblShifts
CREATE TABLE tblShifts (iShiftId INT IDENTITY(1,1) PRIMARY KEY,iPersonId INT,dtShiftStart DATETIME,dtShiftEnd DATETIME)
INSERT INTO tblShifts
SELECT 1,'2006-01-01 08:00:00.000','2006-01-01 17:00:00.000'
UNION
SELECT 2,'2006-01-01 08:00:00.000','2006-01-01 17:00:00.000'
UNION
SELECT 3,'2006-01-01 08:00:00.000','2006-01-01 17:00:00.000'
UNION
SELECT 4,'2006-01-01 07:00:00.000','2006-01-01 16:00:00.000'
UNION
SELECT 5,'2006-01-01 07:00:00.000','2006-01-01 16:00:00.000'
UNION
SELECT 6,'2006-01-01 10:00:00.000','2006-01-01 19:00:00.000'
UNION
SELECT 7,'2006-01-01 10:00:00.000','2006-01-01 19:00:00.000'
UNION
SELECT 1,'2006-01-02 08:00:00.000','2006-01-02 17:00:00.000'
UNION
SELECT 2,'2006-01-02 08:00:00.000','2006-01-02 17:00:00.000'
UNION
SELECT 3,'2006-01-02 08:00:00.000','2006-01-02 17:00:00.000'
UNION
SELECT 4,'2006-01-02 07:00:00.000','2006-01-02 16:00:00.000'
UNION
SELECT 5,'2006-01-02 07:00:00.000','2006-01-02 16:00:00.000'
UNION
SELECT 6,'2006-01-02 10:00:00.000','2006-01-02 19:00:00.000'
UNION
SELECT 7,'2006-01-02 10:00:00.000','2006-01-02 19:00:00.000'
-----------------------------------------------------------------------------------------------------
-- NOW WE HAVE SAMPLE DATA, TO PROCESS THE EXAMPLE OF WHAT YOU ARE TRYING TO ACCOMPLISH
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-- HERE IS HOW YOU CAN GET THE RESULTS YOU ARE LOOKING FOR WITHOUT A CURSOR
-----------------------------------------------------------------------------------------------------
-- INSERT INTO [Your Table]
Select si.iShiftIntervalId,s.iPersonId,s.dtShiftStart,s.dtShiftEnd
-- You can also display YYYY-MM-DD by using datepart and/or datename here
From tblShifts s INNER JOIN tblShiftIntervals si
on dateadd(mi,-5,convert(varchar(30),s.dtShiftStart,114)) < si.dtShiftStartTime
and convert(varchar(30),s.dtShiftEnd,114) > si.dtShiftStartTime
-- THIS QUERY WILL RETURN THE PERSONID ONCE FOR EVERY 5 MINUTE INTERVAL IN THEIR SHIFTS
-- THE SAMPLE DATA SETS IT UP SO THAT EACH DAY IS CONSIDERED SEPERATE
-- Looks something like this:
iShiftIntervalId,iPersonId,dtShiftStart,dtShiftEnd
96,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
97,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
98,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
99,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
100,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
101,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
102,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
103,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
104,1,2006-01-01 08:00:00.000,2006-01-01 17:00:00.000
... and so on
-
-
WOW!!! Thanks guys! Let me play around with these a little bit.
Thanks so much for the quick replies!
Pat
-
Different take... traded in a little bit of the simplicity and speed of Lynn's and Jason's methods for some flexibility so far as a programmable interval and the ability to cross midnight...
First, I'm one of "those" folks that keeps a permanent Tally table (table of numbers) handy... has lot's of uses and doesn't take much space. Here's how to make one...
--===== Create and populate the Tally table on the fly SELECT TOP 11000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC
And, here's my take on the solution with programmable interval and the ability to cross midnight...
--===== Create a test table and populate it with some test data DECLARE @yourtable TABLE(PersonNum VARCHAR(15), ShiftStartDate DATETIME, ShiftEndDate DATETIME) INSERT INTO @yourtable SELECT '001', '01/01/2007 1:00:00:00', '01/01/2007 2:00:00:00' UNION ALL SELECT '002', '01/01/2007 13:00:00:00', '01/01/2007 15:00:00:00' UNION ALL SELECT '003', '01/01/2007 00:03:00:00', '01/03/2007 12:00:00:00'
--===== Declare a variable to hold the desired interval we want to split a day into DECLARE @DesiredInterval INT SET @DesiredInterval = 5 --Minutes
--===== Produce the desired rows SELECT PersonNum, [Date] = CONVERT(CHAR(10),DATEADD(mi,(t.N-1)*@DesiredInterval,DATEADD(dd,DATEDIFF(dd,0,y.ShiftStartDate),0)),101), Interval = 1+(t.N-1)%(24*(60/@DesiredInterval)), IntervalDateTime = DATEADD(mi,(t.N-1)*@DesiredInterval,DATEADD(dd,DATEDIFF(dd,0,y.ShiftStartDate),0)) FROM @yourtable y, dbo.Tally t WHERE DATEADD(mi,(t.N-1)*@DesiredInterval,DATEADD(dd,DATEDIFF(dd,0,y.ShiftStartDate),0))>= y.ShiftStartDate AND DATEADD(mi,(t.N-1)*@DesiredInterval,DATEADD(dd,DATEDIFF(dd,0,y.ShiftStartDate),0)) < y.ShiftEndDate AND t.N <=3*(24*(60/@DesiredInterval)) --Limited to 3 days ORDER BY y.PersonNum,y.IntervalDateTime
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks so much for all the great replies! Awesome! Here's the whole script. What do you guys think? Any changes you might suggest? Indexing maybe? Thanks!
If
Object_ID('tempdb..#tmpScheduleVariance') Is Not Null Drop Table #tmpScheduleVariance -- ScheduleVariance
If
Object_ID('tempdb..#tmpSchedules') Is Not Null Drop Table #tmpSchedules -- Schedule Data
If
Object_ID('tempdb..#tmpTimePunch') Is Not Null Drop Table #tmpTimePunch -- Time Punch Data
---------------------------------------------------------------------------------------------------
-- Insert Schedule Data into #tmpSchedules broken down into 5 minute buckets
---------------------------------------------------------------------------------------------------
-- Create Temp Table
CREATE TABLE #tmpSchedules (PersonNum VARCHAR(15), ComplianceDate DATETIME, ShiftIntervalID INT)
INSERT INTO #tmpSchedules (PersonNum, ComplianceDate, ShiftIntervalID)
(
SELECT
s
.PersonNum,Cast(Convert(VarChar(10),s.ShiftStartDate,101) AS DateTime),si.iShiftIntervalId
FROM Staging_Schedules s INNER JOIN ShiftIntervals si
ON dateadd(mi,-5,convert(varchar(30),s.ShiftStartDate,114)) < si.dtShiftStartTime
AND convert(varchar(30),s.ShiftEndDate,114) > si.dtShiftStartTime
)
---------------------------------------------------------------------------------------------------
-- Insert TimePunch Data into #tmpTimePunch broken down into 5 minute buckets
---------------------------------------------------------------------------------------------------
-- Create Temp Table
CREATE TABLE #tmpTimePunch (PersonNum VARCHAR(15), ComplianceDate DATETIME, ShiftIntervalID INT)
INSERT INTO #tmpTimePunch (PersonNum, ComplianceDate, ShiftIntervalID)
(
SELECT s.PersonNum,Cast(Convert(VarChar(10),s.PunchInDate,101) AS DateTime),si.iShiftIntervalId
FROM Staging_TimePunch s INNER JOIN ShiftIntervals si
ON dateadd(mi,-5,convert(varchar(30),s.PunchInDate,114)) < si.dtShiftStartTime
AND convert(varchar(30),s.PunchOutDate,114) > si.dtShiftStartTime
)
---------------------------------------------------------------------------------------------------
-- Create Temp Table to combine results from #tmpSchedules and #tmpTimePunch
---------------------------------------------------------------------------------------------------
--Temp Table #ScheduleVariance
CREATE TABLE #ScheduleVariance (PersonNum varchar(15),ComplianceDate DATETIME,WorkScheduled int,WorkNotScheduled int,ScheduledNotWorked int)
--INSERT Recs with a match on PersonNum, ComplianceDate, and Interval as WorkScheduled
INSERT INTO #ScheduleVariance (PersonNum,ComplianceDate,WorkScheduled, WorkNotScheduled, ScheduledNotWorked)
(
SELECT
s
.Personnum,s.ComplianceDate,'5' AS WorkedScheduled, '0' AS WorkNotScheduled, '0' AS ScheduledNotWorked
FROM #tmpSchedules s
INNER JOIN #tmpTimePunch t
ON (s.Personnum=t.personnum) AND (s.ComplianceDate=t.ComplianceDate) AND (s.Interval=t.Interval)
)
--INSERT Recs from #tmpSchedules with no match on PersonNum, ComplianceDate, and Interval from #tmpTimePunch as ScheduledNotWorked
INSERT INTO #ScheduleVariance (PersonNum,ComplianceDate,WorkScheduled, WorkNotScheduled, ScheduledNotWorked)
(
SELECT
s
.PersonNum,s.ComplianceDate,'0' AS WorkScheduled, '0' AS WorkNotScheduled, '5' AS ScheduledNotWorked
FROM #tmpSchedules s
LEFT OUTER JOIN #tmpTimePunch t
ON (s.Personnum=t.personnum) AND (s.ComplianceDate=t.ComplianceDate) AND (s.Interval=t.Interval)
WHERE t.PersonNum IS NULL
)
--INSERT Recs from #tmpTimePunch with no match on PersonNum, ComplianceDate, and Interval in #tmpSchedules as WorkNotScheduled
INSERT INTO #ScheduleVariance (PersonNum,ComplianceDate,WorkScheduled, WorkNotScheduled, ScheduledNotWorked)
(
SELECT
t
.PersonNum,t.ComplianceDate,'0' AS WorkScheduled, '5' AS WorkNotScheduled, '0' AS ScheduledNotWorked
FROM #tmpTimePunch t
LEFT OUTER JOIN #tmpSchedules s
ON (t.Personnum=s.personnum) AND (t.ComplianceDate=s.ComplianceDate) AND (t.Interval=s.Interval)
WHERE s.PersonNum IS NULL
)
---------------------------------------------------------------------------------------------------
-- Group and Sum #ScheduleVariance table by PersonNum, PeriodEndDate
---------------------------------------------------------------------------------------------------
INSERT INTO MQBI_Dev.dbo.ScheduleVariance
(PersonNum,PeriodEndDate,WorkScheduled,WorkNotScheduled,ScheduledNotWorked,AllNonCompliance)
SELECT
PersonNum
,
CAST(
CAST(CASE
WHEN DAY(ComplianceDate) <= 15 THEN
CASE
WHEN MONTH(ComplianceDate) < 10 THEN CONVERT(INT, CONVERT(CHAR(4), YEAR(ComplianceDate)) + '0' + CONVERT(CHAR(1), MONTH(ComplianceDate)) + '15')
ELSE CONVERT(INT, CONVERT(CHAR(4), YEAR(ComplianceDate)) + CONVERT(CHAR(2), MONTH(ComplianceDate)) + '15')
END
ELSE
CASE
WHEN MONTH(ComplianceDate) < 10 THEN CONVERT(INT, CONVERT(CHAR(4), YEAR(ComplianceDate)) + '0' + CONVERT(CHAR(1), MONTH(ComplianceDate)) + CONVERT(CHAR(2), DAY(DATEADD(d, -1, DATEADD(m, 1, CONVERT(DATETIME, CONVERT(CHAR(4), YEAR(ComplianceDate)) + '0' + CONVERT(CHAR(1), MONTH(ComplianceDate)) + '01'))))))
ELSE CONVERT(INT, CONVERT(CHAR(4), YEAR(ComplianceDate)) + CONVERT(CHAR(2), MONTH(ComplianceDate)) + CONVERT(CHAR(2), DAY(DATEADD(d, -1, DATEADD(m, 1, CONVERT(DATETIME, CONVERT(CHAR(4), YEAR(ComplianceDate)) + CONVERT(CHAR(2), MONTH(ComplianceDate)) + '01'))))))
END
END
AS VARCHAR)
AS DATETIME) AS PeriodEndDate,
SUM(WorkScheduled), SUM(WorkNotScheduled), SUM(ScheduledNotWorked), Sum(ScheduledNotWorked + WorkedNotScheduled) AS AllNonCompliance
FROM #ScheduleVariance
GROUP BY
PersonNum
,
CAST(
CAST(CASE
WHEN DAY(ComplianceDate) <= 15 THEN
CASE
WHEN MONTH(ComplianceDate) < 10 THEN CONVERT(INT, CONVERT(CHAR(4), YEAR(ComplianceDate)) + '0' + CONVERT(CHAR(1), MONTH(ComplianceDate)) + '15')
ELSE CONVERT(INT, CONVERT(CHAR(4), YEAR(ComplianceDate)) + CONVERT(CHAR(2), MONTH(ComplianceDate)) + '15')
END
ELSE
CASE
WHEN MONTH(ComplianceDate) < 10 THEN CONVERT(INT, CONVERT(CHAR(4), YEAR(ComplianceDate)) + '0' + CONVERT(CHAR(1), MONTH(ComplianceDate)) + CONVERT(CHAR(2), DAY(DATEADD(d, -1, DATEADD(m, 1, CONVERT(DATETIME, CONVERT(CHAR(4), YEAR(ComplianceDate)) + '0' + CONVERT(CHAR(1), MONTH(ComplianceDate)) + '01'))))))
ELSE CONVERT(INT, CONVERT(CHAR(4), YEAR(ComplianceDate)) + CONVERT(CHAR(2), MONTH(ComplianceDate)) + CONVERT(CHAR(2), DAY(DATEADD(d, -1, DATEADD(m, 1, CONVERT(DATETIME, CONVERT(CHAR(4), YEAR(ComplianceDate)) + CONVERT(CHAR(2), MONTH(ComplianceDate)) + '01'))))))
END
END
AS VARCHAR)
AS DATETIME)
---------------------------------------------------------------------------------------------------
-- Clean up Temp Tables
---------------------------------------------------------------------------------------------------
DROP
TABLE #tmpSchedules
DROP
TABLE #tmpTimePunch
DROP
TABLE #tmpScheduleVariance
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply