How can we Group at particular set of records which come in a Sequence , If the sequence changes it would add the records to a new group.
-
Lets say ,There are 4 columns with Primary Key Constraints
L1 L2 L3 Date Forecast
1 a x 01-jan 1.2
1 a x 02-jan 2.0
1 a x 03-jan 3.0
1 a x 05-Jan 5.2
I want to group them, such that in the first group since it would retrieve
1 a x 03-jan 3.2 -- in the first group , which basically has the max(forecast) as the Date field is in a daily sequence.
In the Second group it would give 1 a x 05-Jan 5.2 , basically there is a break in the sequence of the date so its added in the second group.
Are there any suggestions for my problem.
-
First of all I can suggest you to read article from the link found in the bottom of my signature. It will help your question to be answered by other forum members here;-)
From what I can see, one of the way you can go is to use "quirky" update method in order to calculate your groups: http://www.sqlservercentral.com/articles/T-SQL/68467/
-
Have a look at the 'Group Islands of Contiguous Dates' article here[/url]
DECLARE @t TABLE(L1 INT,L2 CHAR(1), L3 CHAR(1), dt Date, Forecast NUMERIC(10,5))
INSERT INTO @t(L1,L2,L3,dt,Forecast)
VALUES
(1, 'a', 'x', '01-jan-2013', 1.2),
(1, 'a', 'x', '02-jan-2013', 2.0),
(1, 'a', 'x', '03-jan-2013', 3.0),
(1, 'a', 'x', '05-Jan-2013', 5.2);
WITH CTE1 AS (
SELECT L1,L2,L3,dt,Forecast,
ROW_NUMBER() OVER(ORDER BY dt DESC) AS rn1
FROM @t),
CTE2 AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY DATEADD(Day,rn1,dt) ORDER BY rn1) AS rn2
FROM CTE1)
SELECT L1,L2,L3,dt,Forecast
FROM CTE2
WHERE rn2 = 1
ORDER BY dt;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
just small change to get record with MAX(Forecast), more sample data added:
DECLARE @t TABLE(L1 INT,L2 CHAR(1), L3 CHAR(1), dt Date, Forecast NUMERIC(10,5))
INSERT INTO @t(L1,L2,L3,dt,Forecast)
VALUES
(1, 'a', 'x', '01-dec-2012', 7.2),
(1, 'a', 'x', '01-jan-2013', 1.2),
(1, 'a', 'x', '02-jan-2013', 2.0),
(1, 'a', 'x', '03-jan-2013', 3.0),
(1, 'a', 'x', '05-Jan-2013', 5.2),
(1, 'a', 'x', '06-Jan-2013', 5.2),
(1, 'a', 'x', '06-Jan-2013', 4.2);
WITH CTE1 AS (
SELECT L1,L2,L3,dt,Forecast,
ROW_NUMBER() OVER(ORDER BY dt DESC) AS rn1
FROM @t)
,CTE2 AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY DATEADD(Day,rn1,dt) ORDER BY dt, Forecast DESC) AS rn2
FROM CTE1)
select * from CTE2 where rn2 = 1
-
I have a lot of data in my Table , Dont you think it would look clumsy if we insert all the data to @T
as done in
DECLARE @t TABLE(L1 INT,L2 CHAR(1), L3 CHAR(1), dt Date, Forecast NUMERIC(10,5))
INSERT INTO @t(L1,L2,L3,dt,Forecast)
VALUES
(1, 'a', 'x', '01-dec-2012', 7.2),
(1, 'a', 'x', '01-jan-2013', 1.2),
(1, 'a', 'x', '02-jan-2013', 2.0),
(1, 'a', 'x', '03-jan-2013', 3.0),
(1, 'a', 'x', '05-Jan-2013', 5.2),
(1, 'a', 'x', '06-Jan-2013', 5.2),
(1, 'a', 'x', '06-Jan-2013', 4.2);
-
Thanks for the reply Mark/Eugene , this works fine for this combination of Primary key contraints i.e
1 a x 01-jan
1 a x 02-Jan
but it fails when i introduce a new set of combination i.e.
(1, 'a', 'x', '01-jan-2013', 1.2),
(1, 'a', 'x', '02-jan-2013', 2.0),
(1, 'a', 'x', '03-jan-2013', 3.0),
(1, 'a', 'x', '05-Jan-2013', 5.2);
(2, 'b', 'y', '01-Feb-2013', 4.0);
(2, 'b', 'y', '02-Feb-2013', 4.0);
How can i get appropriate values in this case
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply