March 25, 2015 at 10:01 am
I'm looking for some assistance writing a query. Initially I was thinking this was a simple case of using group by and or partition. But I am not so sure anymore.
Here is what I am looking for:
CREATE TABLE BasePrice
(PriceEffectiveDate DATETIME, RCID INT, TID INT, PCD VARCHAR(10), Price NUMERIC(18,4))
INSERT INTO BasePrice
SELECT '3/2/2015 18:00', 5, 450, 'HE10', 1.735 UNION ALL
SELECT '3/3/2015 18:00', 5, 450, 'HE10', 1.731 UNION ALL
SELECT '3/4/2015 18:00', 5, 450, 'HE10', 1.704 UNION ALL
SELECT '3/5/2015 18:00', 5, 450, 'HE10', 1.624 UNION ALL
SELECT '3/6/2015 18:00', 5, 450, 'HE10', 1.6 UNION ALL
SELECT '3/9/2015 18:00', 5, 450, 'HE10', 1.71 UNION ALL
SELECT '3/10/2015 18:00', 5, 450, 'HE10', 1.74 UNION ALL
SELECT '3/11/2015 18:00', 5, 450, 'HE10', 1.755 UNION ALL
SELECT '3/12/2015 18:00', 5, 450, 'HE10', 1.736 UNION ALL
SELECT '3/13/2015 18:00', 5, 450, 'HE10', 1.679 UNION ALL
SELECT '3/16/2015 18:00', 5, 450, 'HE10', 1.789 UNION ALL
SELECT '3/17/2015 18:00', 5, 450, 'HE10', 1.902 UNION ALL
SELECT '3/18/2015 18:00', 5, 450, 'HE10', 1.962 UNION ALL
SELECT '3/19/2015 18:00', 5, 450, 'HE10', 1.917 UNION ALL
SELECT '3/20/2015 18:00', 5, 450, 'HE10', 1.915
CREATE TABLE Fees
(RCID INT, TID INT, PCD VARCHAR(10), FeeType VARCHAR(10), EffectiveDate DATETIME, Price NUMERIC(8,6))
INSERT INTO Fees
SELECT 5, 450, 'HE10', 'BFee', '3/3/2015 0:00', 0.010000 UNION ALL
SELECT 5, 450, 'HE10', 'Holding', '3/4/2015 0:00', 0.020000 UNION ALL
SELECT 5, 450, 'HE10', 'BFee', '3/15/2015 0:00', 0.030000 UNION ALL
SELECT 5, 450, 'HE10', 'holding', '3/16/2015 0:00', 0.040000
Desired Result:
╔═════════════════╦══════╦═════╦══════╦══════════╦══════════╦══════════╦═════════════╗
║ Price Date ║ RCID ║ TID ║ PCD ║ Price ║ BFee ║ Holding ║ Total Price ║
╠═════════════════╬══════╬═════╬══════╬══════════╬══════════╬══════════╬═════════════╣
║ 3/2/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.735000 ║ 0.000000 ║ 0.000000 ║ 1.735000 ║
║ 3/3/2015 0:00 ║ 5 ║ 450 ║ HE10 ║ 1.735000 ║ 0.010000 ║ 0.000000 ║ 1.745000 ║
║ 3/3/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.731000 ║ 0.010000 ║ 0.000000 ║ 1.741000 ║
║ 3/4/2015 0:00 ║ 5 ║ 450 ║ HE10 ║ 1.731000 ║ 0.010000 ║ 0.020000 ║ 1.761000 ║
║ 3/4/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.704000 ║ 0.010000 ║ 0.020000 ║ 1.734000 ║
║ 3/5/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.624000 ║ 0.010000 ║ 0.020000 ║ 1.654000 ║
║ 3/6/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.600000 ║ 0.010000 ║ 0.020000 ║ 1.630000 ║
║ 3/9/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.710000 ║ 0.010000 ║ 0.020000 ║ 1.740000 ║
║ 3/10/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.740000 ║ 0.010000 ║ 0.020000 ║ 1.770000 ║
║ 3/11/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.755000 ║ 0.010000 ║ 0.020000 ║ 1.785000 ║
║ 3/12/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.736000 ║ 0.010000 ║ 0.020000 ║ 1.766000 ║
║ 3/13/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.679000 ║ 0.010000 ║ 0.020000 ║ 1.709000 ║
║ 3/15/2015 0:00 ║ 5 ║ 450 ║ HE10 ║ 1.679000 ║ 0.030000 ║ 0.020000 ║ 1.729000 ║
║ 3/16/2015 0:00 ║ 5 ║ 450 ║ HE10 ║ 1.679000 ║ 0.030000 ║ 0.040000 ║ 1.749000 ║
║ 3/16/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.789000 ║ 0.030000 ║ 0.040000 ║ 1.859000 ║
║ 3/17/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.902000 ║ 0.030000 ║ 0.040000 ║ 1.972000 ║
║ 3/18/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.962000 ║ 0.030000 ║ 0.040000 ║ 2.032000 ║
║ 3/19/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.917000 ║ 0.030000 ║ 0.040000 ║ 1.987000 ║
║ 3/20/2015 18:00 ║ 5 ║ 450 ║ HE10 ║ 1.915000 ║ 0.030000 ║ 0.040000 ║ 1.985000 ║
╚═════════════════╩══════╩═════╩══════╩══════════╩══════════╩══════════╩═════════════╝
My current thought is putting all the distinct dates per RCID, TID, and PCD in to a temp table and then just selecting the max price and fee that was entered at or before the date.
I'm sure there is a more elegant way to get this result than staging the data in a temp table but I can't think of anything.
March 25, 2015 at 7:29 pm
I'd guess there's probably a better way to do this (certainly true in SQL 2012), but for now this is the best I can come up with.
SELECT EffectiveDate, RCID, TID, PCD
,Price = MAX(Price)
,BFee = ISNULL(MAX(BFee), 0)
,Holding = ISNULL(MAX(Holding), 0)
,TotalPrice = MAX(Price) + ISNULL(MAX(BFee), 0) + ISNULL(MAX(Holding), 0)
FROM
(
SELECT EffectiveDate, RCID, TID, PCD
,Price =
(
SELECT TOP 1 Price
FROM BasePrice b
WHERE a.RCID = b.RCID AND a.TID = b.TID AND a.PCD = b.PCD AND
b.PriceEffectiveDate <= a.EffectiveDate
ORDER BY b.PriceEffectiveDate DESC
)
,BFee = CASE FeeType
WHEN 'BFee' THEN Price
ELSE
(
SELECT TOP (1) c.Price
FROM Fees c
WHERE a.RCID = c.RCID AND a.TID = c.TID AND a.PCD = c.PCD AND
c.EffectiveDate <= a.EffectiveDate AND
c.FeeType = 'BFee'
ORDER BY c.EffectiveDate DESC
)
END
,Holding = CASE FeeType
WHEN 'Holding' THEN Price
ELSE
(
SELECT TOP (1) c.Price
FROM Fees c
WHERE a.RCID = c.RCID AND a.TID = c.TID AND a.PCD = c.PCD AND
c.EffectiveDate <= a.EffectiveDate AND
c.FeeType = 'Holding'
ORDER BY c.EffectiveDate DESC
)
END
FROM Fees a
UNION ALL
SELECT PriceEffectiveDate, RCID, TID, PCD, Price
,(
SELECT TOP (1) c.Price
FROM Fees c
WHERE a.RCID = c.RCID AND a.TID = c.TID AND a.PCD = c.PCD AND
c.EffectiveDate < a.PriceEffectiveDate AND
c.FeeType = 'BFee'
ORDER BY c.EffectiveDate DESC
)
,(
SELECT TOP (1) c.Price
FROM Fees c
WHERE a.RCID = c.RCID AND a.TID = c.TID AND a.PCD = c.PCD AND
c.EffectiveDate < a.PriceEffectiveDate AND
c.FeeType = 'Holding'
ORDER BY c.EffectiveDate DESC
)
FROM BasePrice ahttp://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
) a
GROUP BY EffectiveDate, RCID, TID, PCD
ORDER BY EffectiveDate;
I think it gets you what you're looking for.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 25, 2015 at 7:43 pm
This may be a little cleaner:
WITH EffectiveDates AS
(
SELECT PriceEffectiveDate, RCID, TID, PCD
FROM #BasePrice
UNION
SELECT EffectiveDate, RCID, TID, PCD
FROM #Fees
)
SELECT [Price Date]=a.PriceEffectiveDate, RCID, TID, PCD
,Price = ISNULL(Price, 0)
,BFee = ISNULL(BFee, 0)
,Holding = ISNULL(Holding, 0)
,TotalPrice = ISNULL(Price, 0) + ISNULL(BFee, 0) + ISNULL(Holding, 0)
FROM EffectiveDates a
OUTER APPLY
(
SELECT TOP (1) Price
FROM #BasePrice b
WHERE a.RCID = b.RCID AND a.TID = b.TID AND a.PCD = b.PCD AND
b.PriceEffectiveDate <= a.PriceEffectiveDate
ORDER BY b.PriceEffectiveDate DESC
) b
OUTER APPLY
(
SELECT TOP (1) BFee=Price
FROM #Fees c
WHERE a.RCID = c.RCID AND a.TID = c.TID AND a.PCD = c.PCD AND
c.EffectiveDate <= a.PriceEffectiveDate AND
c.FeeType = 'BFee'
ORDER BY c.EffectiveDate DESC
) c
OUTER APPLY
(
SELECT TOP (1) Holding=Price
FROM #Fees d
WHERE a.RCID = d.RCID AND a.TID = d.TID AND a.PCD = d.PCD AND
d.EffectiveDate <= a.PriceEffectiveDate AND
d.FeeType = 'Holding'
ORDER BY d.EffectiveDate DESC
) d
ORDER BY [Price Date];
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 26, 2015 at 9:07 am
This works :). One thing I forgot to mention however is the fees are dynamic and not just limited to BFee and Holding, is it possible to modify it to account for this?
Also, this database will be moving to SQL 2012 in a few months, you mentioned it was easier there.
March 26, 2015 at 6:03 pm
tfendt (3/26/2015)
This works :). One thing I forgot to mention however is the fees are dynamic and not just limited to BFee and Holding, is it possible to modify it to account for this?
If the FEES are dynamic, I presume you'd like to see a separate column in the results set for each?
You would then need to build the query up using Dynamic SQL, including as many:
- OUTER APPLYs as there are fee types
- Columns as needed to report each fee type
I know it sounds complicated but I suspect it really wouldn't be too bad.
tfendt (3/26/2015)
Also, this database will be moving to SQL 2012 in a few months, you mentioned it was easier there.
I may have been wrong about that. It was just an initial impression. After I wrote the code I rethought it and off the top of my head it might not be so helpful. LAG and LEAD initially came to mind but they may have to look back a variable number of rows to pick up the appropriate FEE. Maybe. I'd have to play around with it to be sure.
Also one thing I forgot to mention. The first OUTER APPLY (on BasePrice) could be a CROSS APPLY if you're sure that you're not reporting on Fees only.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 30, 2015 at 8:14 am
dwain.c (3/26/2015)
If the FEES are dynamic, I presume you'd like to see a separate column in the results set for each?
You would then need to build the query up using Dynamic SQL, including as many:
- OUTER APPLYs as there are fee types
- Columns as needed to report each fee type
I know it sounds complicated but I suspect it really wouldn't be too bad.
Yes, that is what I was thinking I would have to do. As you say shouldn't be too hard. I've never really used outer apply before so after you posted your solution I researched it and played around with it. I think I have a good grasp on it now :-).
dwain.c (3/26/2015)
Also one thing I forgot to mention. The first OUTER APPLY (on BasePrice) could be a CROSS APPLY if you're sure that you're not reporting on Fees only.
Yep, made the change shortly after I executed the query in my environment.
Thanks for your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply