您可以在单个查询中执行此操作：

SELECT Promotion
     , Sum(CASE WHEN Started >= DATEADD(day, -1, GETDATE()) THEN Amount ELSE 0 END) AS Last_24
     , Sum(CASE WHEN Started >= DATEADD(day, -7, GETDATE()) THEN Amount ELSE 0 END) AS Last_Week
     , Sum(Amount) AS Last_Month
  FROM   dbo.CustomerPayment
 WHERE  Started >= DATEADD(day, - 31, GETDATE()) 
 GROUP 
    BY Promotion
 ORDER BY Last_Week DESC

请注意，此部分：

WHERE  Started >= DATEADD(day, - 31, GETDATE())

正如你对“上个月”概念的解释所阐明的那样。

使用条件聚合——也就是说，将条件移动到 select :

SELECT Promotion,
       SUM(case when Started >= DATEADD(day, - 1, GETDATE()) then Amount end) AS Last_1_day,
       SUM(case when Started >= DATEADD(day, - 7, GETDATE()) then Amount end) AS Last_7_day,
       . . . 
FROM  dbo.CustomerPayment
GROUP BY Promotion;

不过，有一个可能的问题。 GETDATE() --不管它的名字是什么--返回日期的时间组件。我怀疑你可能真的想把这当成一个约会，而不是一个约会时间：

SELECT Promotion,
       SUM(case when Started >= DATEADD(day, - 1, CONVERT(DATE, GETDATE())) then Amount end) AS Last_1_day,
       SUM(case when Started >= DATEADD(day, - 7, CONVERT(DATE, GETDATE())) then Amount end) AS Last_7_day,
       . . . 
FROM  dbo.CustomerPayment
GROUP BY Promotion;

我在找月/周。不是7/30天。
如果您希望这样，只需使用变量使查询可读。

declare @monthstart date,
        @weekstart date
;
select @monthstart=datefromparts(year(current_timestamp),month(current_timestamp),1)
select cast(DATEADD(d,1-DATEPART(WEEKDAY,current_timestamp),CURRENT_TIMESTAMP) as date) as Sunday,
    cast(DATEADD(d,2-case when DATEPART(WEEKDAY,current_timestamp)=1 then 8 else DATEPART(WEEKDAY,current_timestamp) end,CURRENT_TIMESTAMP) as date) as Monday
;