Right now my code works, but it is not dynamic. Below shows the sum of daily values from a daily table in January 2022 compared to the monthly value from the monthly table for January 2022.
I am trying to figure out how to code this so the daily totals compared to the monthly can be pulled at anytime and I won't have to change the dates in the WHERE clauses everytime.
SELECT
b.LocationDescription AS 'Location'
,CONVERT(varchar, cm.RecordDate, 1) AS 'Month Date'
,DATENAME(month,cm.RecordDate) AS 'Month'
,SUM(cm.Production) AS 'Monthly Prod'
,d.total AS 'Daily Prod'
,SUM(cm.Production)-d.total AS 'Diff'
,(CASE WHEN d.total = 0 THEN 0 ELSE (SUM(cm.Production) - d.total)/d.total*100 END) AS 'Percent'
FROM CompletionMonthlyTb cm
INNER JOIN CompletionTb c ON cm.SystemID = c.SystemID
INNER JOIN LocationTb as b ON c.LocationID = b.LocationSystemID
JOIN (SELECT DISTINCT
b.LocationDescription AS 'Location'
,sum(cd.[Production]) AS 'Total'
FROM [xxxxxxx].[dbo].[LocationTb] b
JOIN xxxxxxxxx.dbo.CompletionTb c ON c.LocationID = b.LocationSystemID
JOIN xxxxxxx.dbo.CompletionDailyTb cd ON cd.SystemID = c.SystemID
WHERE cd.RecordDate BETWEEN '1-1-2022' AND '1-31-22'
GROUP BY b.LocationDescription) AS d ON d.Location = b.LocationDescription
WHERE cm.RecordDate = '2022-01-01'
GROUP BY b.LocationDescription, cm.RecordDate, d.total
ORDER BY cm.RecordDate, b.LocationDescription
RESULTS:
Location Month Date Month Monthly Prod Daily Prod Diff Percent
Alpha 01/01/22 January 27663 25584 2079 8
Beta 01/01/22 January 25503 26104 -600 -2
