SQL Query Best Approach Recommendations

Question

SQL Query Best Approach Recommendations

RenzoSQL

Ten Centuries

Points: 1252
More actions
July 29, 2022 at 8:47 pm

#4070140
*/
- This topic was modified 2 years, 3 months ago by RenzoSQL.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3666 More actions · Answer 1

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;

RenzoSQL Ten Centuries Points: 1252 More actions · Answer 2

Thank you so much for the quick response! Will look into this further. Much appreciated

RenzoSQL Ten Centuries Points: 1252 More actions · Answer 3

RenzoSQL

Ten Centuries

Points: 1252

July 29, 2022 at 11:01 pm

#4070173

Thank you very much! <3

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

WHY did you delete your original post? That's not very friendly.

--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)

ScottPletcher SSC Guru Points: 101095 More actions · Answer 5

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".

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

Thanks, Scott. I appreciate it.

--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)

RenzoSQL Ten Centuries Points: 1252 More actions · Answer 7

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.