CODEWITHSUNDEEP
sqlsql-servert-sql
Jan Janoušek
Jan Janoušek

Reputation: 37

Data per weeks previous month

I want to sort data by weeks for previous month and I look for code which is usable automatic without manually writing the dates.

Instead of this:

SELECT
wadat_ist AS 'frame'
,CASE
WHEN wadat_ist BETWEEN '2019-09-01' AND '2019-09-01' THEN 'MA1'
WHEN wadat_ist BETWEEN '2019-09-02' AND '2019-09-08' THEN 'MA2'
WHEN wadat_ist BETWEEN '2019-09-09' AND '2019-09-15' THEN 'MA3'
WHEN wadat_ist BETWEEN '2019-09-16' AND '2019-09-22' THEN 'MA4'
WHEN wadat_ist BETWEEN '2019-09-23' AND '2019-09-30' THEN 'MA5'
END AS 'Activity'

I tried this:

SELECT
wadat_ist AS 'frame'
,CASE
WHEN wadat_ist BETWEEN (SELECT (Convert (DATETIME, (SELECT DATEADD(DAY,1,EOMONTH(GETDATE(),-2)))))) AND  (SELECT DateAdd (wk, 0, (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, +0.99999)))) THEN 'MA1'
WHEN wadat_ist BETWEEN (SELECT DateAdd (wk, -4, (SELECT DATEADD(day,-1,DATEadd(MONTH,datediff(month,0,GETDATE()),0))))) AND (SELECT DateAdd (wk, 1, (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)))) THEN 'MA2'
WHEN wadat_ist BETWEEN (SELECT DateAdd (wk, -3, (SELECT DATEADD(day,-1,DATEadd(MONTH,datediff(month,0,GETDATE()),0))))) AND (SELECT DateAdd (wk, 2, (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)))) THEN 'MA3'
WHEN wadat_ist BETWEEN (SELECT DateAdd (wk, -2, (SELECT DATEADD(day,-1,DATEadd(MONTH,datediff(month,0,GETDATE()),0))))) AND (SELECT DateAdd (wk, 3, (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)))) THEN 'MA4'
WHEN wadat_ist BETWEEN (SELECT DateAdd (wk, -1, (SELECT DATEADD(day,-1,DATEadd(MONTH,datediff(month,0,GETDATE()),0))))) AND (SELECT DateAdd (wk, 4, (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)))) THEN 'MA5'
END AS 'Activity'

But the results are quite different. Values from first week are in the second week, values from second week are in the third and so on .. .all weeks are shifted somehow... Furthmore first week (MA1) is marked as NULL but his values should go to last week (MA5).

Plus I find this one, but it counts few weeks into one, but as the time goes, it matches to the rigt week, so maybe on the first of november will be fine, but I rather need something that will show data anytime in month.

WHEN wadat_ist BETWEEN (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)) AND (select dateadd(wk, datediff(wk, 5, getdate()) - 5, 6)) THEN 'MA1'
WHEN wadat_ist BETWEEN (select dateadd(wk, datediff(wk, 4, getdate()) - 4, 0)) AND (select dateadd(wk, datediff(wk, 4, getdate()) - 4, 6)) THEN 'MA2'
WHEN wadat_ist BETWEEN (select dateadd(wk, datediff(wk, 3, getdate()) - 3, 0)) AND (select dateadd(wk, datediff(wk, 3, getdate()) - 3, 6)) THEN 'MA3'
WHEN wadat_ist BETWEEN (select dateadd(wk, datediff(wk, 2, getdate()) - 2, 0)) AND (select dateadd(wk, datediff(wk, 2, getdate()) - 2, 6)) THEN 'MA4'
WHEN wadat_ist BETWEEN (select dateadd(wk, datediff(wk, 1, getdate()) - 1, 0)) AND (SELECT EOMONTH(getdate(),-1)) THEN 'MA5'

Using T-SQL

Upvotes: 1

Views: 77

Answers (2)

Andriy
Andriy

Reputation: 123

As a one of the solutions you can create TimeTable in your database or temp table with your range of time period and then generate column WeekOfMonth:
1. create table at least with these columns(date, year, month, week, quarter : use DATEPART)
2. generate your column:

WeekOfMonth = DENSE_RANK() OVER (PARTITION BY [year], [month] ORDER BY [week])



If you create a good time table it will be useful for future tasks.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

Try below (partial) query, comments are inline:

-- set monday as first day of week
set datefirst 1;

-- after checking, that 1 of september is 35th week of year, you can do the following:
select case when datepart(week, wadat_ist) = 35 then 'MA1'
            when datepart(week, wadat_ist) = 36 then 'MA2'
            when datepart(week, wadat_ist) = 37 then 'MA3'
            when datepart(week, wadat_ist) = 38 then 'MA4'
            when datepart(week, wadat_ist) = 39 then 'MA5'
       end as 'Activity'

or shorter

select case datepart(week, wadat_ist) 
            when 35 then 'MA1'
            when 36 then 'MA2'
            when 37 then 'MA3'
            when 38 then 'MA4'
            when 39 then 'MA5'
       end as 'Activity'

Upvotes: 0

Related Questions