SQL help - Passing a value into a sub-query

Transact-SQL
1

Hi there,
I'm am trying to solve an SQL problem and running into some difficulty. By no means an expert in SQL so hoping the wonderful brains here can help me solve this problem.

Suppose I had some data that looked liked this:

image

.. a basic table consisting of a customer's weekly payment due.

Now, I want to write a select query which will return all rows in this table as well as a new column called 'AmountPassedDue' which will summarize all AmountDue values for the previous weeks, i.e. where DueDate is less and PaymentDay is equal to the current row.

I have tried this using a sub-query, but I need to pass in DueDate and PaymentDay into the sub-query, and this doesn't seem possible.

Is it possible to achieve this with a nested sub-query or is there another way?

Any advice would be much appreciated!

Thanks.

2

Welcome to this forum!

To help people help you, it's considered best to provide usable sample data, like this:

CREATE TABLE #data ( Cust int NOT NULL, DueDate date NOT NULL, PaymentDay varchar(9) NULL, AmountDue decimal(9, 2) NULL )
INSERT INTO #data ( Cust, DueDate, AmountDue ) VALUES
    (1, '2021-07-26', 50),
    (2, '2021-07-27', 120),
    (3, '2021-07-28', 75),
    (4, '2021-07-29', 40),
    (5, '2021-07-30', 25),
    (6, '2021-07-31', 45),
    (7, '2021-08-01', 56),
    (1, '2021-08-02', 50),
    (2, '2021-08-03', 40),
    (3, '2021-08-04', 35),
    (4, '2021-08-05', 70),
    (5, '2021-08-06', 56)
UPDATE #data
SET PaymentDay = DATENAME(WEEKDAY, DueDate)

With that done, we can try to write a query. I'm not 100% sure what you're looking for, but something like this should help you along:

SELECT 
    d.Cust, d.DueDate, d.PaymentDay, d.AmountDue,
    (SELECT SUM(AmountDue) FROM #data d2 WHERE d2.Cust = d.Cust AND 
        d2.DueDate <= CAST(GETDATE() AS date)) AS AmountPastDue
FROM #data d
1 Like
3

Hi Scott!
Many thanks for the reply. And thanks, I'll be sure to provide sample data in my next post.

Made a small adjustment, but this is exactly what I was looking for!

SELECT 
d.Cust, d.DueDate, d.PaymentDay, d.AmountDue,
(SELECT SUM(AmountDue) FROM [common].TestData d2 WHERE d2.Cust = d.Cust AND 
    (d2.DueDate < d.DueDate AND d2.PaymentDay = d.PaymentDay)) AS AmountPastDue

FROM [common].TestData d

Thanks so much.

4

You're welcome.

Thanks very much for the feedback!

And glad I could help.