SQLTeam.com | Weblogs | Forums

Need help to calculate last week,last month etc data



I am looking for if else or case statement to select last week,last to last week,last month,last quarter and last year data.
And i need that in a variable. and then i will compare that variable to the column in my table.
tried below code.. but how will i get last- month,quarter and year data?

declare @period varchar(100)
IF(@period = 'Last Week')
SET @period = DATEADD(dd,-7,CONVERT(datetime,CONVERT(nvarchar(11),GETDATE())))
ELSE IF (@period='Last Two Weeks')
SET @period=DATEADD(dd,-14,CONVERT(datetime,CONVERT(nvarchar(11),GETDATE())))

select * from department where joindate>@period
please help.


Something like:

declare @period varchar(100)='Last Week';

with cte_period(period,dt_start,dt_end)
  as (          select 'Last Week'
      union all select 'Last Two Weeks'
      union all select 'Last Month'
      union all select 'Last Quarter'
      union all select 'Last Year'
select d.*
  from cte_period as p
       inner join department as d
               on d.joindate>=p.dt_start
              and d.joindate<p.dt_end
 where p.period=@period

Edit: wrongfully posted my test Query with hardcoded date - sorry


Thanks it worked..


how to get only the year and month in above query separated by hyphen?
like 2017-01


Select convert(char(7), getdate(), 121)