CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Kyle
Kyle

Reputation: 5557

Month grouping of invoices, based on conditions in SQL

I have a table of invoices that looks like this:

InvoiceDate InvoiceNumber   PaidDate    PayStatus   Amount
-----------------------------------------------------------
2012-1-23   1234            2012-02-28  Unpaid      1234
2012-2-01   2345            2012-03-12  Paid        23456

I need to GROUP these by (and take their monthly sums) certainly conditions.

I came up with a WHERE CLAUSE for only the current month. The logic is like this.

This is only for the current month (which reports on the last day of the prev month). I'm at a loss at how to do it for ALL MONTHS in the invoice table.

    -- only extract invoices with invoice dates less than or equal to the last day of the previous month
AND b.InvoiceDt <= DATEADD(dd, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

    -- the 'lateness' should not exceed 90 days (lateness = diff(Period - (InvoiceDt + Terms)))
AND DATEDIFF(day, DATEADD(day, ISNULL(b.Term, 0), b.InvoiceDt), DATEADD(dd, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) <= 90

    -- take either unpaid PayStatus OR if it's marked as paid, ActualPaymentdt should be greater than or equal to the last day of the previous month
AND (b.PayStatus = 'Unpaid' OR b.ActualPaymentDt >= DATEADD(dd, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)))

    -- if the day component of invoice date equals 1 AND it belongs to acct 4300, exclude it
AND NOT (b.AccountNumber = 4300 AND DAY(b.InvoiceDt) = 1)

Upvotes: 0

Views: 514

Answers (1)

PinnyM
PinnyM

Reputation: 35531

Join on another derived table that contains all months in the invoices table:

CROSS JOIN (SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0, InvoiceDt), 0) as InvoiceMonth 
            FROM invoices) m

Then substitute GETDATE() with m.InvoiceMonth.

And don't forget to GROUP BY m.InvoiceMonth as well.

Upvotes: 1

Related Questions