T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

Question

Post reply

T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
January 3, 2014 at 3:06 am

#301869

INPUTS:
CREATE TABLE COMP_RATINGS
(
EID INT,
RATING VARCHAR(20),
AbsenceStartDate DATETIME,
AbsenceEndDate DATETIME
)
INSERT INTO COMP_RATINGS VALUES
(769,'BBB','2011-06-30','2011-09-30'),
(769,'BBB','2011-12-31','2012-03-31'),
(769,'BBB','2012-03-31','2012-06-30')
SELECT * FROM COMP_RATINGS;
OUTPUT :
769BBB2011-06-30 00:00:00.0002011-09-30 00:00:00.000
769BBB2011-12-31 00:00:00.0002012-03-31 00:00:00.000
769BBB2012-03-31 00:00:00.0002012-06-30 00:00:00.000
EXPECTED OUTPUT:
769BBB2011-06-30 00:00:00.0002011-09-30 00:00:00.000
769BBB2011-12-31 00:00:00.0002012-06-30 00:00:00.000
CAN ANY ONE HELP ME TO GET THIS OUT PUT.
THANKS
BHANU

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 1

IF YOU NEED ANY MORE DETAILS OR CLARIFICATION ON THE REQUIREMENT

PLEASE ASK I WILL PROVIDE CLARIFICATION.

THANKS

BHANU

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Please stop shouting.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 3

DECLARE @COMP_RATINGS TABLE

(

EID INT,

RATING VARCHAR(20),

AbsenceStartDate DATETIME,

AbsenceEndDate DATETIME

)

INSERT INTO @COMP_RATINGS VALUES

(769,'BBB','2011-06-30','2011-09-30'),

(769,'BBB','2011-12-31','2012-03-31'),

(769,'BBB','2012-03-31','2012-06-30')

SELECT * FROM

(

select C.EID,C.RATING,C.AbsenceStartDate,ISNULL(C1.AbsenceEndDate,C.AbsenceEndDate)AbsenceEndDate from @COMP_RATINGS C

LEFT JOIN @COMP_RATINGS C1

On C.EID =C1.EID

AND C.RATING = C1.RATING

AND (C.AbsenceStartDate = C1.AbsenceEndDate OR C.AbsenceEndDate = C1.AbsenceStartDate)

)E

WHERE AbsenceEndDate <>AbsenceStartDate

Regards,
Mitesh OSwal
+918698619998

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 4

HI MITESH,

THANK YOU FOR YOUR HELP.

THE QUERY IS SATISFYING THE SAMPLE GIVEN DATA.

BUT IT IS NOT SOLVING MY ORIGINAL PROBLEM.

THE ORIGINAL DATA IN THE TABLE:

5364914b+2011-09-30 00:00:00.00000002011-12-31 00:00:00.0000000

5364914bb2011-06-30 00:00:00.00000002011-09-30 00:00:00.0000000

5364914bb2011-12-31 00:00:00.00000002012-03-31 00:00:00.0000000

5364914bb2012-03-31 00:00:00.00000002012-06-30 00:00:00.0000000

5364914bb+2009-12-31 00:00:00.00000002010-03-31 00:00:00.0000000

5364914bb+2010-03-31 00:00:00.00000002010-06-30 00:00:00.0000000

5364914bb+2010-06-30 00:00:00.00000002010-09-30 00:00:00.0000000

5364914bb+2010-09-30 00:00:00.00000002010-12-31 00:00:00.0000000

5364914bb+2010-12-31 00:00:00.00000002011-03-31 00:00:00.0000000

5364914bb+2011-03-31 00:00:00.00000002011-06-30 00:00:00.0000000

5364914bb+2012-06-30 00:00:00.00000002012-12-31 00:00:00.0000000

5364914bb+2012-12-31 00:00:00.00000002013-03-31 00:00:00.0000000

THE REQUIRED OUTPUT IS :

5364914b+2011-09-30 00:00:00.00000002011-12-31 00:00:00.0000000

5364914bb2011-06-30 00:00:00.00000002011-09-30 00:00:00.0000000

5364914bb2011-12-31 00:00:00.00000002012-06-30 00:00:00.0000000

5364914bb+2009-12-31 00:00:00.00000002011-06-30 00:00:00.0000000

5364914bb+2012-06-30 00:00:00.00000002013-03-31 00:00:00.0000000

NOTE :

1. IF THE SEQUENCE IS CONTINUING IT SHOULD DISPLAY ONLY ONE RECORDS IF THE SEQUENCE CHANGES THE IT SHOULD DISPLAY DIFFERENT RECORDS FOR THE SAME RATING ( BB OR B+ OR BB+)

IF YOU PROVIDE THE QUERY TO MEETS THE ABOVE REQUIRED OUTPUT IT WILL BE VERY HELPFUL TO ME.

THANKS

BHANU

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

kbhanu15 (1/3/2014)
HI MITESH,
THANK YOU FOR YOUR HELP.
THE QUERY IS SATISFYING THE SAMPLE GIVEN DATA.
BUT IT IS NOT SOLVING MY ORIGINAL PROBLEM.
THE ORIGINAL DATA IN THE TABLE:
5364914b+2011-09-30 00:00:00.00000002011-12-31 00:00:00.0000000
5364914bb2011-06-30 00:00:00.00000002011-09-30 00:00:00.0000000
5364914bb2011-12-31 00:00:00.00000002012-03-31 00:00:00.0000000
5364914bb2012-03-31 00:00:00.00000002012-06-30 00:00:00.0000000
5364914bb+2009-12-31 00:00:00.00000002010-03-31 00:00:00.0000000
5364914bb+2010-03-31 00:00:00.00000002010-06-30 00:00:00.0000000
5364914bb+2010-06-30 00:00:00.00000002010-09-30 00:00:00.0000000
5364914bb+2010-09-30 00:00:00.00000002010-12-31 00:00:00.0000000
5364914bb+2010-12-31 00:00:00.00000002011-03-31 00:00:00.0000000
5364914bb+2011-03-31 00:00:00.00000002011-06-30 00:00:00.0000000
5364914bb+2012-06-30 00:00:00.00000002012-12-31 00:00:00.0000000
5364914bb+2012-12-31 00:00:00.00000002013-03-31 00:00:00.0000000
THE REQUIRED OUTPUT IS :
5364914b+2011-09-30 00:00:00.00000002011-12-31 00:00:00.0000000
5364914bb2011-06-30 00:00:00.00000002011-09-30 00:00:00.0000000
5364914bb2011-12-31 00:00:00.00000002012-06-30 00:00:00.0000000
5364914bb+2009-12-31 00:00:00.00000002011-06-30 00:00:00.0000000
5364914bb+2012-06-30 00:00:00.00000002013-03-31 00:00:00.0000000
NOTE :
1. IF THE SEQUENCE IS CONTINUING IT SHOULD DISPLAY ONLY ONE RECORDS IF THE SEQUENCE CHANGES THE IT SHOULD DISPLAY DIFFERENT RECORDS FOR THE SAME RATING ( BB OR B+ OR BB+)
IF YOU PROVIDE THE QUERY TO MEETS THE ABOVE REQUIRED OUTPUT IT WILL BE VERY HELPFUL TO ME.
THANKS
BHANU

Please don't type everything in capitals - it's equivalent to shouting.

Can you set up your new sample data set as inserts please? Same as your first post.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 6

Hi All,

I set up the new sample data with insert statement.

DECLARE @COMP_RATINGS TABLE

(

EID INT,

RATING VARCHAR(20),

AbsenceStartDate DATETIME,

AbsenceEndDate DATETIME

)

INSERT INTO @COMP_RATINGS VALUES

(769,'b+','2011-06-30','2011-09-30'),

(769,'bb','2011-06-30','2011-09-30'),

(769,'bb','2011-12-31 ','2012-03-31 '),

(769,'bb','2012-03-31 ','2012-06-30 '),

(769,'bb+','2009-12-31','2010-03-31'),

(769,'bb+','2010-03-31','2010-06-30 '),

(769,'bb+','2010-06-30','2010-09-30'),

(769,'bb+','2010-09-30','2010-12-31'),

(769,'bb+','2010-12-31','2011-03-31'),

(769,'bb+','2011-03-31','2011-06-30'),

(769,'bb+','2012-06-30','2012-12-31'),

(769,'bb+','2012-12-31','2013-03-31')

select * from @COMP_RATINGS

Output from the above table :

5364914 b+ 2011-09-30 00:00:00.0000000 2011-12-31 00:00:00.0000000

5364914 bb 2011-06-30 00:00:00.0000000 2011-09-30 00:00:00.0000000

5364914 bb 2011-12-31 00:00:00.0000000 2012-03-31 00:00:00.0000000

5364914 bb 2012-03-31 00:00:00.0000000 2012-06-30 00:00:00.0000000

5364914 bb+ 2009-12-31 00:00:00.0000000 2010-03-31 00:00:00.0000000

5364914 bb+ 2010-03-31 00:00:00.0000000 2010-06-30 00:00:00.0000000

5364914 bb+ 2010-06-30 00:00:00.0000000 2010-09-30 00:00:00.0000000

5364914 bb+ 2010-09-30 00:00:00.0000000 2010-12-31 00:00:00.0000000

5364914 bb+ 2010-12-31 00:00:00.0000000 2011-03-31 00:00:00.0000000

5364914 bb+ 2011-03-31 00:00:00.0000000 2011-06-30 00:00:00.0000000

5364914 bb+ 2012-06-30 00:00:00.0000000 2012-12-31 00:00:00.0000000

5364914 bb+ 2012-12-31 00:00:00.0000000 2013-03-31 00:00:00.0000000

The required output is :

5364914 b+ 2011-09-30 00:00:00.0000000 2011-12-31 00:00:00.0000000

5364914 bb 2011-06-30 00:00:00.0000000 2011-09-30 00:00:00.0000000

5364914 bb 2011-12-31 00:00:00.0000000 2012-06-30 00:00:00.0000000

5364914 bb+ 2009-12-31 00:00:00.0000000 2011-06-30 00:00:00.0000000

5364914 bb+ 2012-06-30 00:00:00.0000000 2013-03-31 00:00:00.0000000

Thanks

Bhanu

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Thanks.

Please explain the result for rating = 'b+'.

There's only one row with this value in the sample data, and it's completely different in the required output.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 8

hi,

in the original data

b+ has only one records it should display as it is.

bb has 3 records it should display as 2 records based on the date sequence.

bb+ has 8 records so it should display in the required output as 2 records.

now you are clear there we have 3 objects b+,bb,bb+.

thanks for you support.

let me know if you need more details.

thanks

bhanu

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Source data

(769,'b+','2011-06-30','2011-09-30'),

Required output

5364914 b+ 2011-09-30 00:00:00.0000000 2011-12-31 00:00:00.0000000

If the required output is incorrect then please correct it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 10

Hi All,

Sorry for the inconvenience in providing the inputs correctly.

DECLARE @COMP_RATINGS TABLE

(

EID INT,

RATING VARCHAR(20),

AbsenceStartDate DATETIME,

AbsenceEndDate DATETIME

)

INSERT INTO @COMP_RATINGS VALUES

(769,'bb','2011-06-30','2011-09-30'),

(769,'bb','2011-12-31 ','2012-03-31 '),

(769,'bb','2012-03-31 ','2012-06-30 '),

(769,'ccc','2009-12-31','2010-03-31'),

(769,'ccc','2010-03-31','2010-06-30 '),

(769,'ccc','2010-06-30','2010-09-30'),

(769,'ccc','2010-09-30','2010-12-31'),

(769,'ccc','2010-12-31','2011-03-31'),

(769,'ccc','2011-03-31','2011-06-30'),

(769,'ccc','2012-06-30','2012-12-31'),

(769,'ccc','2012-12-31','2013-03-31')

select * from @COMP_RATINGS

The original data from The above table:

769bb2011-06-30 2011-09-30

769bb2011-12-31 2012-03-31 -----Missing sequence in dates( 1 quarter)

769bb2012-03-31 2012-06-30

769ccc2009-12-31 2010-03-31

769ccc2010-03-31 2010-06-30

769ccc2010-06-30 2010-09-30

769ccc2010-09-30 2010-12-31

769ccc2010-12-31 2011-03-31

769ccc2011-03-31 2011-06-30

769ccc2012-06-30 2012-12-31 ----Missing sequence in dates

769ccc2012-12-31 2013-03-31

The required Output:

769bb2011-06-30 2011-09-30

769bb2011-12-31 2012-06-30

769ccc2009-12-31 2011-06-30

769ccc2012-06-30 2013-03-31

let me know for more details.

Thanks

bhanu

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Thanks.

-- try this

SELECT a.EID, a.RATING, a.AbsenceStartDate,

AbsenceEndDate = ISNULL(b.AbsenceEndDate, a.AbsenceEndDate)

FROM @COMP_RATINGS a

LEFT JOIN @COMP_RATINGS b

ON b.EID = a.EID

AND b.RATING = a.RATING

AND b.AbsenceStartDate = a.AbsenceEndDate

WHERE NOT EXISTS (SELECT 1 FROM @COMP_RATINGS c WHERE c.EID = a.EID AND c.RATING = a.RATING AND c.AbsenceEndDate = a.AbsenceStartDate)

-- and this

;WITH nd AS (

SELECT

a.EID, a.RATING, x.[Event], x.[Date],

n = COUNT(*) OVER (PARTITION BY a.EID, a.RATING, x.[Date])

FROM @COMP_RATINGS a

CROSS APPLY (VALUES ('Start', a.AbsenceStartDate), ('End', a.AbsenceEndDate) ) x ([Event], [Date])

)

SELECT

s.EID, s.RATING,

AbsenceStartDate = s.[Date],

AbsenceEndDate = x.[Date]

FROM nd s

CROSS APPLY (

SELECT TOP 1 *

FROM nd e

WHERE e.EID = s.EID

AND e.RATING = s.RATING

AND e.[Event] = 'End'

AND e.n = 1

AND e.[Date] >= s.[Date]

ORDER BY e.[Date]

) x

WHERE s.[Event] = 'Start'

AND s.n = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 12

Thank you very much boss 🙂

it is working fine 🙂

I am very very thankful to your support.

Thanks

Bhanu

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

You're welcome, thanks for the feedback 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86908 More actions · Answer 14

I would never attempt to produce a faster solution than ChrisM@Work because I know I can't, unless of course I get a little help from Itzik Ben-Gan.

-- Method by Itzik Ben-Gan

-- http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx

;WITH C1 AS (

SELECT EID, RATING, ts, Type

,e=CASE Type WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY EID, RATING, Type ORDER BY AbsenceEndDate) END

,s=CASE Type WHEN -1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY EID, RATING, Type ORDER BY AbsenceStartDate) END

FROM @COMP_RATINGS

CROSS APPLY (

VALUES (1, AbsenceStartDate), (-1, AbsenceEndDate)) a(Type, ts)

),

C2 AS (

SELECT C1.*

,se=ROW_NUMBER() OVER (PARTITION BY EID, RATING ORDER BY ts, Type DESC)

FROM C1),

C3 AS (

SELECT EID, RATING, ts

,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY EID, RATING ORDER BY ts)-1) / 2 + 1)

FROM C2

WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0)

SELECT EID, RATING, AbsenceStartDate=MIN(ts), AbsenceEndDate=MAX(ts)

FROM C3

GROUP BY EID, RATING, grpnm;

While it may look more complicated, I can assure you that it runs like the proverbial bat out of the underworld. 😛

The article linked into the comments explains in detail how it works.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St