SQL Query Best Approach Recommendations

  •  

    */

    • This topic was modified 2 years, 3 months ago by  RenzoSQL.
  • 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;

     

     

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

  • Thank you very much! <3

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

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

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

  • 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