December 11, 2012 at 9:11 pm
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_MENTER_DT CONSECUTIVE_D
Boo K K5/1/2012 11:55:00 PM1
Boo K K5/2/2012 11:30:00 PM2
Boo K K5/4/2012 10:30:00 AM1
LIAW S 4/30/2012 11:48:52 PM1
LIAW S5/1/2012 00:11:07 AM2
LIAW S5/1/2012 11:59:07 AM2
LIAW S 5/1/2012 4:42:02 AM2
LIAW S5/2/2012 1:10:09 AM3
LIAW S5/2/2012 1:43:06 AM3
LIAW S5/4/2012 2:17:47 AM1
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
December 12, 2012 at 4:17 am
What about weekends and public holidays? Should they restart your counter?
December 12, 2012 at 4:30 am
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)
December 12, 2012 at 4:43 am
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.
December 12, 2012 at 9:00 am
thanks, i will try those codes when i get b to office. Any simpler suggestion anyone as this is quite complicated.
December 12, 2012 at 9:16 am
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);
December 12, 2012 at 9:39 am
Oops - didn't think row_number() was available in v2005.
Still seems new.
Cursors never.
DTS - only when needed and never to control.
December 12, 2012 at 5:36 pm
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
December 12, 2012 at 7:28 pm
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.
December 12, 2012 at 10:32 pm
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);
December 12, 2012 at 10:49 pm
Hi Lyn,
Thanks for ur reply i will test that soon. Currently i just do not know how to implement my 8 SQL statements in SSIS. TROUBLED:( Just by using this will be able to help me derive my target table but my supervisore hopes to see a neat process using SSIS.
I did it by, using SQL statement 1 will create T1, then i use SQL statment to query from t1 to derive T2, then drop T1........to SQL8 to create TARGET_TABLE, drop T7. I know i have used a very noob way. How can i translate all these and use it in SSIS?
December 13, 2012 at 1:19 am
First, you didn't ask for an SSIS solution, you asked for a SQL solution.
Second, use the right tool for the job. This isn't a job for SSIS, it is a job for SQL.
Third, I have another SQL solution:
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)
)
, GrpDates as (
select
PassM,
EnterDt,
GrpDate = dateadd(dd, dense_rank() over (partition by PassM order by dateadd(dd,datediff(dd,0,EnterDt),0)) * -1, dateadd(dd,datediff(dd,0,EnterDt),0)),
ConsecutiveD
from
TestData
)
select
PassM,
EnterDt,
ConsecutiveD,
dr = dense_rank() over (partition by PassM, GrpDate order by dateadd(dd,datediff(dd,0,EnterDt),0))
from
GrpDates
order by
PassM,
EnterDt;
When you run the code above, compare the ConsecutiveD column (the expected results) to the dr column (the computed Consecutive Days column).
December 13, 2012 at 1:46 am
Hi lyn,
I tested your solutions and it works. However they keep prompt me "the OVER SQL constructs or statements is not supported". I just ignored that. Do u have any idea whats that?
And ya i am sry, i did not asked for an SSIS solution and i do know this is an SQL job. However i am asked to see if i can do this in SSIS, which i am researching for it now. So asking for ur opinion.
Anyway thanks for replying.
Thanks,
10e5x
P.S I joined 3 forums to learn on SQL server and this forum is the best among all. Fastest reply rate with accuracy. THANKS!!!
December 13, 2012 at 2:25 am
10e5x (12/13/2012)
Hi lyn,I tested your solutions and it works. However they keep prompt me "the OVER SQL constructs or statements is not supported". I just ignored that. Do u have any idea whats that?
And ya i am sry, i did not asked for an SSIS solution and i do know this is an SQL job. However i am asked to see if i can do this in SSIS, which i am researching for it now. So asking for ur opinion.
Anyway thanks for replying.
Thanks,
10e5x
P.S I joined 3 forums to learn on SQL server and this forum is the best among all. Fastest reply rate with accuracy. THANKS!!!
First, look with eye, try spelling my name correctly, it is spelled with 2 n's not one.
Second, what version of SQL Server are you running?
Third, what is the full error message you are getting? The dense_rank function has been available since SQL Server 2005 and should work without giving you any error messages.
December 13, 2012 at 2:30 am
Hi Lynn,
Firstly, i am sorry, Lynn.
Secondly, i am using SQL Server 2005
Lastly, that sentence is the entire error sentence. It appears when i click the ! functions in query builder.
Regards,
10e5x
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply