Need help with grouping items in any given period

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with grouping items in any given period

Author

Topic

auzroz
Starting Member

5 Posts

Posted - 2009-08-04 : 18:27:14

Hello all!
I am creating a query based on a time and attendance database. There are several tables to work with, but what I am trying to do is group time codes in accordance with our attendance policy.
We currently allow sick calls to cover four days in a seven day period. I.E. I call in sick Monday, Tuesday, Thursday, Saturday - we will only count that as one sick call because it is four days in seven. Now if the number of abscences exceeds four, that is considered another abscence. I am only looking at doing this for the current pay period (14 Days), but it can go back 7 days from the first day of the pay period.
Have I confused you yet?

To keep things simple here is a sample data set for the current pay period:
Date Time Code
08/13/2009 NULL
08/12/2009 SKUN
08/11/2009 NULL
08/10/2009 SKUN
08/09/2009 SKUN
08/08/2009 SKUN
08/07/2009 NULL
08/06/2009 NULL
08/05/2009 SKUN
08/04/2009 SKUN
08/03/2009 SKUN
08/03/2009 SKUN
08/03/2009 NOSHOW
08/02/2009 SKUN
08/01/2009 NULL
07/31/2009 NULL

I have a list of time codes (this can be called via a select statement) that can be combined, but for simplicity sake, I have used SKUN and NOSHOW, there will be other time codes involved, multiple codes in a day not all are applicable to the combination.
And I would want it to look like this (Doesn't have to be as detailed, as long as I can group the different sick calls), the logic should start at the begining of the pay period to take into account a sick call made in the previous pay period:
Date Time Code Event
08/13/2009 NULL NULL
08/12/2009 SKUN SICKCALL 2 (Day 4)
08/11/2009 NULL NULL
08/10/2009 SKUN SICKCALL 2 (Day 3)
08/09/2009 SKUN SICKCALL 2 (Day 2)
08/08/2009 SKUN SICKCALL 2 (Day 1)
08/07/2009 NULL NULL
08/06/2009 NULL NULL
08/05/2009 SKUN SICKCALL 1 (Day 4)
08/04/2009 SKUN SICKCALL 1 (Day 3)
08/03/2009 SKUN SICKCALL 1 (Day 2)
08/03/2009 SKUN SICKCALL 1 (Day 2)
08/03/2009 NOSHOW SICKCALL 1 (Day 2)
08/02/2009 SKUN SICKCALL 1 (Day 1)
08/01/2009 NULL NULL
07/31/2009 NULL NULL

Things to help:
I have a number table, a date table, a time code table, and a pay period table. Currently running SQL2005. Any help would be appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-05 : 21:03:12

[code]
declare @sample table
(
Date_Time datetime,
Code varchar(10)
)
insert into @sample (Date_Time, Code)
select '08/13/2009', NULL union all
select '08/12/2009', 'SKUN' union all
select '08/11/2009', NULL union all
select '08/10/2009', 'SKUN' union all
select '08/09/2009', 'SKUN' union all
select '08/08/2009', 'SKUN' union all
select '08/07/2009', NULL union all
select '08/06/2009', NULL union all
select '08/05/2009', 'SKUN' union all
select '08/04/2009', 'SKUN' union all
select '08/03/2009', 'SKUN' union all
select '08/03/2009', 'SKUN' union all
select '08/03/2009', 'NOSHOW' union all
select '08/02/2009', 'SKUN' union all
select '08/01/2009', NULL union all
select '07/31/2009', NULL

select *,
event = case when Code is not null
then ((dense_rank() over (partition by case when Code in ('NOSHOW', 'SKUN') then 'SICKCALL' end
order by Date_Time) - 1) % 4) + 1
end
from @sample
order by Date_Time desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

auzroz
Starting Member

5 Posts

Posted - 2009-08-06 : 03:36:20

That is getting me closer, however, it seems to reset itself on any time code. I.E. WRK. which should not be attributed to an event. I would like to see the sickcall marker instead of the day if possible.
Here are the results from the above code.
dtval_date tcode_name event
2009-07-31 00:00:00.000 WRK 1
2009-08-01 00:00:00.000 NULL NULL
2009-08-02 00:00:00.000 SKUN 1
2009-08-03 00:00:00.000 SKUN 2
2009-08-04 00:00:00.000 SKUN 3
2009-08-05 00:00:00.000 SKUN 4
2009-08-06 00:00:00.000 NULL NULL
2009-08-07 00:00:00.000 NULL NULL
2009-08-08 00:00:00.000 SKUN 1
2009-08-09 00:00:00.000 SKUN 2
2009-08-10 00:00:00.000 SKUN 3
2009-08-11 00:00:00.000 NULL NULL
2009-08-12 00:00:00.000 SKUN 4
2009-08-13 00:00:00.000 NULL NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-06 : 06:07:49

[code]
DECLARE @sample TABLE
(
Date_Time datetime,
Code varchar(10)
)
INSERT INTO @sample (Date_Time, Code)
SELECT '08/13/2009', NULL UNION ALL
SELECT '08/12/2009', 'SKUN' UNION ALL
SELECT '08/11/2009', NULL UNION ALL
SELECT '08/10/2009', 'SKUN' UNION ALL
SELECT '08/09/2009', 'SKUN' UNION ALL
SELECT '08/08/2009', 'SKUN' UNION ALL
SELECT '08/07/2009', NULL UNION ALL
SELECT '08/06/2009', NULL UNION ALL
SELECT '08/05/2009', 'SKUN' UNION ALL
SELECT '08/04/2009', 'SKUN' UNION ALL
SELECT '08/03/2009', 'SKUN' UNION ALL
SELECT '08/03/2009', 'SKUN' UNION ALL
SELECT '08/03/2009', 'NOSHOW' UNION ALL
SELECT '08/02/2009', 'SKUN' UNION ALL
SELECT '08/01/2009', NULL UNION ALL
SELECT '07/31/2009', NULL

;WITH data (Date_Time, Code, SickCall, SickCall_ID)
AS
(
SELECT Date_Time, Code,
SickCall = CASE WHEN Code IN ('NOSHOW', 'SKUN') THEN 'SICKCALL' END,
SickCall_ID = dense_rank() OVER (PARTITION BY CASE WHEN Code IN ('NOSHOW', 'SKUN') THEN 'SICKCALL' END
ORDER BY Date_Time)
FROM @sample
)
SELECT Date_Time, Code,
event = SickCall + ' '
+ CONVERT(varchar(10), ((SickCall_ID - 1)/ 4) + 1) + ' '
+ '(DAY ' + CONVERT(varchar(10), (SickCall_ID - 1) % 4 + 1) + ')'
FROM data
ORDER BY Date_Time DESC

/*
Date_Time Code event
------------ ---------- ------------------------------------
2009-08-13 NULL NULL
2009-08-12 SKUN SICKCALL 2 (Day 4)
2009-08-11 NULL NULL
2009-08-10 SKUN SICKCALL 2 (Day 3)
2009-08-09 SKUN SICKCALL 2 (Day 2)
2009-08-08 SKUN SICKCALL 2 (Day 1)
2009-08-07 NULL NULL
2009-08-06 NULL NULL
2009-08-05 SKUN SICKCALL 1 (Day 4)
2009-08-04 SKUN SICKCALL 1 (Day 3)
2009-08-03 SKUN SICKCALL 1 (Day 2)
2009-08-03 SKUN SICKCALL 1 (Day 2)
2009-08-03 NOSHOW SICKCALL 1 (Day 2)
2009-08-02 SKUN SICKCALL 1 (Day 1)
2009-08-01 NULL NULL
2009-07-31 NULL NULL

(16 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

auzroz
Starting Member

5 Posts

Posted - 2009-08-06 : 17:41:01

KH, I want to thank you for your help. Almost there.

I changed the sample set to be:
DECLARE @sample TABLE
(
dtval_date datetime,
tcode_name varchar(10)
)
INSERT INTO @sample (dtval_date, tcode_name)
SELECT '08/13/2009', NULL UNION ALL
SELECT '08/12/2009', 'SKUN' UNION ALL
SELECT '08/11/2009', NULL UNION ALL
SELECT '08/10/2009', 'SKUN' UNION ALL
SELECT '08/09/2009', 'MEAL' UNION ALL
SELECT '08/09/2009', 'WRK' UNION ALL
SELECT '08/09/2009', 'WRK' UNION ALL
SELECT '08/08/2009', 'MEAL' UNION ALL
SELECT '08/08/2009', 'WRK' UNION ALL
SELECT '08/08/2009', 'WRK' UNION ALL
SELECT '08/07/2009', NULL UNION ALL
SELECT '08/06/2009', NULL UNION ALL
SELECT '08/05/2009', 'MEAL' UNION ALL
SELECT '08/05/2009', 'WRK' UNION ALL
SELECT '08/05/2009', 'WRK' UNION ALL
SELECT '08/04/2009', 'WRK' UNION ALL
SELECT '08/04/2009', 'WRK' UNION ALL
SELECT '08/04/2009', 'MEAL' UNION ALL
SELECT '08/03/2009', 'SKUN' UNION ALL
SELECT '08/03/2009', 'SKUN' UNION ALL
SELECT '08/02/2009', 'SKUN' UNION ALL
SELECT '08/01/2009', NULL UNION ALL
SELECT '07/31/2009', 'WRK'

And these were the results:
dtval_date tcode_name event
2009-08-13 00:00:00.000 NULL NULL
2009-08-12 00:00:00.000 SKUN SICKCALL 1 (DAY 4)
2009-08-11 00:00:00.000 NULL NULL
2009-08-10 00:00:00.000 SKUN SICKCALL 1 (DAY 3)
2009-08-09 00:00:00.000 WRK NULL
2009-08-09 00:00:00.000 WRK NULL
2009-08-09 00:00:00.000 MEAL NULL
2009-08-08 00:00:00.000 MEAL NULL
2009-08-08 00:00:00.000 WRK NULL
2009-08-08 00:00:00.000 WRK NULL
2009-08-07 00:00:00.000 NULL NULL
2009-08-06 00:00:00.000 NULL NULL
2009-08-05 00:00:00.000 MEAL NULL
2009-08-05 00:00:00.000 WRK NULL
2009-08-05 00:00:00.000 WRK NULL
2009-08-04 00:00:00.000 WRK NULL
2009-08-04 00:00:00.000 WRK NULL
2009-08-04 00:00:00.000 MEAL NULL
2009-08-03 00:00:00.000 SICKU SICKCALL 1 (DAY 2)
2009-08-02 00:00:00.000 SKUN SICKCALL 1 (DAY 1)
2009-08-01 00:00:00.000 NULL NULL
2009-07-31 00:00:00.000 WRK NULL

It doesn't seem to be taking into account the 7 day window from the first (lowest date) sick call.
Thank you again for the help.
----
Edited result set and clarified 7 day restriction

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-06 : 21:01:22

what is the expected result for the sample you posted above ?

By the way what is SKUN ?

KH
[spoiler]Time is always against us[/spoiler]

auzroz
Starting Member

5 Posts

Posted - 2009-08-06 : 22:38:17

expected result should look like:

dtval_date tcode_name event
2009-08-13 00:00:00.000 NULL NULL
2009-08-12 00:00:00.000 SKUN SICKCALL 2 (DAY 2)
2009-08-11 00:00:00.000 NULL NULL
2009-08-10 00:00:00.000 SKUN SICKCALL 2 (DAY 1)
2009-08-09 00:00:00.000 WRK NULL
2009-08-09 00:00:00.000 WRK NULL
2009-08-09 00:00:00.000 MEAL NULL
2009-08-08 00:00:00.000 MEAL NULL
2009-08-08 00:00:00.000 WRK NULL
2009-08-08 00:00:00.000 WRK NULL
2009-08-07 00:00:00.000 NULL NULL
2009-08-06 00:00:00.000 NULL NULL
2009-08-05 00:00:00.000 MEAL NULL
2009-08-05 00:00:00.000 WRK NULL
2009-08-05 00:00:00.000 WRK NULL
2009-08-04 00:00:00.000 WRK NULL
2009-08-04 00:00:00.000 WRK NULL
2009-08-04 00:00:00.000 MEAL NULL
2009-08-03 00:00:00.000 SICKU SICKCALL 1 (DAY 2)
2009-08-02 00:00:00.000 SKUN SICKCALL 1 (DAY 1)
2009-08-01 00:00:00.000 NULL NULL
2009-07-31 00:00:00.000 WRK NULL

They key is that from the first sick call, there is a seven day window for sick calls where four abscences are counted as one. If there are 5 - 7 absences in a seven day window, these are counted as a second absence and the seven day window resets with the first date of the second set.
I.E
If I called in 8/1/09, and was absent 8/1 and 8/4, that is one sick call. If I am absent from 8/5 - 8/8, that is a second sick call. However, if I called in sick on 8/1 and was gone from 8/1 - 8/2 and then again 8/5-8/6, this is one absence since it is 4 days out of 7. I hope this helps clarify a bit.

SKUN is simply a sick call that is unpaid (SicK UNpaid).

auzroz
Starting Member

5 Posts

Posted - 2009-08-11 : 17:29:36

bump.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources