July 21, 2014 at 1:08 am
CREATE Table #tempID
(
ID int NOT NULL )
CREATE Table #tempreadings
(
ID int NOT NULL ,
Value float ,
Readingdate datetime
)
CREATE Table #tempdeliveries
(
ID int NOT NULL ,
Value float ,
deliverydate datetime
)
INSERT INTO #tempID
SELECT 1
INSERT INTO #tempID
SELECT 2
INSERT INTO #tempreadings
SELECT 1 , 23.09 , '2014-07-17 07:48:30.200'
INSERT INTO #tempreadings
SELECT 1 , 78.56 , '2014-07-16 05:12:30.200'
INSERT INTO #tempreadings
SELECT 1 , 32.56 , '2014-07-13 04:21:30.200'
INSERT INTO #tempreadings
SELECT 2 , 12.34 , '2014-07-16 02:03:20.200'
INSERT INTO #tempreadings
SELECT 2 , 56.43 , '2014-07-15 06:48:30.200'
INSERT INTO #tempreadings
SELECT 2 , 90.76 , '2014-07-14 11:23:30.200'
INSERT INTO #tempdeliveries
SELECT 1 , 9.34 , '2014-07-16 07:23:30.200'
INSERT INTO #tempdeliveries
SELECT 1 , 6.45 , '2014-07-17 06:48:30.200'
INSERT INTO #tempdeliveries
SELECT 1 , 22.56 , '2014-07-12 04:21:30.200'
INSERT INTO #tempdeliveries
SELECT 2 , 256.14 , '2014-07-16 01:03:20.200'
INSERT INTO #tempdeliveries
SELECT 2 , 26.3 , '2014-07-14 12:23:30.200'
INSERT INTO #tempdeliveries
SELECT 2 , 9.6 , '2014-07-01 11:23:30.200'
SELECT * FROM #tempreadings order by Readingdate desc
-- Result 1
-- Here ForEach ID i would like to get the max Readingdate SO my Output Would be
ID ReadingDate SeqID Valuie
1 2014-07-17 07:48:30.200 1 23.09
2 2014-07-16 02:03:20.200 1 12.34
-- Result 2
-- In The same Query I want the ReadingDate For Each ID And the ReadingDate IS 24 Hours Less Than the max Reading Date of Each ID . So i want the Output Should be
ID ReadingDate SeqID Value
1 2014-07-16 05:12:30.200 2 78.56
2 2014-07-14 11:23:30.200 2 90.76
-- Result 3
-- SO My OutPut Should be Both of Above resultsets
ID ReadingDate SeqID Value
1 2014-07-17 07:48:30.200 1 23.09
1 2014-07-16 05:12:30.200 2 78.56
2 2014-07-16 02:03:20.200 1 12.34
2 2014-07-14 11:23:30.200 2 90.76
--- We Need to join The above Results Set 3 With #tempdeliveries Based ON ID and We need to Find The Deliveries Which Falls Between
-- The two dates of Result Set 3
-- SeQ ID Should Be static -- 3
-- Example The Below Delivery Dates 2014-07-16 07:23:30.200 AND 2014-07-17 06:48:30.200 Falls Between
-- 2014-07-17 07:48:30.200 AND 2014-07-16 05:12:30.200 o fthe above Result Set for the ID 1 , so we have to pick those records.
-- Result 4
ID deliverydate SeqID Value
1 2014-07-16 07:23:30.200 3 9.34
1 2014-07-17 06:48:30.200 3 6.45
2 2014-07-16 01:03:20.200 3 256.14
2 2014-07-14 12:23:30.200 3 26.3
-- THe Final Output Should be Union all of both Result 3 & Result 4 THe Below is the Final Result Set
ID ReadingDate SeqID Value
1 2014-07-17 07:48:30.200 1 23.09
1 2014-07-16 05:12:30.200 2 78.56
1 2014-07-16 07:23:30.200 3 9.34
1 2014-07-17 06:48:30.200 3 6.45
2 2014-07-16 02:03:20.200 1 12.34
2 2014-07-14 11:23:30.200 2 90.76
2 2014-07-16 01:03:20.200 3 256.14
2 2014-07-14 12:23:30.200 3 26.3
July 21, 2014 at 1:53 am
See also this thread.
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
July 21, 2014 at 2:08 am
ramrajan (7/21/2014)
..
.
-- Result 2
-- In The same Query I want the ReadingDate For Each ID And the ReadingDate IS 24 Less Than the max Reading Date of Each ID . So i want the Output Should be
ID ReadingDate SeqID Value
1 2014-07-16 05:12:30.200 2 78.56
2 2014-07-14 11:23:30.200 2 90.76
.
.
.
You will need to elaborate on this definition "the ReadingDate IS 24 Less ".
For ID=1, the row chosen for Result 2 has a readingdate which is 1 day before the max readingdate.
For ID=2, the row chosen is two days before, even though there is a row in the table for the day before the max readingdate.
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
July 21, 2014 at 2:09 am
What ever thats posted in that thread is not correct, So please help me with this scenario.
July 21, 2014 at 2:10 am
Please read it as "ReadingDate IS 24 Hours Less Than the max Reading Date "
Appreciate your response
July 21, 2014 at 2:17 am
ramrajan (7/21/2014)
Please read it as "ReadingDate IS 24 Hours Less Than the max Reading Date "Appreciate your response
To help resolve this, I've rejigged the sample data to add a row number to #tempreadings and #tempdeliveries:
CREATE Table #tempID (ID int NOT NULL )
INSERT INTO #tempID VALUES (1), (2)
CREATE Table #tempreadings (ID int NOT NULL, RowID INT, Value float, Readingdate datetime)
INSERT INTO #tempreadings VALUES
(1, 1, 23.09, '2014-07-17 07:48:30.200'), -- max Readingdate
(1, 2, 78.56, '2014-07-16 05:12:30.200'), -- the day before max Readingdate, also > 24 hours
(1, 3, 32.56, '2014-07-13 04:21:30.200'),
(2, 4, 12.34, '2014-07-16 02:03:20.200'), -- max Readingdate
(2, 5, 56.43, '2014-07-15 06:48:30.200'), -- the day before max Readingdate, but < 24 hours
(2, 6, 90.76, '2014-07-14 11:23:30.200')
CREATE Table #tempdeliveries (ID int NOT NULL, RowID INT, Value float, deliverydate datetime)
INSERT INTO #tempdeliveries VALUES
(1, 1, 9.34, '2014-07-16 07:23:30.200'),
(1, 2, 6.45, '2014-07-17 06:48:30.200'),
(1, 3, 22.56, '2014-07-12 04:21:30.200'),
(2, 4, 256.14, '2014-07-16 01:03:20.200'),
(2, 5, 26.3, '2014-07-14 12:23:30.200'),
(2, 6, 9.6, '2014-07-01 11:23:30.200')
-- Result 1
-- Here ForEach ID i would like to get the max Readingdate SO my Output Would be
ID ReadingDate SeqID Valuie
1 2014-07-17 07:48:30.200 1 23.09 -- RowID = 1
2 2014-07-16 02:03:20.200 1 12.34 -- RowID = 4
-- Result 2
-- In The same Query I want the ReadingDate For Each ID And the ReadingDate IS 24 Less Than the max Reading Date of Each ID . So i want the Output Should be
ID ReadingDate SeqID Value
1 2014-07-16 05:12:30.200 2 78.56 -- RowID = 2
2 2014-07-14 11:23:30.200 2 90.76 -- RowID = 6
-- Result 3
-- SO My OutPut Should be Both of Above resultsets
ID ReadingDate SeqID Value
1 2014-07-17 07:48:30.200 1 23.09 -- RowID = 1
1 2014-07-16 05:12:30.200 2 78.56 -- RowID = 2
2 2014-07-16 02:03:20.200 1 12.34 -- RowID = 4
2 2014-07-14 11:23:30.200 2 90.76 -- RowID = 6
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
July 21, 2014 at 2:19 am
You will need to elaborate on this definition "the ReadingDate IS 24 Less ".
For ID=1, the row chosen for Result 2 has a readingdate which is 1 day before the max readingdate.
For ID=2, the row chosen is two days before, even though there is a row in the table for the day before the max readingdate.
ID = 2 Has Following Values
SELECT 2 , 12.34 , '2014-07-16 02:03:20.200'
SELECT 2 , 56.43 , '2014-07-15 06:48:30.200'
SELECT 2 , 90.76 , '2014-07-14 11:23:30.200'
Max Date FOr ID 2 IS 2014-07-16 02:03:20.200 Now i want to go back 24 Hours to this date and nned to find the Max Reading Date so i have mentioned as 2014-07-14 11:23:30.200 in my output.
July 21, 2014 at 2:30 am
"The most recent reading which is more than 24 hours before the max reading date"?
Like this:
;WITH OrderedData AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Readingdate DESC)
FROM #tempreadings t
)
SELECT o.ID, o.RowID, o.Value, o.Readingdate
FROM OrderedData o
WHERE o.rn = 1
UNION ALL
SELECT x.ID, x.RowID, x.Value, x.Readingdate
FROM OrderedData o
CROSS APPLY (SELECT TOP 1 * FROM #tempreadings t WHERE t.ID = o.ID AND t.Readingdate < DATEADD(DAY,-1,o.Readingdate) ) x
WHERE o.rn = 1
ORDER BY ID, Readingdate DESC
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
July 21, 2014 at 2:34 am
As i mentioned in my post we also need to join #temp
--- We Need to join The above Results Set 3 With #tempdeliveries Based ON ID and We need to Find The Deliveries Which Falls Between
-- The two dates of Result Set 3 for that ID
-- SeQ ID Should Be static -- 3
-- Example FOR ID =1 The Below Delivery Dates 2014-07-16 07:23:30.200 AND 2014-07-17 06:48:30.200 Falls Between
-- 2014-07-17 07:48:30.200 AND 2014-07-16 05:12:30.200 o fthe above Result Set for the ID 1 , so we have to pick those records.
-- Result 4
ID deliverydate SeqID Value
1 2014-07-16 07:23:30.200 3 9.34
1 2014-07-17 06:48:30.200 3 6.45
2 2014-07-16 01:03:20.200 3 256.14
2 2014-07-14 12:23:30.200 3 26.3
July 21, 2014 at 2:49 am
ramrajan (7/21/2014)
As i mentioned in my post we also need to join #temp--- We Need to join The above Results Set 3 With #tempdeliveries Based ON ID and We need to Find The Deliveries Which Falls Between
-- The two dates of Result Set 3 for that ID
-- SeQ ID Should Be static -- 3
-- Example FOR ID =1 The Below Delivery Dates 2014-07-16 07:23:30.200 AND 2014-07-17 06:48:30.200 Falls Between
-- 2014-07-17 07:48:30.200 AND 2014-07-16 05:12:30.200 o fthe above Result Set for the ID 1 , so we have to pick those records.
-- Result 4
ID deliverydate SeqID Value
1 2014-07-16 07:23:30.200 3 9.34
1 2014-07-17 06:48:30.200 3 6.45
2 2014-07-16 01:03:20.200 3 256.14
2 2014-07-14 12:23:30.200 3 26.3
Does the query I posted work, yes or no? I'm well aware that it's only one piece of the solution.
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
July 21, 2014 at 2:50 am
Yes that is Really Working.
But i need to do additional join with deliveries to get few more records as mentioned in my post.
July 21, 2014 at 3:02 am
ramrajan (7/21/2014)
Yes that is Really Working.But i need to do additional join with deliveries to get few more records as mentioned in my post.
Yes I know. One step at a time - that's how you and I both work when we are given a specification. You are beginning to sound like an end-user!
Try this:
-- This query returns result set 4
;WITH OrderedData AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Readingdate DESC)
FROM #tempreadings t
)
SELECT d.ID, d.deliverydate, SeqID = 3, d.Value
FROM OrderedData o
CROSS APPLY (SELECT TOP 1 Readingdate FROM #tempreadings t WHERE t.ID = o.ID AND t.Readingdate < DATEADD(DAY,-1,o.Readingdate) ) x
INNER JOIN #tempdeliveries d ON d.ID = o.ID AND d.deliverydate BETWEEN x.Readingdate AND o.Readingdate
WHERE o.rn = 1
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
July 21, 2014 at 4:52 am
Here's the final solution:
;WITH
SeqID1 AS (
SELECT x.ID, x.RowID, x.Value, MAXReadingdate = x.Readingdate
FROM (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Readingdate DESC)
FROM #tempreadings t
) x
WHERE rn = 1
),
SeqID2 AS (
SELECT [h-24].ID, [h-24].RowID, [h-24].Value, s1.MAXReadingdate,
[h-24Readingdate] = [h-24].Readingdate
FROM SeqID1 s1
CROSS APPLY (
SELECT TOP 1 *
FROM #tempreadings t
WHERE t.ID = s1.ID
AND t.Readingdate < DATEADD(DAY, -1, s1.MAXReadingdate)
) [h-24]
)
SELECT ID, Readingdate = MAXReadingdate, SeqID = 1, Value
FROM SeqID1
UNION ALL
SELECT ID, Readingdate = [h-24Readingdate], SeqID = 2, Value
FROM SeqID2
UNION ALL
SELECT d.ID, d.deliverydate, SeqID = 3, d.Value
FROM SeqID2 s2
INNER JOIN #tempdeliveries d
ON d.ID = s2.ID
AND d.deliverydate BETWEEN s2.[h-24Readingdate] AND s2.MAXReadingdate
ORDER BY ID, Readingdate DESC
You would probably get a performance lift from setting up SeqID1 as a #temp table.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply