日期条款的动态

Question

现在我的代码有效，但这不是动态的。下面显示了2022年1月每日表的每日值的总和，而不是2022年1月的月表。

我试图弄清楚如何编码此内容而且我不必在每次子句中更改日期。

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

结果：

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