CODEWITHSUNDEEP
sqlsql-server
Nisha Nethani
Nisha Nethani

Reputation: 109

sql how to add missing week to the table

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

This is what I have

enter image description here

and this is what i want to add the missing week

enter image description here

Upvotes: 0

Views: 990

Answers (3)

duduwe
duduwe

Reputation: 1260

Assuming that #weekly_calendar table contains your valid work weeks (i.e., for Dec 2015). By the way, syntax is for MSSQL. You should specify what database you are using.

You can also dynamically create the calendar on run-time. This is just to show the concept in an easy to understand way.

-- week start dates
-- 2015-12-01
-- 2015-12-07
-- 2015-12-14
-- 2015-12-21
-- 2015-12-28

create table #weekly_calendar (
    week_start_date datetime,
    week_end_date datetime
)

Assuming that #report_date contains the report date of the employee.

-- report dates
-- 2015-12-02
-- 2015-12-15
-- 2015-12-29

create table #report_date (
    report_date datetime
)

This is how you display the unreported dates.

select * from #weekly_calendar w
left join #report_date r
on r.report_date between w.week_start_date and w.week_end_date

If you do not have the week_end_date. Again, assuming your work days start from Monday to Friday.

select * from #weekly_calendar w
left join #report_date r
on r.report_date between w.week_start_date and DATEADD(dd, 6-(DATEPART(dw, w.week_end_date)), w.week_end_date)

enter image description here

Upvotes: 0

Justin
Justin

Reputation: 9724

Query SQLFIDDLEEXAMPLE:

CREATE TABLE tb 
    (
     d1 date, 
     d2 date
    );

INSERT INTO tb
(d1, d2)
VALUES
('2015-12-10', '2015-12-07'),
('2015-12-15', '2015-12-14'),
('2015-12-29', '2015-12-28'),
('2016-01-05', '2016-01-04');

SET DATEFIRST 1 

INSERT INTO tb
( d1, d2 )
select null, DATEADD(day,number,'2015-01-01')
FROM master..spt_values t1
LEFT JOIN tb t2
ON DATEADD(day,number,'2015-01-01') = t2.d2
WHERE type = 'P'
AND DATEADD(day,number,'2015-01-01') >= '2015-12-01'
AND DATEADD(day,number,'2015-01-01') <= '2016-01-04'
AND DATEPART(weekday,DATEADD(day,number,'2015-01-01')) = 1
AND t2.d2 is null

SELECT *
FROM tb

Result:

|         d1 |         d2 |
|------------|------------|
| 2015-12-10 | 2015-12-07 |
| 2015-12-15 | 2015-12-14 |
| 2015-12-29 | 2015-12-28 |
| 2016-01-05 | 2016-01-04 |
|     (null) | 2015-12-21 |

Upvotes: 1

pberggreen
pberggreen

Reputation: 958

You can create a new Calendar/Weeks table containing all the weeks in the year. This table should be in advance.

You can then make a reference from your data table to this calendar table (by id or week/year).

Your report should be based on the calendar table with an outer join to your data table.

This way your report will contain all weeks even if some weeks don't have any data.

EDIT: You would need a new table like this:

Week:
| Start date | End date |
| 12/07/15   | 12/13/15 |
| 12/14/15   | 12/20/15 |
| 12/21/15   | 12/27/15 |
etc...

Upvotes: 0

Related Questions