Need help to calculate last week,last month etc data

Transact-SQL
, ,
1

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.

2

Something like:

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

with cte_period(period,dt_start,dt_end)
  as (          select 'Last Week'
                      ,dateadd(week,datediff(week,0,current_timestamp)-1,0)
                      ,dateadd(week,datediff(week,0,current_timestamp),0)
      union all select 'Last Two Weeks'
                      ,dateadd(week,datediff(week,0,current_timestamp)-2,0)
                      ,dateadd(week,datediff(week,0,current_timestamp),0)
      union all select 'Last Month'
                      ,dateadd(month,datediff(month,0,current_timestamp)-1,0)
                      ,dateadd(month,datediff(month,0,current_timestamp),0)
      union all select 'Last Quarter'
                      ,dateadd(quarter,datediff(quarter,0,current_timestamp)-1,0)
                      ,dateadd(quarter,datediff(quarter,0,current_timestamp),0)
      union all select 'Last Year'
                      ,dateadd(year,datediff(year,0,current_timestamp)-1,0)
                      ,dateadd(year,datediff(year,0,current_timestamp),0)
     )
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

1 Like
3

Thanks it worked..

4

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

5

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