Cursor Or Not
-
Name Date Cost1 Cost2 Cost3 Cost4
AAA 2011-04-16 Null 5.00 5.00 12.00
AAA 2015-06-06 Null Null Null Null
AAA 2018-07-23 50.00 25.10 78.30 167.00
BBB 2010-01-01 Null Null Null Null
BBB 2018-07-23 25.00 18.00 5.00 120.00
CCC 2017-11-04 Null Null Null Null
CCC 2018-05-06 Null Null Null Null
CCC 2018-07-01 250.00 200.00 150.00 60000The information above is in a table, I would like to know without writing a cursor procedures what would be the best way to only return rows with the Name data that has the most recent date, so it can look like:
Name Cost
AAA 50.50
AAA 25.10
AAA 78.30
AAA 167.00
BBB 25.00
BBB 18.00
BBB 5.00
BBB 120.00
CCC 250.00
CCC 200.00
CCC 150.00
CCC 60000 -
No need for a cursor here, simply filter the set first and then unpivot it.
😎Here is an example
USE TEEST;
GO
SET NOCOUNT ON;;WITH SAMPLE_DATA ([Name],[Date],[Cost1],[Cost2],[Cost3],[Cost4])
AS (
SELECT 'AAA','2011-04-16',Null,5.00,5.00,12.00 UNION ALL
SELECT 'AAA','2015-06-06',Null,Null,Null,Null UNION ALL
SELECT 'AAA','2018-07-23',50.00,25.10,78.30,167.00 UNION ALL
SELECT 'BBB','2010-01-01',Null,Null,Null,Null UNION ALL
SELECT 'BBB','2018-07-23',25.00,18.00,5.00,120.00 UNION ALL
SELECT 'CCC','2017-11-04',Null,Null,Null,Null UNION ALL
SELECT 'CCC','2018-05-06',Null,Null,Null,Null UNION ALL
SELECT 'CCC','2018-07-01',250.00,200.00,150.00,60000
)
,BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.[Name]
ORDER BY SD.[Date] DESC
) AS ND_RID
,SD.[Name]
,SD.Cost1
,SD.Cost2
,SD.Cost3
,SD.Cost4
FROM SAMPLE_DATA SD
)
SELECT
UNP.[Name]
,UNP.[Cost]
FROM BASE_DATA BD
CROSS APPLY
(
SELECT BD.Name, BD.Cost1 UNION ALL
SELECT BD.Name, BD.Cost2 UNION ALL
SELECT BD.Name, BD.Cost3 UNION ALL
SELECT BD.Name, BD.Cost4
) UNP([Name],[Cost])
WHERE BD.ND_RID = 1;Output
Name Cost
---- --------
AAA 50.00
AAA 25.10
AAA 78.30
AAA 167.00
BBB 25.00
BBB 18.00
BBB 5.00
BBB 120.00
CCC 250.00
CCC 200.00
CCC 150.00
CCC 60000.00 -
That will work for a small sample test data but there are over three thousands rows, that will be a lot of "Select ......... Union all" statements.
-
kd11 - Thursday, July 26, 2018 6:45 AM
He's just setting up your test data to use for demonstration purposes. That's not a part of the solution to the problem. There are a lot of us that won't post a coded solution unless we've tested it.
Please see the article at the first link under "Helpful Links" in my signature line below for how to help us help you better and more quickly in the future.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
@kd11,
Cursor or not? I vote not. There aren't many problems that can't be solved without using a cursor. Here's a solution using UNPIVOT.
DECLARE @pvt table(sName char(3), sDate date, Cost1 smallmoney, Cost2 smallmoney, Cost3 smallmoney, Cost4 smallmoney);
INSERT @pvt (sName, sDate, Cost1, Cost2, Cost3, Cost4)
VALUES ('AAA', '2011-04-16', Null, 5.00, 5.00, 12.00)
, ('AAA', '2015-06-06', Null, Null, Null, NULL)
, ('AAA', '2018-07-23', 50.00, 25.10, 78.30, 167.00)
, ('BBB', '2010-01-01', Null, Null, Null, NULL)
, ('BBB', '2018-07-23', 25.00, 18.00, 5.00, 120.00)
, ('CCC', '2017-11-04', Null, Null, Null, NULL)
, ('CCC', '2018-05-06', Null, Null, Null, NULL)
, ('CCC', '2018-07-01', 250.0, 200.00, 150.00, 60000);
WITH MaxDateByName AS
(
SELECT sName
, sDate
, Cost1
, Cost2
, Cost3
, Cost4
, Row_Number() OVER (PARTITION BY sName ORDER BY sDate DESC) Rn
FROM @pvt
),
UnPvt AS
(
SELECT sName, sDate, Costs
FROM
(SELECT MaxDateByName.sName
, MaxDateByName.sDate
, MaxDateByName.Cost1
, MaxDateByName.Cost2
, MaxDateByName.Cost3
, MaxDateByName.Cost4
FROM MaxDateByName
WHERE MaxDateByName.Rn = 1) pvt
UNPIVOT (Costs FOR Cost IN (Cost1, Cost2, Cost3, Cost4)) upvt
)
SELECT UnPvt.sName
, UnPvt.sDate
, UnPvt.Costs
FROM UnPvt;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply