October 30, 2010 at 6:12 am
i have a table with this structure:
DECLARE @tblLeaveRec TABLE(
intSlNoINT IDENTITY,
txtEmpNoCHAR (6),
intLeaveAccNoINT,
dtdaySMALLDATETIME
)
insert into @tblLeaveRec values ( '1000',1,'01-Dec-2009')
insert into @tblLeaveRec values ( '1000',1,'02-Dec-2009')
insert into @tblLeaveRec values ( '1000',1,'03-Dec-2009')
insert into @tblLeaveRec values ( '1000',1,'04-Dec-2009')
insert into @tblLeaveRec values ( '1000',1,'05-Dec-2009')
insert into @tblLeaveRec values ( '1000',1,'01-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'02-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'03-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'04-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'05-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'06-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'07-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'08-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'09-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'10-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'11-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'12-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'13-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'14-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'15-Jan-2010')
insert into @tblLeaveRec values ( '1000',1,'02-Feb-2010')
insert into @tblLeaveRec values ( '1000',1,'03-Feb-2010')
insert into @tblLeaveRec values ( '1000',1,'04-Feb-2010')
insert into @tblLeaveRec values ( '1000',1,'05-Feb-2010')
insert into @tblLeaveRec values ( '1000',1,'06-Feb-2010')
insert into @tblLeaveRec values ( '1000',1,'12-Feb-2010')
insert into @tblLeaveRec values ( '1000',1,'13-Feb-2010')
(
Now, in my procedure, if i pass a date, i want to know at that time, the person was on long leave(12 days or above) or not. I mean, if i pass the date as 3rd Dec, It should return 0. If it is 5th Jan, then it should pass 1.
Is there any way to write the code for this unless using "while" or "cursor"?
Thanks,
Pramod
October 30, 2010 at 9:40 am
The solution to this is in recognizing that you need to identify the "islands" - the start/end positions of each contiguous range of values.
The first thing that I'm going to do is to re-declare your table, so that it provides an index that can be utilized:
DECLARE @tblLeaveRec TABLE(
intSlNo INT IDENTITY,
txtEmpNo CHAR (6),
intLeaveAccNo INT,
dtday SMALLDATETIME,
UNIQUE(txtEmpNo, dtday)
);
Then, use this code to determine, the starting points (for the group, where there isn't a previous date), the ending points (for the group, where there isn't a following date). Join the points together, and you have your islands, with the starting and ending points. Add in the number of days the range covers, and you're ready to get your results.
declare @StartDate smalldatetime;
set @StartDate = '20100103';
WITH StartingPoints AS
(
-- get records for each txtEmpNo where there is not a preceding day
SELECT txtEmpNo,
dtday,
RN = row_number() OVER (PARTITION BY txtEmpNo ORDER BY dtDay)
FROM @tblLeaveRec t1
WHERE NOT EXISTS( SELECT t2.*
FROM @tblLeaveRec t2
WHERE t2.txtEmpNo = t1.txtEmpNo
AND t2.dtday = DateAdd(day, -1, t1.dtday))
), EndingPoints AS
(
-- get records for each txtEmpNo where there is not a following day
SELECT txtEmpNo,
dtday,
RN = row_number() OVER (PARTITION BY txtEmpNo ORDER BY dtDay)
FROM @tblLeaveRec t3
WHERE NOT EXISTS( SELECT t4.*
FROM @tblLeaveRec t4
WHERE t4.txtEmpNo = t3.txtEmpNo
AND t4.dtday = DateAdd(day, 1, t3.dtday))
), Islands AS
(
-- join the results together to get a start/end range
SELECT s.txtEmpNo,
StartDate = s.dtday,
EndDate = e.dtday,
-- include the total range
DateRange = DateDiff(day, s.dtday, e.dtday)+1
FROM StartingPoints s
JOIN EndingPoints e
ON s.txtEmpNo = e.txtEmpNo
AND s.RN = e.RN
)
SELECT Qty = count(*)
FROM Islands
WHERE @StartDate BETWEEN StartDate and EndDate
AND DateRange >= 12 -- ("long leave")
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 30, 2010 at 10:06 am
An alternative method (faster, less reads) is:
declare @StartDate smalldatetime;
set @StartDate = '20100103';
WITH CTE AS
(
-- Group each date by the difference between that day and the row number
-- (sequential dates will have the same group date)
SELECT txtEmpNo,
dtday,
Grp = DateAdd(day, - ROW_NUMBER() OVER (PARTITION BY txtEmpNo ORDER BY dtday), dtday)
FROM @tblLeaveRec
), Islands AS
(
-- the island range is the min/max value for each group
SELECT txtEmpNo,
StartRange = MIN(dtday),
EndRange = MAX(dtday)
FROM CTE
GROUP BY txtEmpNo, Grp
)
-- Check to see if the specified date is within the specified range
SELECT Qty = count(*)
FROM Islands
WHERE @StartDate BETWEEN StartRange and EndRange
AND EndRange-StartRange+1 >= 12; -- ("long leave")
Statistics from both methods:
FIRST METHOD
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#1FEDB87C'. Scan count 2, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 3 ms.
SECOND METHOD
Table '#1FEDB87C'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 30, 2010 at 3:09 pm
Ya beat me to it by a long shot. Well done.
Oddly enough, I just wrote an "SQL Spackle" on the same subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2010 at 7:13 pm
Jeff Moden (10/30/2010)
Ya beat me to it by a long shot. Well done.
Thanks. I've got just five words to say... "SQL Server MVP Deep Dives[/url]"
Oddly enough, I just wrote an "SQL Spackle" on the same subject.
Not so odd... I was thinking about a "SQL Spackle" on Gaps/Islands - but I'd be borrowing heaving from the above book! :w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 31, 2010 at 6:55 am
I have a solution which yields this statistics
Table 'Worktable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
No cte and zero windowed functions.
No gaps and islands calculations.
Just plain euclidean mathematics... Still interested in seeing it even if works in SQL Server 2000 too?
There is one limitation though.. You cannot have a new leave within 12 days of previous leave.
You can improve on WayneS solution by adding the same date filter as I have.
SELECTtxtEmpNo,
CASE SUM(DATEDIFF(DAY, 0, dtDay)) - COUNT(*) * MIN(DATEDIFF(DAY, 0, dtDay))
WHEN COUNT(*) * (COUNT(*) - 1) / 2 THEN COUNT(*) / 12
ELSE 0
END AS Qty
FROM@tblLeaveRec
WHEREdtday BETWEEN DATEADD(DAY, -11, @StartDate) AND DATEADD(DAY, 11, @StartDate)
GROUP BYtxtEmpNo
N 56°04'39.16"
E 12°55'05.25"
October 31, 2010 at 10:17 am
WayneS (10/30/2010)
Jeff Moden (10/30/2010)
Ya beat me to it by a long shot. Well done.Thanks. I've got just five words to say... "SQL Server MVP Deep Dives[/url]"
Oddly enough, I just wrote an "SQL Spackle" on the same subject.
Not so odd... I was thinking about a "SQL Spackle" on Gaps/Islands - but I'd be borrowing heaving from the above book! :w00t:
I never read it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2010 at 10:34 am
SwePeso (10/31/2010)
I have a solution which yields this statistics
Table 'Worktable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
No cte and zero windowed functions.
No gaps and islands calculations.
Just plain euclidean mathematics... Still interested in seeing it even if works in SQL Server 2000 too?
There is one limitation though.. You cannot have a new leave within 12 days of previous leave.
You can improve on WayneS solution by adding the same date filter as I have.
SELECTtxtEmpNo,
CASE SUM(DATEDIFF(DAY, 0, dtDay)) - COUNT(*) * MIN(DATEDIFF(DAY, 0, dtDay))
WHEN COUNT(*) * (COUNT(*) - 1) / 2 THEN COUNT(*) / 12
ELSE 0
END AS Qty
FROM@tblLeaveRec
WHEREdtday BETWEEN DATEADD(DAY, -11, @StartDate) AND DATEADD(DAY, 11, @StartDate)
GROUP BYtxtEmpNo
Very cool, Peter! It manages to use only 1 five row sort instead of 2 twenty-seven row sorts. We need a bigger test with multiple "start date" lookups instead of just 1.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2010 at 11:38 pm
WayneS (10/30/2010)
Jeff Moden (10/30/2010)
Ya beat me to it by a long shot. Well done.Thanks. I've got just five words to say... "SQL Server MVP Deep Dives[/url]"
Oddly enough, I just wrote an "SQL Spackle" on the same subject.
Not so odd... I was thinking about a "SQL Spackle" on Gaps/Islands - but I'd be borrowing heaving from the above book! :w00t:
We still need something on overlapping date ranges instead of just consecutive dates. It would be my pleasure to let one of you good folks write it up.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2010 at 6:35 am
Thanks a lot guys. It really helped me a lot. It also helped me to completely understand the CTE Recursiveness.
Thanks again.
Pramod
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply