10e5x (12/12/2012)
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
That's because you originally said "date" when talking about entry, not datetime.
Change my code like this: -
SELECT PassM, CAST(EnterDt AS DATETIME) EnterDt
INTO #yourSampleData
FROM (SELECT 'Boo K K','5/1/2012 11:55:00 PM'
UNION ALL SELECT 'Boo K K','5/2/2012 11:30:00 PM'
UNION ALL SELECT 'Boo K K','5/4/2012 10:30:00 AM'
UNION ALL SELECT 'LIAW S','4/30/2012 11:48:52 PM'
UNION ALL SELECT 'LIAW S','5/1/2012 00:11:07 AM'
UNION ALL SELECT 'LIAW S','5/1/2012 11:59:07 AM'
UNION ALL SELECT 'LIAW S','5/1/2012 4:42:02 AM'
UNION ALL SELECT 'LIAW S','5/2/2012 1:10:09 AM'
UNION ALL SELECT 'LIAW S','5/2/2012 1:43:06 AM'
UNION ALL SELECT 'LIAW S','5/4/2012 2:17:47 AM'
)a(PassM,EnterDt);
SELECT PassM,
MIN(EnterDt) AS firstDateEntrySequence, MAX(EnterDt) AS lastDateEntrySequence,
DATEDIFF(dd,MIN(EnterDt),MAX(EnterDt))+1 AS NumberOfDaysInSequence
FROM (SELECT PassM, EnterDt,
DATEADD(dd, - ROW_NUMBER() OVER (PARTITION BY PassM ORDER BY EnterDt), EnterDt)
FROM (SELECT PassM, DATEADD(dd, DATEDIFF(dd, 0, EnterDt), 0)
FROM #yourSampleData
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, EnterDt), 0), PassM
) a(PassM, EnterDt)
GROUP BY PassM, EnterDt
) a(PassM, EnterDt, EntryGroup)
GROUP BY PassM, EntryGroup;
And it returns: -
PassM firstDateEntrySequence lastDateEntrySequence NumberOfDaysInSequence
------- ----------------------- ----------------------- ----------------------
LIAW S 2012-04-30 00:00:00.000 2012-05-02 00:00:00.000 3
Boo K K 2012-05-01 00:00:00.000 2012-05-02 00:00:00.000 2
LIAW S 2012-05-04 00:00:00.000 2012-05-04 00:00:00.000 1
Boo K K 2012-05-04 00:00:00.000 2012-05-04 00:00:00.000 1