July 29, 2022 at 10:20 pm
The easiest approach is a recursive cte. I tried to come up with a non recursive solution. I found one that works with this data, but it makes assumptions about the data that are probably not valid.
-- non cte
SELECT a.ParentTransactionID, b.AggregateBillCount
FROM (
SELECT TransactionID AS ParentTransactionID, LAG(TransactionID,1) OVER (ORDER BY TransactionID) AS prevParent
FROM @trans AS a
WHERE a.ParentTransaction IS NULL
) AS a
CROSS APPLY (SELECT COUNT(*) AS AggregateBillCount
FROM @trans
WHERE BillDate IS NOT NULL
AND TransactionID > ISNULL(a.prevParent,0)
AND TransactionID <= a.ParentTransactionID) AS b
ORDER BY b.AggregateBillCount DESC;
--cte
WITH bills AS
( SELECT TransactionID AS CurrentTransactionID, TransactionID, IIF(BillDate IS NULL,0,1) AS BillCount
FROM @Trans
WHERE Parenttransaction IS NULL
UNION ALL
SELECT a.CurrentTransactionID, b.TransactionID, 1
FROM bills AS a
JOIN @Trans AS b ON a.TransactionID = b.ParentTransaction AND b.BillDate IS NOT NULL
)
SELECT CurrentTransactionID, SUM(BillCount) as AggregateBillCount
FROM bills
GROUP BY CurrentTransactionID
ORDER BY SUM(BillCount) DESC;
July 29, 2022 at 11:01 pm
Thank you so much for the quick response! Will look into this further. Much appreciated
July 29, 2022 at 11:01 pm
Thank you very much! <3
July 30, 2022 at 1:58 am
WHY did you delete your original post? That's not very friendly.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2022 at 1:02 am
Here's the original post:
"I have a little exercise and would like to know your opinion on the most effective/best way to approach. Please see the result I am looking for at the bottom. Thank you in Advance.
DATA:
DECLARE @Trans TABLE (TransactionID INT, BillDate DATE, ParentTransaction INT)
INSERT INTO @Trans(TransactionID, BillDate, ParentTransaction)
VALUES (50 , NULL , NULL), (45 , '2011-10-01', 50), (40 , '2011-09-05', 45)
, (35 , '2011-01-07', 40), (100, '2011-01-24', NULL), (95 , NULL , 100)
, (90 , NULL , 95), (70 , NULL , NULL);
SELECT TransactionID, BillDate, ParentTransaction
FROM @Trans
OUTCOME SOLUTION RESULT:
The most current transaction in the assessment dataset is defined by a null value in the parent transaction column
A bill is defined by the presence of a date in the billdate column
Question: How many bills have been sent for each current transaction?
Exercise: Please create a SQL query that will generate the expected results
Expected Results:
--CurrentTransactionId, AggregateBillCount
--50, 3
--100, 1
--70, 0
*/
"
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 31, 2022 at 4:37 am
Thanks, Scott. I appreciate it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2022 at 2:23 pm
My Apologies, didnt mean to delete it, and I am not sure how it happened. Thank you Scott for reposting it. Sorry about that Jeff. Thanks again Ed B as this confirmed the original approach I took with it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply