Dates

• Louise-213220

  SSC-Addicted

  Points: 484

  More actions

  March 2, 2005 at 2:06 pm

  #362094

  I have been asked to write some SQL (using Reporting Services) for the value of sales between a date range for the current year and also for the same period the previous year. How do I best achieve this

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  March 2, 2005 at 2:16 pm

  #545031

  Something like this?

  Select 'CURRENT' as "Year", sum(orders) from dbo.Orders where OrderDate between @DateStart and @DateEnd

  UNION ALL

  Select 'LAST' as "Year", sum(orders) from dbo.Orders where OrderDate between

  DATEADD(yyyy, -1, @DateStart) and

  DATEADD(yyyy, -1, @DateEnd)

• kenno_rules

  SSC-Addicted

  Points: 473

  More actions

  March 3, 2005 at 3:38 pm

  #545349

  Or, if you need your results on the same row, then...

  select sum(case when OrderDate between @DateStart and @DateEnd then Orders else null end) as ThisYearOrders,

  sum(case when OrderDate between DATEADD(yyyy, -1, @DateStart) and DATEADD(yyyy, -1, @DateEnd) then Orders else null end) as LastYearOrders

  from dbo.Orders

  where (OrderDate between @DateStart and @DateEnd)

  or (OrderDate between DATEADD(yyyy, -1, @DateStart) and DATEADD(yyyy, -1, @DateEnd))