Would like to know why my query does not roll up and sum all "ID_Number", with individual "Product_ID", "Total_Receipts" Aging?
Created query with SQL Server Management Studio, my output to display correctly has parameters, and I have tried the following with my query.
Totals:
count(ar.Product_ID) AS 'Total_Receipts'
sum(ar.Receipt_Amount) AS 'Total_Amount',
sum(chg.Balance) AS 'Total Balance'
Currently my SQL SSMS query output displays the following:
ID_Number Production_ID Total_Receipts Aging Month Year Total_Amount Total Balance
0017 105206 143 90 DAYS PLUS April 2021 $357.00 $357.00
0017 105206 143 90 DAYS PLUS April 2021 $380.00 $380.00
0017 105343 132 90 DAYS PLUS April 2021 $330.00 $330.00
0017 105343 132 90 DAYS PLUS April 2021 $351.00 $351.00
What I really would like is to have a specific output that provides "Production_ID" and consolidate by each "ID_Number", then "Total sum with AGING" and SUM receipt charges "Total_Amount" like listed below:
ID_Number Production_ID Total_Receipts Aging Month Year Total_Amount Total Balance
0017 105206 143 90 DAYS PLUS April 2021 $737.00 $737.00
0017 105343 132 90 DAYS PLUS April 2021 $681.00 $681.00
[Query]
DECLARE @STARTDATE DATETIME2
DECLARE @ENDDATE DATETIME2
SET @STARTDATE = '2021-01-01 00:00:00'
SET @ENDDATE = '2022-02-10 00:00:00'
SELECT DISTINCT
te.ID_Number AS 'ID_Number',
chg.product_ID,
count(ar.Product_ID) AS 'Total_Receipts'
,CASE
WHEN DATEDIFF(day, chg.Created_Date, @EndDate) < 30 THEN 'CURRENT'
WHEN DATEDIFF(day, chg.Created_Date, @EndDate) BETWEEN 30 AND 59 THEN '30 DAYS'
WHEN DATEDIFF(day, chg.Created_Date, @EndDate) BETWEEN 60 AND 89 THEN '60 DAYS'
WHEN DATEDIFF(day, chg.Created_Date, @EndDate) >= 90 THEN '90 DAYS PLUS'
END AS 'Aging',
DATENAME(MONTH, chg.Created_Date) AS 'Month',
DATEPART(YEAR, chg.Created_Date) AS 'Year',
sum(ar.Receipt_Amount) AS 'Total_Amount',
sum(chg.Balance) AS 'Total Balance'
FROM dbo.charges AS chg
LEFT JOIN dbo.ar_receipts AS ar
ON arcpts.production_ID = chg.production_ID
INNER JOIN dbo.entities AS te
ON buld.Name = te.Name
INNER JOIN dbo.Employee AS emp
ON ar.Charge_ID = emp.Charge_ID
WHERE
chg.Balance > 0 AND
chg.Created_Date >= @STARTDATE AND
chg.Created_Date <= @ENDDATE
GROUP BY
te.ID_Number,
chg.Created_Date,
chg.product_ID
ORDER BY
te.ID_Number,
Datename(month,chg.Created_Date)
For example you can aggregate columns with max
SELECT
te.ID_Number AS ID_Number,
chg.product_ID,
count(ar.Product_ID) AS Total_Receipts
,max(CASE
WHEN DATEDIFF(day, chg.Created_Date, @EndDate) < 30 THEN 'CURRENT'
WHEN DATEDIFF(day, chg.Created_Date, @EndDate) BETWEEN 30 AND 59 THEN '30 DAYS'
WHEN DATEDIFF(day, chg.Created_Date, @EndDate) BETWEEN 60 AND 89 THEN '60 DAYS'
WHEN DATEDIFF(day, chg.Created_Date, @EndDate) >= 90 THEN '90 DAYS PLUS'
END) AS max_Aging,
max(DATENAME(MONTH, chg.Created_Date)) AS max_Month,
max(DATEPART(YEAR, chg.Created_Date)) AS max_Year,
sum(ar.Receipt_Amount) AS Total_Amount,
sum(chg.Balance) AS Total_Balance
FROM dbo.charges AS chg
LEFT JOIN dbo.ar_receipts AS ar
ON arcpts.production_ID = chg.production_ID
INNER JOIN dbo.entities AS te
ON buld.Name = te.Name
INNER JOIN dbo.Employee AS emp
ON ar.Charge_ID = emp.Charge_ID
WHERE
chg.Balance > 0 AND
chg.Created_Date >= @STARTDATE AND
chg.Created_Date <= @ENDDATE
GROUP BY
te.ID_Number, chg.product_ID
ORDER BY
te.ID_Number, chg.product_ID