Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Need help on creating SQL statement for deriving Consecutive worked days. Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 9:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi all,
Anyone can help me in deriving the sql statement to achieve the new column CONSECUTIVE_D?
Consecutive days should be solely based on ENTER_DT regardless of the no. of times. And if an employee enters many times in the same day it is still counted as one consecutive days. And if an employee enters on 5/13/2012 00:00:59 AM and the next entry is 5/14/2012 23:59:55, it is still consider as consecutive. Better e.g

PASS_M ENTER_DT CONSECUTIVE_D
Boo K K 5/1/2012 11:55:00 PM 1
Boo K K 5/2/2012 11:30:00 PM 2
Boo K K 5/4/2012 10:30:00 AM 1
LIAW S 4/30/2012 11:48:52 PM 1
LIAW S 5/1/2012 00:11:07 AM 2
LIAW S 5/1/2012 11:59:07 AM 2
LIAW S 5/1/2012 4:42:02 AM 2
LIAW S 5/2/2012 1:10:09 AM 3
LIAW S 5/2/2012 1:43:06 AM 3
LIAW S 5/4/2012 2:17:47 AM 1

How can i derive the consecutive column? Please note the last row of the table, consecutive is set b to 1 as the employee has not enter on the 5/3/2012.

This is what i tried but its wrong:
SELECT PASS_M, ENTRY_DT, DATEDIFF(D, MIN(ENTRY_DT) OVER (PARTITION BY PASS_M), ENTRY_DT) + 1 AS CONSECTUTIVE_DAYS
INTO TEMP_TARGET
FROM TEMP_5
ORDER BY PASS_M, ENTRY_DT;


My logic is to make use of DATEDIFF(day, ENTRY_DT, PrevEntry_DT).
Counter = 1;
If DATEDIFF(day, ENTRY_DT, PrevEntry_DT) = 1, +1 to Counter. Else counter will always remain as 1.
PrevEntry_DT should be the previous ENTRY_DT of the row aboved, same PASS_M of cuz. (maybe can make use of rownumber or?)
However i do not how to apply!
Thanks,
10e5x
Post #1395412
Posted Wednesday, December 12, 2012 4:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
What about weekends and public holidays? Should they restart your counter?


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1395581
Posted Wednesday, December 12, 2012 4:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi,
Thanks for replying. We should ignore whether it is weekend or pub holidays. Just derive consecutive from the entry date. For example: staff a enters on a fri sat sun and following tues. so on the entry record of fri will be 1 consec day, sat 2 and sun it will show 3 consecutive day but on 1 for tues. Will reset the counter only there is a gap(no back to back entry date)
Post #1395587
Posted Wednesday, December 12, 2012 4:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
Could probably be simpler but this separates out the steps.


declare @t table (s varchar(10), dt datetime)
insert @t select 'A', '20120101'
insert @t select 'A', '20120102'
insert @t select 'A', '20120102 01:00'
insert @t select 'A', '20120103'
insert @t select 'A', '20120105'
insert @t select 'A', '20120106'
insert @t select 'A', '20120106'
insert @t select 'B', '20120101'
insert @t select 'B', '20120103'
insert @t select 'B', '20120104'

;with cte as
(
select *, dte = dateadd(dd,datediff(dd,0,dt),0) from @t
) ,
cte2 as
(
select *, num = (select count(distinct t2.dte) from cte t2 where t2.dte<=t1.dte and t1.s=t2.s) ,
gap = case when exists (select * from cte t3 where t3.dte = t1.dte-1 and t1.s = t3.s) then 0 else 1 end
from cte t1
)
select * ,
val = num - (select max(num) from cte2 t2 where t2.dte<=t1.dte and t2.gap=1 and t1.s=t2.s)+1
from cte2 t1



Cursors never.
DTS - only when needed and never to control.
Post #1395591
Posted Wednesday, December 12, 2012 9:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
thanks, i will try those codes when i get b to office. Any simpler suggestion anyone as this is quite complicated.
Post #1395734
Posted Wednesday, December 12, 2012 9:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 2,422, Visits: 7,439
Assuming I've understood what you want, then something like this: -
SELECT StaffId,
MIN(EntryDate) AS firstDateEntrySequence, MAX(EntryDate) AS lastDateEntrySequence,
DATEDIFF(dd,MIN(EntryDate),MAX(EntryDate))+1 AS NumberOfDaysInSequence
FROM (SELECT StaffId, EntryDate,
DATEADD(dd, - ROW_NUMBER() OVER (PARTITION BY StaffId ORDER BY EntryDate), EntryDate)
FROM #yourSampleData
GROUP BY StaffId, EntryDate) a(StaffId, EntryDate, EntryGroup)
GROUP BY StaffId, EntryGroup;

