January 18, 2011 at 2:20 am
Hi,
I'm stuck on a problem where i need to display the date ranges of someone in absent (hollidays or whatever) in the total duration,
But in our table, we only insert the workdays and not the weekends.
This means that: when someone is at leaf for 3 weeks, my routine will display 3 ranges of 1 week
Here i should be able to tell the totaal duration in the form of startday in week21 till endday in week3
I tried the Dateadd function so i could get groups of dates but how to overcome the missing week-end days.?
Note also: when a person is Ill (sick) then the week-end days will be in the database witch will be making it a more complex problem
Table structure, for simplicity i just show 1 table, in real live this table is connected to several other ones from witch i need data
/****** Object: Table [dbo].[AFWEZIGHEDEN] Script Date: 01/18/2011 10:09:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AFWEZIGHEDEN](
[AFW_ID] [int] IDENTITY(1,1) NOT NULL,
[AFW_REMOVED] [bit] NOT NULL,
[PAR_ID_FIRMA] [int] NOT NULL,
[PAR_ID_AFDELING_WKN] [int] NOT NULL,
[PAR_ID_DISC] [int] NOT NULL,
[WKN_ID] [int] NOT NULL,
[AFW_TYPE_AFWEZIGHEID] [int] NOT NULL,
[AFW_VAN_DATUM_UUR] [datetime] NULL,
[AFW_TOT_DATUM_UUR] [datetime] NULL,
[ADR_ID_WKN] [int] NULL,
CONSTRAINT [PK_AFWEZIGHEDEN] PRIMARY KEY CLUSTERED
(
[AFW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AFWEZIGHEDEN] CHECK CONSTRAINT [FK_AFWEZIGHEDEN_ADRWKN_OPWKN_ID]
GO
ALTER TABLE [dbo].[AFWEZIGHEDEN] ADD CONSTRAINT [DF_AFWEZIGHEDEN_AFW_REMOVED] DEFAULT ((0)) FOR [AFW_REMOVED]
GO
-- Insert testdata
INSERT INTO dbo.AFWEZIGHEDEN
( AFW_REMOVED ,
PAR_ID_FIRMA ,
PAR_ID_AFDELING_WKN ,
PAR_ID_DISC ,
WKN_ID ,
AFW_TYPE_AFWEZIGHEID ,
AFW_VAN_DATUM_UUR ,
AFW_TOT_DATUM_UUR ,
ADR_ID_WKN
)
SELECT 0,174,1430,868,151,1464,'2010-11-11','2010-11-11 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-12','2010-11-12 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-15','2010-11-15 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-16','2010-11-16 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-17','2010-11-17 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-18','2010-11-18 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-19','2010-11-19 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-22','2010-11-22 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-23','2010-11-23 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-24','2010-11-24 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-25','2010-11-25 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-26','2010-11-26 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1464,'2010-11-29','2010-11-29 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1472,'2010-11-30','2010-11-30 23:59:59',40313
UNION ALL
SELECT 0,174,1430,868,151,1464,'2010-12-01','2010-12-01 23:59:59',40313
-- My code so far
DECLARE @WKN_ID INT
SET @WKN_ID = 151
;WITH [cteRangeZiekteDagen] (Datum,GroupNr,WKN_ID, Afdeling, TypeAfw)
AS
(
SELECT a.[AFW_VAN_DATUM_UUR]
, DATEADD(dd, ROW_NUMBER() OVER (PARTITION BY a.[WKN_ID] ORDER BY [AFW_VAN_DATUM_UUR] DESC),a.[AFW_VAN_DATUM_UUR])
, a.[WKN_ID]
,a.PAR_ID_AFDELING_WKN
,CASE WHEN a.AFW_TYPE_AFWEZIGHEID IN (1464,1472) THEN 'V' ELSE 'Z' END AS TypeAfw
FROM [dbo].[AFWEZIGHEDEN] AS a
WHERE a.[AFW_REMOVED] = 0
AND a.[WKN_ID] = CASE WHEN @WKN_ID > 0 THEN @WKN_ID ELSE a.[WKN_ID] END
)
SELECT MIN(Datum) AS VanDag, MAX(Datum) AS TotDag, DATEDIFF(dd,MIN(Datum),MAX(Datum)) + 1 AS numdagen, [WKN_ID],TypeAfw
FROM [cteRangeZiekteDagen]
GROUP BY [WKN_ID], GroupNr,TypeAfw
ORDER BY [WKN_ID]
This code i close but not close enough.
The results should show:
for the range 11-11-2010 till 12-11-2010 = correct 2 days
for the range 15-11-2010 till 03-12-2010 15 days, whereas my code is showing 3 periods , witch should be one,
this is due to the lack of the days witch fall in a Weekend, cause we are only working in weekdays and not in week-ends you simply cannot take leaf in the week-end
however when AFW_TYPE_AFWEZIGHEID = 1474 or 1425 then these are days of illnes or accidents witch will be having week-end days included in this table, so the procedure schould account for that
Could someone help me in the way of finding a solution for this.
Tnx alot,
Wkr,
Eddy
January 18, 2011 at 2:29 am
Have you considered the use of a calendar table ?
January 18, 2011 at 2:43 am
Hi dave,
Tnx for the link, will go reading that article right away,
I got some troubles posting the topic, it was already submitted when i was not done writing due to hitting the enter button :hehe:
Eddy
January 21, 2011 at 6:46 am
Hi dave,
I have created the calender table in my system,
I already found some good uses for it but not so right away for this problem..
consider the folowing ranges with these codes
2010-07-12 -- 2010-07-12 F = 1 day holliday
2010-07-13 -- 2010-07-16 V = 4 days vacation
2010-07-19 -- 2010-07-23 V = 5 days vacation
2010-07-26 -- 2010-07-30 V = 5 days vacation
2010-12-27 -- 2010-12-31 V = 4 days vacation
The result i would need =
2010-07-12 till 2010-07-12 = 1 day cause its code F
2010-12-13 till 2010-07-30 = 14 days V (excluding the WE in the count but display the range as 1 period cause only WE days are fallingthe range)
2010-12-27 -- 2010-12-31 = 4 Days
Now i tried to use a second cte with would get me the min(date) and max(date) of the periods but that would give me a result of
2010-07-13 till 2010-12-31 = a lot of days to much 🙂
How can i get the desired result with the use of the calender table..?
I don't see it ..
Tnx already for any advice
Wkr,
Eddy
January 24, 2011 at 6:27 am
Hello,
Anybody that can help me out here pls.?
what i have:
DECLARE @afw TABLE(id INT IDENTITY(1,1) PRIMARY KEY,
wkn_id INT,
code VARCHAR(2),
datefrom datetime,
dateuntil DATETIME
)
INSERT INTO @afw
( wkn_id ,
code ,
datefrom ,
dateuntil
)
SELECT 1, 'V', '2011-01-03', '2011-01-03 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-04', '2011-01-04 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-05', '2011-01-05 23:59:59'
UNION ALL
SELECT 1, 'TA', '2011-01-06', '2011-01-06 23:59:59' -- code TA count for the same as code V, this would not change the range
UNION ALL
SELECT 1, 'V', '2011-01-07', '2011-01-07 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-10', '2011-01-10 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-11', '2011-01-11 23:59:59'
UNION ALL
SELECT 1, 'SV', '2011-01-24', '2011-01-24 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-27', '2011-01-27 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-28', '2011-01-28 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-31', '2011-01-31 23:59:59'
;WITH [cteRangeZiekteDagen] (dateFrom, GroupNr, WknId, Code)
AS
(
SELECT a.datefrom
, DATEADD(dd, ROW_NUMBER() OVER (PARTITION BY a.[WKN_ID] ORDER BY datefrom DESC),a.datefrom)
, a.[WKN_ID]
, CASE WHEN a.code IN ('V','TA','TK') THEN 'V' ELSE 'Z' END AS code
FROM @afw AS a
)
SELECT MIN(dateFrom) AS VanDag, MAX(dateFrom) AS TotDag, DATEDIFF(dd,MIN(dateFrom),MAX(dateFrom)) + 1 AS numdagen, WknId,Code
FROM [cteRangeZiekteDagen]
GROUP BY WknId, GroupNr,Code
ORDER BY WknId
This gives me the result as :
VanDag TotDag numdagen WknId Code
----------------------- ----------------------- ----------- ----------- ----
2011-01-03 00:00:00.000 2011-01-07 00:00:00.000 5 1 V
2011-01-10 00:00:00.000 2011-01-11 00:00:00.000 2 1 V
2011-01-24 00:00:00.000 2011-01-24 00:00:00.000 1 1 Z
2011-01-27 00:00:00.000 2011-01-28 00:00:00.000 2 1 V
2011-01-31 00:00:00.000 2011-01-31 00:00:00.000 1 1 V
(5 row(s) affected)
range in output should be
V 2011-01-03 until 2011-01-11 23:59:59 <-- cause 08/01 & 09/01 are weekend days so dont interupt the range and code TA should count for the same as V
SV 2011-01-24 until 2011-01-24 23:59:59 <-- cause this counts a sick hence the 'Z' of 'ZIEK'
V 2010-01-27 until 2011-01-31 23:59:59
I have the calender table in our database but do not see how i can use it here.
cause when i group on code the MIN(Date) would be 2011-01-03 and the MAX(date) would give me 2011-01-31 for the code V witch would not be correct..
Does anybody see a solution on how to handle this via T-SQL, may even be a function or stored procedure
Any help would be really appreciated...cause im really lost right now..
Wkr,
Eddy
January 25, 2011 at 7:11 am
Nobody with an Idea on how to solve this problem.?
Schould i really fall back on external vb.net application for this.?
I hardly can believe that with the genius code from ditto authors i already have seen here, there would no one that would have an idea on how to solve this problem..
Still hoping ..;
Wkr,
Eddy
January 25, 2011 at 9:12 am
Seems to me the way to apply the use of a calendar table will need to be based on the code for the absence, so two "views" of the calendar table will be necessary. One will have only weekdays in the overall range of data, while the other will have all dates in the range. You can then at least identify starts and stops in a sequence of dates in your data, by comparing against the appropriate "view" of the calendar table. Does that help?
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 25, 2011 at 10:15 am
The idea of 2 views i can understand and is possible.
But my main problem is still current
if we forget 2 seconds about the Z ranges but only concentrate on the V days
how can i detect where 1 periode stops and another begins
if i have 2x 5 working days following each other, giving 14 days, that i can manage
but if after the second week there is a following periode that starts like saying 2 months later
there i have the problem of detecting this, my routine would show 1 range where it should show 2 in that case
finally i should have a solution that can handle the following:
if a range stops on friday, and the next range starts the first monday after this friday then it is considered 1 range
if a range stops on friday and the next range starts not on the following weekday then it should be considered a new range
But how does one handle that.?
Wkr,
Eddy
January 25, 2011 at 11:31 am
This is going to be hard to describe, and I wish I had an instance of SQL Server handy so I could take your sample data and write some T-SQL code, but the idea behind the view of the calendar table with only the weekdays, is that this view could contain a row_number, and because the next record in the view after a record with a date on Friday would be a date representing Monday, and that these 2 records would have consecutive row_number values, which would allow you to detect when records in the employee absence data don't match against consecutive values of the view. Does that make sense?
I know there are some posts in the forums here that address detecting sequences, and I also know that one of Itzik Ben-Gan's books on SQL Server specifically addresses it and has code samples to show exactly how, using a similar technique, and using a "Tally Table", or Calendar table, to do so.
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 26, 2011 at 1:46 am
Hi Steve,
I have found the article and the sample document you where talking about:
Webpage : http://www.manning.com/nielsen/SampleChapter5.pdf
I have tried several approaches based on this, just as my primary solution was based on this,but that was a trick i had found here on the forum.
And i know for sure it should be able to solve but sofar im still struggling with the case where there is a gap of more than 3 days
i should be able to remember the end day of the last range en compare that with a datediff to the start day of the next
and when the days between are only week-enddays en less then 3 days difference, then the end day of the last range should be changed to the end day of the second range,
when this is done, compare the end day of the second range to the begin day of the third range and so on..
thats how i see the logic but sofar not the solution..
Tnx for your help, it is really appreciated
Wkr,
Eddy
January 26, 2011 at 4:33 am
Hi Steve,
I have almost broken all my braincells on this one, but i'm getting closer ..
The hint to the document that you posted had me change my way of thinking, for this i thank you..
If you look at the code below you will see that i already have what i need for WKNID 1 (employee 1)
even when i have mixed codes it still runs flawless,
however for WKNID 2 there is still a small problem, this is due to the fact that i have put 3 x 5 days V as startpoint for this employee
when i have 2 ranges the code seems to run fine, but on 3 consecutive ranges (do i spell this right?) , the chain gets broken,
do you see where i go wrong here.?
the code:
-- range in output should be
-- WKNID 1 :
-- V 2011-01-03 until 2011-01-14 23:59:59 <-- cause 08/01 & 09/01 are weekend days so dont interupt the range
-- V 2011-01-24 until 2011-01-30 23:59:59 <-- cause code TA should count for the same as V so dont interrupt
-- V 2011-02-7 until 2011-02-09 23:59:59
-- Z 2011-02-10 until 2011-02-23 23:59:59 <-- Is sick over longer periode count as 1 piece
-- V 2011-02-24 until 2011-03-09 23:59:59 <-- start holliday right after sickness
--
-- WKNID2 :
-- V 2011-01-02 until 2011-01-25 23:59:59 <-- 3 weeks of holliday, code runs with errors here
-- Z 2011-01-27 until 2011-02-23 23:59:59 <-- Sickness
-- V 2011-02-25 until 2011-02-25 23:59:59 <-- counts as V cause CA is included, works fine
-- For WKNID 1 the code runs as it should with the exspected results
-- however for WKNID 2 there is a problem because he starts with 3 periods with a duration of 5 days, and the cain is broken somewhere
DECLARE @afw TABLE(id INT IDENTITY(1,1) PRIMARY KEY,
wkn_id INT,
code VARCHAR(2),
datefrom datetime,
dateuntil DATETIME
)
INSERT INTO @afw
( wkn_id ,
code ,
datefrom ,
dateuntil
)
SELECT 1, 'V', '2011-01-03', '2011-01-07 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-10', '2011-01-14 23:59:59'
UNION ALL
SELECT 1, 'V', '2011-01-24', '2011-01-29 23:59:59'
UNION ALL
SELECT 1, 'TA', '2011-01-30', '2011-01-30 23:59:59' -- code TA count for the same as code V, this would not change the range
UNION ALL
SELECT 1, 'V', '2011-02-07', '2011-02-09 23:59:59'
UNION ALL
SELECT 1, 'Z', '2011-02-10', '2011-02-23 23:59:59'
UNION ALL
SELECT 1, 'AC', '2011-02-24', '2011-02-25 23:59:59' -- code CA count for the same as code V, this would not change the range
UNION ALL
SELECT 1, 'V', '2011-02-28', '2011-03-09 23:59:59'
UNION ALL
SELECT 2, 'V', '2011-01-02', '2011-01-07 23:59:59'
UNION ALL
SELECT 2, 'V', '2011-01-10', '2011-01-14 23:59:59'
UNION ALL
SELECT 2, 'V', '2011-01-17', '2011-01-21 23:59:59'
UNION ALL
SELECT 2, 'TA', '2011-01-24', '2011-01-25 23:59:59' -- code TA count for the same as code V, this would not change the range
UNION ALL
SELECT 2, 'Z', '2011-01-27', '2011-02-23 23:59:59'
UNION ALL
SELECT 2, 'AC', '2011-02-25', '2011-02-25 23:59:59' -- code CA count for the same as code V, this would not change the range
;WITH [cte1] (dateFrom,DateUntil, GroupNr, WknId, Code)
AS
(
SELECT a.datefrom,
a.dateuntil
, DATEADD(dd, ROW_NUMBER() OVER (PARTITION BY a.[WKN_ID] ORDER BY datefrom DESC),a.datefrom)
, a.[WKN_ID]
, CASE WHEN a.code IN ('V','TA','AC') THEN 'V' ELSE 'Z' END AS code
FROM @afw AS a
)
--SELECT * FROM cteRangeZiekteDagen
,
cte3 AS (
SELECT a.dateFrom,
a.DateUntil ,
a.GroupNr ,
a.WknId ,
a.Code ,
Until = CASE WHEN b.DateUntil IS NULL THEN a.DateUntil ELSE b.DateUntil END
FROM cte1 AS a LEFT JOIN cte1 AS b ON a.WknId = b.WknId AND b.GroupNr > a.GroupNr AND a.GroupNr <> b.GroupNr AND a.Code = b.Code AND DATEDIFF(dd,a.DateUntil,b.dateFrom) <= 3
)
,
cte4 AS(
SELECT dateFrom ,
DateUntil ,
GroupNr ,
WknId ,
Code ,
Until,
ROW_NUMBER() OVER (PARTITION BY WknId,Until ORDER BY GroupNr) AS rownum FROM cte3
)
SELECT dateFrom ,
Until AS newEndDate,
DateUntil AS OldEndDate,
GroupNr ,
WknId ,
Code ,
rownum FROM cte4 WHERE rownum = 1
ORDER BY WknId, dateFrom
the output i get when i run this is:
dateFrom newEndDate OldEndDate GroupNr WknId Code rownum
----------------------- ----------------------- ----------------------- ----------------------- ----------- ---- --------------------
2011-01-03 00:00:00.000 2011-01-14 23:59:59.000 2011-01-07 23:59:59.000 2011-01-11 00:00:00.000 1 V 1
2011-01-24 00:00:00.000 2011-01-30 23:59:59.000 2011-01-29 23:59:59.000 2011-01-30 00:00:00.000 1 V 1
2011-02-07 00:00:00.000 2011-02-09 23:59:59.000 2011-02-09 23:59:59.000 2011-02-11 00:00:00.000 1 V 1
2011-02-10 00:00:00.000 2011-02-23 23:59:59.000 2011-02-23 23:59:59.000 2011-02-13 00:00:00.000 1 Z 1
2011-02-24 00:00:00.000 2011-03-09 23:59:59.000 2011-02-25 23:59:59.000 2011-02-26 00:00:00.000 1 V 1
2011-01-02 00:00:00.000 2011-01-14 23:59:59.000 2011-01-07 23:59:59.000 2011-01-08 00:00:00.000 2 V 1 <-- this is wrong
2011-01-10 00:00:00.000 2011-01-21 23:59:59.000 2011-01-14 23:59:59.000 2011-01-15 00:00:00.000 2 V 1 <-- this is wrong
2011-01-17 00:00:00.000 2011-01-25 23:59:59.000 2011-01-21 23:59:59.000 2011-01-21 00:00:00.000 2 V 1 <-- this is wrong
2011-01-27 00:00:00.000 2011-02-23 23:59:59.000 2011-02-23 23:59:59.000 2011-01-29 00:00:00.000 2 Z 1
2011-02-25 00:00:00.000 2011-02-25 23:59:59.000 2011-02-25 23:59:59.000 2011-02-26 00:00:00.000 2 V 1
(10 row(s) affected)
here you see that for WknId 1, all is fine
but for WknId 2 there is still a problem
All help is really appreciated,
Wkr,
Eddy
January 27, 2011 at 7:14 am
I managed to dig out the SQL book that talks about the concept of identifying sequences and such, and was able to find a routine that identifies overlapping sessions from a table of sessions, and uses DISTINCT and WHERE clause NOT EXISTS logic to mingle sessions for the same user for the same app that overlap into a single record with a start time and end time.
The idea was to logically define what constitutes a start time, as well as what constitutes and end time, and then match the start and end times together using a query with a correlated subquery.
The data the book was working with had time values involved (as part of datetime fields), so that got me thinking about what to do if your data could reflect a 1/2 day of vacation, for example, such as someone leaving at noon on Friday to take care of a doctor's appointment, for example, and using vacation time to cover it. Then, purely working with the date alone would be inadequate.
Until I can get a SQL instance up and running, I'm going to have difficulty with presenting much more than ideas. I'm awaiting the arrival of a 2008 R2 Developer Edition, and I'm not sure when it's going to arrive or how long it will be before I can get it installed, so for now I just hope the ideas are helping, and you have made good progress. I think the issue with the existing query is that it doesn't operate on a sufficiently well-defined set of start and end times. Let me know how things are going...
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 27, 2011 at 10:09 am
Hi steve,
Tnx for your reply.
No, this is no concern, all days are inserted as FULL days, thats also why i have put the enddates time as 23:59:59 in the code.
But my guess is, if the code so far is working well when there are 2 ranges following each other, and not when there are 3 ranges.
Then i would have the need for a recursive cte,
That is also a problem, cause that makes it really dificult as i do understand the concept of the recursive cte but have troubles writing one,
They are so new to me, that i'm a bit afraid of them :hehe:
Thank you verry much for your help in trying to solve this puzzle,
Just damn pitty i can not present a sql instance to ya, otherwise i would 😉
Wkr,
Eddy
January 27, 2011 at 10:42 am
You don't need any recursion for this. Start by thinking about the logical requirements for a time to qualify as a start time. For example, there must be no other records where this records sequence number within it's group is smaller and the difference is > 1. Similarly, for end times, there's no sequence number within it's group that's larger and the difference is > 1. "It's group" is where employee id and code are equal (the code as adjusted for similar codes). Those would be your NOT EXISTS conditions for each CTE,
and the sequence number comes into play as a result of your calendar table.
Does that get you going in the right direction?
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 27, 2011 at 11:12 am
Hi steve,
I think i understand what you mean..,
Tomorrow morning, first thing i do when i get at work (here its now 7PM)
is trying what you suggested..
Keep you updated,
Wkr,
Eddy
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply