Partition and order the records

Question

Post reply

Partition and order the records

ramrajan

SSCommitted

Points: 1737
More actions
July 18, 2014 at 6:56 am

#291172

CREATE Table #tempID
(
ID int NOT NULL )
CREATE Table #tempreadings
(
ID int NOT NULL ,
Readingdate datetime
)
INSERT INTO #tempID
SELECT 1
INSERT INTO #tempID
SELECT 2
INSERT INTO #tempreadings
SELECT 1 , '2014-07-17 07:48:30.200'
INSERT INTO #tempreadings
SELECT 1 , '2014-07-17 06:48:30.200'
INSERT INTO #tempreadings
SELECT 1 , '2014-07-17 05:48:30.200'
INSERT INTO #tempreadings
SELECT 2 , '2014-07-16 07:48:30.200'
INSERT INTO #tempreadings
SELECT 2 , '2014-07-16 06:48:30.200'
INSERT INTO #tempreadings
SELECT 2 , '2014-07-16 05:48:30.200'
For each ID Reading Date weneed to show the latest Reading date along with the ReadingDate Which is lessthan 1 day to the current reading
-- Here is the below output
ID ReadingDate SeqID
1 2014-07-18 07:48:30.200 1
1 2014-07-17 07:48:30.200 2
2 2014-07-17 07:48:30.200 1
2 2014-07-16 07:48:30.200 2

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

Latest reading date

Current reading

Are they the same thing?

^{“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

ramrajan SSCommitted Points: 1737 More actions · Answer 2

ramrajan

SSCommitted

Points: 1737

July 18, 2014 at 7:10 am

#1730588

Yes thats correct

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

Use ROW_NUMBER(). Partition by ID, order by date descending, pick the first two rows.

Then check the date difference between row 2 and row 1 of each partition, using a CTE will make this easy for you.

^{“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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

I'm having some trouble understanding what you're after in the output. You have 3 different times for temp id 1, but two identical times in the output. To get things going, here's how I would partition and order the rows, but the output is not exactly what you have listed as I don't completely see exactly what you need.

SELECT t.ID, tr.Readingdate, ROW_NUMBER() OVER(PARTITION BY t.ID ORDER BY tr.readingdate DESC) SeqID

FROM #tempID t

INNER JOIN #tempreadings tr ON tr.ID = t.ID

ORDER BY t.ID, SeqID;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ramrajan SSCommitted Points: 1737 More actions · Answer 5

It is giving all the records but i want the output as

ID ReadingDate SeqID

1 2014-07-18 07:48:30.200 1

1 2014-07-17 07:48:30.200 2

2 2014-07-17 07:48:30.200 1

2 2014-07-16 07:48:30.200 2

ramrajan SSCommitted Points: 1737 More actions · Answer 6

ramrajan

SSCommitted

Points: 1737

July 18, 2014 at 7:48 am

#1730612

Please someone help me

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

ramrajan (7/18/2014)
Please someone help me

Can you provide Ed with the information he needs to complete your query for you?

^{“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

ramrajan SSCommitted Points: 1737 More actions · Answer 8

I have already information i need for each ID we have to show the records based on the reading

First reading is always the current reading and the second is the 24 Hrs back reading of the current.

ramrajan SSCommitted Points: 1737 More actions · Answer 9

And her is how i want to get the output. Based on the sample data mentioned in my POst

ID ReadingDate SeqID

1 2014-07-18 07:48:30.200 1

1 2014-07-17 07:48:30.200 2

2 2014-07-17 07:48:30.200 1

2 2014-07-16 07:48:30.200 2

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

ramrajan (7/18/2014)
And her is how i want to get the output. Based on the sample data mentioned in my POst
ID ReadingDate SeqID
1 2014-07-18 07:48:30.200 1
1 2014-07-17 07:48:30.200 2
2 2014-07-17 07:48:30.200 1
2 2014-07-16 07:48:30.200 2

The output rows don't exist in the sample data.

^{“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

ramrajan SSCommitted Points: 1737 More actions · Answer 11

If we have multiple readings for a ID we are picking the latest record date as current reading and then we need to show the prevoius reading data which is the latest of Current Reading - 24 Hours.

That is what i have mentioned in output

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

ramrajan (7/18/2014)
If we have multiple readings for a ID we are picking the latest record date as current reading and then we need to show the prevoius reading data which is the latest of Current Reading - 24 Hours.
That is what i have mentioned in output

No, your desired output shows current reading + 24 hours:

SELECT *, SeqID = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Readingdate DESC)

FROM (

SELECT ID, Readingdate = MAX(Readingdate) FROM #tempreadings GROUP BY ID

UNION ALL

SELECT ID, DATEADD(DAY,1,MAX(Readingdate)) FROM #tempreadings GROUP BY ID

) d

ORDER BY ID, Readingdate DESC

^{“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