That's based on the following sample data, since you chose not to supply any: -
IF object_id('tempdb..#yourSampleData') IS NOT NULL
BEGIN
DROP TABLE #yourSampleData;
END;

SELECT StaffId, EntryDate
INTO #yourSampleData
FROM (VALUES(1, '2012-12-12'),(1, '2012-12-13'),(1, '2012-12-15'),
(2, '2012-12-12'),(2, '2012-12-14'),(2, '2012-12-16')
)a(StaffId, EntryDate);


SELECT StaffId, EntryDate
INTO #yourSampleData
FROM (SELECT 1, '2012-12-12'
UNION ALL SELECT 1, '2012-12-13'
UNION ALL SELECT 1, '2012-12-15'
UNION ALL SELECT 2, '2012-12-12'
UNION ALL SELECT 2, '2012-12-14'
UNION ALL SELECT 2, '2012-12-16'
)a(StaffId, EntryDate);




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1395750
Posted Wednesday, December 12, 2012 9:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
Oops - didn't think row_number() was available in v2005.
Still seems new.



Cursors never.
DTS - only when needed and never to control.
Post #1395768
Posted Wednesday, December 12, 2012 5:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi Cadavre,
Thank you v much for ur help. Btw may i tried and your method returned my all 1 as numberofdays.
Oh yes u are right, i should have uploaded a sample data. I will do it v soon. Need to mask a few fields.
Thanks,
10e5x
Post #1395942
Posted Wednesday, December 12, 2012 7:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
nigelrivett (12/12/2012)
Could probably be simpler but this separates out the steps.


declare @t table (s varchar(10), dt datetime)
insert @t select 'A', '20120101'
insert @t select 'A', '20120102'
insert @t select 'A', '20120102 01:00'
insert @t select 'A', '20120103'
insert @t select 'A', '20120105'
insert @t select 'A', '20120106'
insert @t select 'A', '20120106'
insert @t select 'B', '20120101'
insert @t select 'B', '20120103'
insert @t select 'B', '20120104'

;with cte as
(
select *, dte = dateadd(dd,datediff(dd,0,dt),0) from @t
) ,
cte2 as
(
select *, num = (select count(distinct t2.dte) from cte t2 where t2.dte<=t1.dte and t1.s=t2.s) ,
gap = case when exists (select * from cte t3 where t3.dte = t1.dte-1 and t1.s = t3.s) then 0 else 1 end
from cte t1
)
select * ,
val = num - (select max(num) from cte2 t2 where t2.dte<=t1.dte and t2.gap=1 and t1.s=t2.s)+1
from cte2 t1


Hi nigelrivet,
tested your method it works. but it is very complicated. btw may i ask u if i am using this type of TSQL method, will there be overhead? Cuz i will be working on huge number of data eventually. Around 200k rows. Would it crash? I already have around a chunk of tsql statments working with 5 temp cte tables, will it crash or run super slow?

Thanks alot.
Post #1395958
Posted Wednesday, December 12, 2012 10:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
Just another option to consider, think of it as food for thought:


with TestData as (
select
PassM,
cast(EnterDt as datetime) EnterDt,
ConsecutiveD
from
(values
('Boo K K','5/1/2012 11:55:00 PM', 1),
('Boo K K','5/2/2012 11:30:00 PM', 2),
('Boo K K','5/4/2012 10:30:00 AM', 1),
('LIAW S','4/30/2012 11:48:52 PM', 1),
('LIAW S','5/1/2012 00:11:07 AM', 2),
('LIAW S','5/1/2012 11:59:07 AM', 2),
('LIAW S','5/1/2012 4:42:02 AM', 2),
('LIAW S','5/2/2012 1:10:09 AM', 3),
('LIAW S','5/2/2012 1:43:06 AM', 3),
('LIAW S','5/4/2012 2:17:47 AM', 1)
)dt(PassM, EnterDt, ConsecutiveD)
)
, UniqueDts as (
select distinct
PassM,
dateadd(dd,datediff(dd,0,EnterDt),0) UniqEnterDt
from
TestData
)
, GrpDates as (
select
PassM,
UniqEnterDt,
GrpDate = dateadd(dd,row_number() over (partition by PassM order by UniqEnterDt) *-1,UniqEnterDt),
rn = row_number() over (partition by PassM order by UniqEnterDt)
from
UniqueDts
)
,ConsecutiveDts as (
select
PassM,
UniqEnterDt,
rn = row_number() over (partition by PassM, GrpDate order by UniqEnterDt)
from
GrpDates
)
select
td.PassM,
td.EnterDt,
cd.rn as CompConsecutiveD,
td.ConsecutiveD
from
TestData td
inner join ConsecutiveDts cd
on (td.PassM = cd.PassM and
dateadd(dd,datediff(dd,0,td.EnterDt),0) = cd.UniqEnterDt);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1395985
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse