CODEWITHSUNDEEP
sqlsql-servert-sqldatepart
SvenB
SvenB

Reputation: 139

TSQL - date range calculation with different records in column

I used to only have these kind of records in my "period"-column: CW 01, CW 02,...,CW 21

To retrieve only a certain range of calendar weeks I used this statement:

Select Period,....,
where    
(CONVERT(tinyint, SUBSTRING(Period, 4, 2)) >= DATEPART(week, GETDATE()) - 5), ...

Now I have to add records like MO 05, MO 06 for monthly values. Obviously I need the CW and MO values now.

So, instead of Datepart(week,...), I thought I could use "month" but I couldn't find a way to modify the substring stmt, in order to retrieve the last 6 weeks or 2 months from now.

Do you have an idea how to solve this? All I want is: "Show me the records for the last 2 months but also the CW entries within this 2 months from now range. I'm using MSSQL 2012.

UPDATE: fiddle

Thank you.

Upvotes: 1

Views: 236

Answers (1)

Ravi Singh
Ravi Singh

Reputation: 2080

Try this :

SQL Fiddle

Query:

with demo_cte as
( select DATEPART(wk,getdate()) CW,DATEPART(MM,getdate()) MO
   , GETDATE() cur_date, DATEADD(MM,-2,getdate()) end_date
    union all
  select DATEPART(wk,cur_date-7) CW,DATEPART(MM,cur_date-7) MO
   , cur_date -7 cur_date, end_date
from demo_cte
where cur_date>end_date
)   
select * from demo_cte

Results:

| CW | MO |                     CUR_DATE |                     END_DATE |
-------------------------------------------------------------------------
| 24 |  6 |  June, 13 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 23 |  6 |  June, 06 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 22 |  5 |   May, 30 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 21 |  5 |   May, 23 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 20 |  5 |   May, 16 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 19 |  5 |   May, 09 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 18 |  5 |   May, 02 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 17 |  4 | April, 25 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 16 |  4 | April, 18 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |
| 15 |  4 | April, 11 2013 12:18:43+0000 | April, 13 2013 12:18:43+0000 |

Update :

Try this :

Select * from test
where    period like 'CW%' and 
(CONVERT(tinyint, SUBSTRING(Period, 4, 2)) >= DATEPART(week, GETDATE()) - 5)
UNION 
Select * from test
where    period like 'MO%' and 
(CONVERT(tinyint, SUBSTRING(Period, 4, 2)) >= DATEPART(MM, GETDATE()) - 2);

OR this

Select * from test
where    (period like 'CW%' and 
(CONVERT(tinyint, SUBSTRING(Period, 4, 2)) >= DATEPART(week, GETDATE()) - 5))
or
(period like 'MO%' and 
(CONVERT(tinyint, SUBSTRING(Period, 4, 2)) >= DATEPART(MM, GETDATE()) - 2));

Upvotes: 2

Related Questions