Extracting year to date data and comparing with same time last year

Question

I am trying to extract data for transactions as of current date comparing with same time last year. Please see the code:

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

I expect to extract for instance data for 2023-01-01 to the current date and data for 2022-01-01 to similar date, i.e. 2023-01-01 -2023-01-05 and 2022-01-01 -2022-01-05 without manually inputting the date ranges but rather refreshing automatically.

Answer 1

Just an option which uses a CTE to create the desired dates. Then it becomes a small matter for a JOIN to your table.

Example

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

The CTE generates

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