提取年内至今的数据并与去年同期进行比较

Question

我试图提取交易的数据作为当前日期与去年同期比较。请参阅代码:

select m_number
, sum(amount)as Turnover
, cast(date_processed as date) as business_date
from x.table1
where cast(date_processed as date) between '2023-01-01' and cast(current_date as date) 
order by m_number, cast(date_processed

我希望提取2023-01-01到当前日期的数据，以及2022-01-01到类似日期的数据，即2023-01-01 -2023-01-05和2022-01-01 -2022-01-05，而不需要手动输入日期范围，而是自动刷新。

Answer 1

只是一个使用CTE来创建所需日期的选项。然后，对表进行JOIN就变成了一件小事。

with cte as (
Select CY1 = convert(date,format(convert(date,getdate()),'yyyy-01-01'))
,CY2 = convert(date,getdate())
,PY1 = dateadd(year,-1,convert(date,format(convert(date,getdate()),'yyyy-01-01')))
,PY2 = dateadd(year,-1,convert(date,getdate()))
)
Select *
From  [dbo].[YourTable]
Join  cte on [YourDate] between CY1 and CY2
or [YourDate] between PY1 and PY2

CTE生成

CY1         CY2         PY1         PY2
2023-01-01  2023-01-05  2022-01-01  2022-01-05