CODEWITHSUNDEEP
t-sql
AnxiousdeV
AnxiousdeV

Reputation: 373

TSQL, Get child records for ParentId where condition X

Goal is to retrieve a summary of past due amounts for a user. The group by clause appears to be the issue as it is required for the SUM function call. I know that this is possible via a sub query but I would like to avoid this for performance reasons.

SELECT      u.Name      AS 'UserName'
        SUM(i.Amount)   AS 'PastDue'
FROM        [User] u
LEFT JOIN   [Invoice] i
ON      i.UserId = u.Id
WHERE       i.DueDate > GETDATE()
AND     i.Paid = 0
GROUP BY ???

Table [User]

Id  Name
1   John
2   Mike
3   Brian
4   Todd
5   Asyan

Table [Invoice]

Id  UserId      Amount      DueDate     Paid
1   1       100     1/1/2019    0
2   1       100     1/2/2019    0
3   5       200     1/1/2019    1
4   5       200     1/2/2019    1
5   5       200     1/3/2019    0

Expected Result

UserName    PastDue
John        200
Asyan       200

Upvotes: 0

Views: 25

Answers (1)

Brinda Jana
Brinda Jana

Reputation: 26

This query checks for "greater than GETDATE()". What you propably meant was duedate less than getdate().

The following query should give you the expected result:

SELECT u.Name AS 'UserName', 
SUM(i.Amount) AS 'PastDue' FROM [User] u
LEFT JOIN [Invoice] i ON i.UserId = u.Id
WHERE i.DueDate < GETDATE() AND i.Paid = 0
GROUP BY u.Name

Upvotes: 1

Related Questions