Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question

1 Answer

up vote 1 down vote

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 |
share|improve this answer
not sure if this is what I need. See my added fiddle above for the structure. In that fiddle I want to filter out the "CW 08"-row. – SvenB 6 hours ago
@SvenB : Filtering out "CW 08" is the simple where clause. Can you add the sample input and output. I will be able to help. – Ravi Singh 6 hours ago
it's just an example. I know I can do it manually. For example, only get the last two months from now should work inside a stmt. – SvenB 5 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.