I have a database where I have invoices like customer charges (Invoice_Number, Created_Date, Amount) and invoice accounts receivable charge amount (invoice_Number, Created_Date).
I want to get the sum of invoices total amount per customer with "Invoice_Number" going back 1 month back.
I have tried the following and what I get is the invoice number repeated with created date, and what I really need is the "total amount" per invoice number rolled up one month before today's date.
[My query]
SELECT DISTINCT,
inv.Invoice_Number,
chg.Created_Date,
SUM (DISTINCT chg.Amount) AS 'Amount'
FROM dbo.charges AS ‘chg’
INNER JOIN dbo.ar_receipts AS ‘inv’
ON chg.Invoice_ID = inv.Invoice_ID
WHERE chg.Created_Date >= DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) --One month going back.
AND inv.Invoice_number = '0411'
GROUP BY
inv.Invoice_Number,
chg.Created_Date,
chg.Amount
Order BY
inv.Invoice_Number
I want to get the sum of invoices total amount per customer with "Invoice_Number" going back 1 month back.
I have tried the following and what I get is the invoice number repeated with created date, and what I really need is the "total amount" per invoice number rolled up one month before today's date.
[My query]
SELECT DISTINCT,
inv.Invoice_Number,
chg.Created_Date,
SUM (DISTINCT chg.Amount) AS 'Amount'
FROM dbo.charges AS ‘chg’
INNER JOIN dbo.ar_receipts AS ‘inv’
ON chg.Invoice_ID = inv.Invoice_ID
WHERE chg.Created_Date >= DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) --One month going back.
AND inv.Invoice_number = '0411'
GROUP BY
inv.Invoice_Number,
chg.Created_Date,
chg.Amount
Order BY
inv.Invoice_Number
