returns 1 row from date range rows
-
Dear All,
Please help me write a T_SQL (SQL 2008). For all rows in the sample data below where the BAL_AMT values are the same, I need to return a single row with AGRMNT, PARTY_ID, MIN(START_DT), MX(END_DT), BAL_AMT and the rest of the rows in the table. The BAL_AMT value are considered the same if the rows are in a range defined by START_DT, END_DT and the BAL_AMT hasn't changed. Sample records in the table below
IDAGRMNTPARTY_IDSTART_DT END_DATE BAL_AMT
1LTB 34 1997-04-17 2008-05-140
2LTB 34 2008-05-15 2008-09-290
3LTB 34 2008-09-30 2008-10-02196
4LTB 34 2008-10-03 2008-10-066
5LTB 34 2008-10-07 2008-10-1341
34LTB 34 2009-06-12 2009-02-120
From the above dataset, I want to return
IDAGRMNTPARTY_IDSTART_DT END_DATE BAL_AMT
1LTB 34 1997-04-17 2008-09-290
3LTB 34 2008-09-30 2008-10-02196
4LTB 34 2008-10-03 2008-10-066
5LTB 34 2008-10-07 2008-10-1341
34LTB 34 2009-06-12 2009-02-120
Please NOTE, I haven't got the last record (ID = 34) in the range because the BAL_AMT would have changed to 196 after recors with ID 2.
Thanks,
Jaysen
-
Thank you for your reply and apologies, I should have read the ethitec before posting! I've basically created a temp table and loaded some data in it to play around and get the SQL working;
/* Create a temp table */
CREATE TABLE [dbo].[delete_temp](
[ID] [int] NOT NULL,
[AGRMNT] [nvarchar](3) NOT NULL,
[PARTY_ID] [int] NOT NULL,
[START_DT] [date] NOT NULL,
[END_DATE] [date] NOT NULL,
[BAL_AM] [int] NOT NULL
) ON [PRIMARY]
GO
/*Insert sample data*/
INSERT INTO delete_temp
VALUES (1,'LTB',34,'1997-04-17','2008-05-14',0),
(2,'LTB',34,'2008-05-15','2008-09-29',0),
(3,'LTB',34,'2008-09-30','2008-10-02',196),
(4,'LTB',34,'2008-10-03','2008-10-06',6),
(5,'LTB',34,'2008-10-07','2008-10-13',41),
(34,'LTB',34,'2009-06-12','2009-02-12',0)
/* Below is the query I ran but it's grouping records 1, 2 and 34 as one group but what I need as a group is records 1 and 2 (because they have the same BAL_AM and this changes for record 3) and all the other records in their own group because their BAL_AM are different. All I'm trying to do is eliminate the duplicates BAL_AM by keeping the MIN start_Date and MAX end_date for the group. A group is defined when the BAL_AM is the same and the group ends when the BAL_AM changes. Hope this is clear. */
SELECT MAX(ID) AS ID, MIN(START_DT) AS START_DT, MAX(END_DATE) AS END_DATE, MAX(BAL_AM) AS BAL_AM
FROM delete_temp
GROUP BY AGRMNT, PARTY_ID, BAL_AM
-
vee_jess (6/28/2011)
Thank you for your reply and apologies, I should have read the ethitec before posting! I've basically created a temp table and loaded some data in it to play around and get the SQL working;/* Create a temp table */
CREATE TABLE [dbo].[delete_temp](
[ID] [int] NOT NULL,
[AGRMNT] [nvarchar](3) NOT NULL,
[PARTY_ID] [int] NOT NULL,
[START_DT] [date] NOT NULL,
[END_DATE] [date] NOT NULL,
[BAL_AM] [int] NOT NULL
) ON [PRIMARY]
GO
/*Insert sample data*/
INSERT INTO delete_temp
VALUES (1,'LTB',34,'1997-04-17','2008-05-14',0),
(2,'LTB',34,'2008-05-15','2008-09-29',0),
(3,'LTB',34,'2008-09-30','2008-10-02',196),
(4,'LTB',34,'2008-10-03','2008-10-06',6),
(5,'LTB',34,'2008-10-07','2008-10-13',41),
(34,'LTB',34,'2009-06-12','2009-02-12',0)
/* Below is the query I ran but it's grouping records 1, 2 and 34 as one group but what I need as a group is records 1 and 2 (because they have the same BAL_AM and this changes for record 3) and all the other records in their own group because their BAL_AM are different. All I'm trying to do is eliminate the duplicates BAL_AM by keeping the MIN start_Date and MAX end_date for the group. A group is defined when the BAL_AM is the same and the group ends when the BAL_AM changes. Hope this is clear. */
SELECT MAX(ID) AS ID, MIN(START_DT) AS START_DT, MAX(END_DATE) AS END_DATE, MAX(BAL_AM) AS BAL_AM
FROM delete_temp
GROUP BY AGRMNT, PARTY_ID, BAL_AM
Can you explain how to handle the final row, the end date appears to be before the start date.
____________________________________________________
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
-
Well spotted, mistake from my part whilst entering sample data in the table, the sample data is as below
/*Insert sample data*/
INSERT INTO delete_temp
VALUES (1,'LTB',34,'1997-04-17','2008-05-14',0),
(2,'LTB',34,'2008-05-15','2008-09-29',0),
(3,'LTB',34,'2008-09-30','2008-10-02',196),
(4,'LTB',34,'2008-10-03','2008-10-06',6),
(5,'LTB',34,'2008-10-07','2008-10-13',41),
(34,'LTB',34,'2009-06-12','2009-12-12',0)
-
Similar to this
http://www.sqlservercentral.com/Forums/Topic1125847-392-1.aspx
Here's my solution, looks a bit complex but runs quickly
WITH StartsAndEnds(StartEnd,ID,START_DT,END_DATE,AGRMNT,PARTY_ID,BAL_AM) AS (SELECT 'S' AS StartEnd,
ID,
START_DT,
DATEADD(day,-1,START_DT),
AGRMNT,
PARTY_ID,
BAL_AM
FROM delete_temp
UNION ALL
SELECT 'E' AS StartEnd,
ID,
DATEADD(day,1,END_DATE),
END_DATE,
AGRMNT,
PARTY_ID,
BAL_AM
FROM delete_temp),
OrderedStarts AS (
SELECT ID,
START_DT,
AGRMNT,
PARTY_ID,
BAL_AM,
ROW_NUMBER() OVER(PARTITION BY AGRMNT,PARTY_ID,BAL_AM ORDER BY START_DT,StartEnd DESC) AS rnBoth,
2*(ROW_NUMBER() OVER(PARTITION BY AGRMNT,PARTY_ID,BAL_AM,StartEnd ORDER BY START_DT))-1 AS rnStartEnd
FROM StartsAndEnds),
OrderedEnds AS (
SELECT END_DATE,
AGRMNT,
PARTY_ID,
BAL_AM,
ROW_NUMBER() OVER(PARTITION BY AGRMNT,PARTY_ID,BAL_AM ORDER BY END_DATE DESC,StartEnd) AS rnBothRev,
2*(ROW_NUMBER() OVER(PARTITION BY AGRMNT,PARTY_ID,BAL_AM,StartEnd ORDER BY END_DATE DESC))-1 AS rnStartEndRev
FROM StartsAndEnds),
Starts AS (
SELECT ID,
START_DT,
AGRMNT,
PARTY_ID,
BAL_AM,
ROW_NUMBER() OVER(PARTITION BY AGRMNT,PARTY_ID,BAL_AM ORDER BY START_DT) AS rn
FROM OrderedStarts
WHERE rnBoth=rnStartEnd),
Ends AS (
SELECT END_DATE,
AGRMNT,
PARTY_ID,
BAL_AM,
ROW_NUMBER() OVER(PARTITION BY AGRMNT,PARTY_ID,BAL_AM ORDER BY END_DATE) AS rn
FROM OrderedEnds
WHERE rnBothRev=rnStartEndRev)
SELECT s.ID,s.AGRMNT,s.PARTY_ID,s.START_DT,e.END_DATE,s.BAL_AM
FROM Starts s
INNER JOIN Ends e ON e.AGRMNT=s.AGRMNT
AND e.PARTY_ID=s.PARTY_ID
AND e.BAL_AM=s.BAL_AM
AND e.rn=s.rn
AND s.START_DT<=e.END_DATE
ORDER BY s.AGRMNT,s.PARTY_ID,s.START_DT,e.END_DATE,s.BAL_AM;
____________________________________________________
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 -
SSC Eights,
Thank you very much for your help, much appreciated. Your solution works even for overlapping dates, great!
Jaysen
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply