sql get every week start date between two dates

Question

I have a table that has this data Date when the employees reported and the week start date(monday) for that week. Now they did not work all the dates for example there is no data on week of christmas. Is there a way I can add the missing week.So, I will still have the week start date for each and every week. But the report date can be null.

I cannot declare variables.

Take a look here: http://stackoverflow.com/questions/9180308/ssrs-default-parameter-values-in-subscription/9180701#9180701 — JonH, Jan 29 '16 at 18:28
@haytem - You must have not read it...The scalar valued functions allow him to make a call to get weekstart_date for ANY date. It very much is related. In fact, he could simply call `SELECT * FROM [MyDB].[dbo].[udfCommonDates] (GetDate())` or do a `SELECT get_week_start FROM [MyDB].[dbo].[udfCommonDates] (GetDate())` and load or join to this table. — JonH, Jan 29 '16 at 18:35
Oh my bad, I didn't see this. Yes you are right this is a very elegant solution to this problem. — Haytem BrB, Jan 29 '16 at 18:38
the dates they need are missing from the table.. how will a `get_week_start` function help if there are no dates for that week in the table? — JamieD77, Jan 29 '16 at 18:43
@JamieD77 - Simple you pass in a date to get the weeks that are missing. — JonH, Jan 29 '16 at 18:51

score 2 · Answer 1 · answered Jan 29 '16 at 18:44

2

With your data, probably the easiest way is this:

select distinct weekstart_date
from t
union
select distinct dateadd(day, 7, weekstart_date)
from t;

You never seem to be missing more than one week, so just combining each week with the next week seems sufficient.

answered Jan 29 '16 at 18:44

Gordon Linoff

1,242,037
58
646
786

distinct with union? does that make it extra distincty? – JamieD77 Jan 29 '16 at 18:49
@JamieD77 - union is needed to pull the rest of the query. Without it this wouldn't work. – JonH Jan 29 '16 at 18:49
@That's true JonH, but UNION makes the resultset distinct.. just wondering if the 2 extra distincts help do something i'm not aware of – JamieD77 Jan 29 '16 at 18:54
@JamieD77 . . . Interesting observation. It is there for two reasons. First, the first subquery can use an index on the column. Second, reducing the volume of data from each table is a boost to performance, given the performance characteristics of `distinct`. However, this is (in all likelihood) a very small gain in this case. – Gordon Linoff Jan 30 '16 at 22:02

score 1 · Answer 2 · answered Jan 29 '16 at 18:56

You can do this with a recursive cte if there are more than 1 week gaps.

WITH WeeklyCTE AS 
(
    SELECT  MIN(weekstart_date) AS weekstart_date
    FROM    MyTable
    UNION ALL
    SELECT  DATEADD(week, 1, weekstart_date)
    FROM    WeeklyCTE
    WHERE   DATEADD(week, 1, weekstart_date) <= GETDATE()
)
SELECT  t.reportdate,
        w.weekstart_date
FROM    WeeklyCTE w
        LEFT JOIN MyTable t ON w.weekstart_date = t.weekstart_date

sql get every week start date between two dates

2 Answers2