June 21, 2010 at 2:29 am
Hi all,
I have a small problem for witch i cannot see the solution..
I thought it would not be so hard but whatever i try, its wrong 🙁
The problem:
We have a table in witch we keep al days that an employer is not at work (an absent table)
in this table we work day per day but for legal purposes i have the need to find ranges of dates that an employer was sick.
codes for this are 'Z' and 'AO'
testdata:
DECLARE @afw TABLE( id INT IDENTITY(1,1) PRIMARY KEY, van DATETIME, tot DATETIME, code varchar(2))
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-05-1 00:00:00' , '2010-05-1 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-05-2 00:00:00' , '2010-05-2 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-05-3 00:00:00' , '2010-05-3 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-1 00:00:00' , '2010-06-1 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-2 00:00:00' , '2010-06-2 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-3 00:00:00' , '2010-06-3 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-4 00:00:00' , '2010-06-4 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-5 00:00:00' , '2010-06-5 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-6 00:00:00' , '2010-06-6 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-7 00:00:00' , '2010-06-7 23:59:59' ,'G' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-8 00:00:00' , '2010-06-8 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-9 00:00:00' , '2010-06-9 23:59:59' ,'Z' )
INSERT INTO @afw ( [van] ,[tot] ,) VALUES ( '2010-06-10 00:00:00' , '2010-06-10 23:59:59' ,'Z' )
SELECT * FROM @afw AS a
From this testdata the result i want is:
from: 2010-05-01 00:00:00 till 2010-05-03 23:00:59 *** person was sick in this timerange and next day is not found
from: 2010-06-01 00:00:00 till 2010-06-06 23:59:59 *** range stops here cause next day has another code thats not Z of AO
from: 2010-06-08 00:00:00 till 2010-06-10 23:59:59 *** range stops here cause there is no next day found with code Z or AO
Any advice on how to find this data would be highly appreciated.
Wkr,
Eddy
June 21, 2010 at 2:33 am
June 21, 2010 at 3:45 am
Hi Dave,
Thx a lot for the link, it was just what i needed.,
Amazing how you guys can come up that quickly with the right link for a certain problem.
I did however alter the code a bit because this week-end i have seen somewhere in an article from this site that BOL suggest not to increment dates with the + signature.
Therefore i have altered the code and implemented the DateAdd function
;
WITH cteRangeZiekteDagen ( Datum, GroupNr, WKN_ID )
AS ( SELECT a.[AFW_VAN_DATUM_UUR] ,
DATEADD(dd,
ROW_NUMBER() OVER ( PARTITION BY [WKN_ID] ORDER BY [AFW_VAN_DATUM_UUR] DESC ),
a.[AFW_VAN_DATUM_UUR]) ,
[WKN_ID]
FROM [dbo].[AFWEZIGHEDEN] AS a
WHERE [PAR_ID_AFDELING_WKN] IN ( 1430, 1435 )
AND [AFW_TYPE_AFWEZIGHEID] IN ( 1474, 1425 ) -- ziek en arbeidsongeval
AND [AFW_REMOVED] = 0
AND [PAR_ID_FIRMA] = 174
)
SELECT MIN([Datum]) AS VanDag ,
MAX([Datum]) AS TotDag ,
[WKN_ID]
FROM [cteRangeZiekteDagen]
WHERE [WKN_ID] = 28
GROUP BY [WKN_ID] ,
[GroupNr]
ORDER BY 1
go
I also got to enchance the row_number function with a partition by clause but that one i found real easy 😀
Tnx you verry much for you superlightning fast reply with the correct link,
Wkr,
Eddy
June 21, 2010 at 3:48 am
eddy-644184 (6/21/2010)Tnx you verry much for you superlightning fast reply with the correct link
Glad to help , though as far as correct goes, there are other methods that may be more optimal with larger datasets. Just be careful to test with a realistic dataload.
June 21, 2010 at 4:11 am
Hi Dave,
I have tested it on the actual data thats in our table,
It Contains approx. 110.000 records,
With an increase of approx. 10.000 a year
i have also created an test index to speed it up a little bit, i see now that we have an index seek in stead of index scan.
The query performs well in terms of speed
SQL Server Execution Times:
CPU time = 313 ms, elapsed time = 160 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
For IO stats, its not real clear whether tis is good or less good
(185 row(s) affected)
Table 'AFWEZIGHEDEN'. Scan count 18, logical reads 1846, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.
I have included the execution plan in this post.,
I already understand the most items in an execution report but for this one, i do not find directly something that i can alter to make things even bether,
This is for one, the first time i see parralelism in action 😀
Wkr,
Eddy
June 21, 2010 at 4:20 am
Looks pretty good, one sort in there which *could* be removed by adding ( or extending by including) an index.
June 21, 2010 at 8:02 am
eddy-644184 (6/21/2010)
Hi Dave,Amazing how you guys can come up that quickly with the right link for a certain problem.
Well, considering that Dave wrote that blog... 😉
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply