Add missing dates and values

Question

I have a table which outputs this example data, but for many users and different date ranges:-

Users..........Date..............Hours
John Smith.....Thu 02-JAN-2020...8 
John Smith.....Fri 03-JAN-2020...8 
John Smith.....Mon 06-JAN-2020...8 
John Smith.....Tue 07-JAN-2020...8 
John Doe.......Fri 03-JAN-2020...8
John Doe.......Mon 06-JAN-2020...8 
John Doe.......Tue 07-JAN-2020...8 
John Doe.......Wed 08-JAN-2020...8

Result should read:

Users.............Date........................Hours <br/>
John Smith.....Thu 02-JAN-2020...8 <br/>
John Smith.....Fri 03-JAN-2020...8 <br/>
John Smith.....Sat 04-JAN-2020...0 MISSING WEEKEND <br/>
John Smith.....Sun 05-JAN-2020...0 MISSING WEEKEND <br/>
John Smith.....Mon 06-JAN-2020...8 <br/>
John Smith.....Tue 07-JAN-2020...8 <br/>
John Doe.......Fri 03-JAN-2020...8 <br/>
John Doe.......Sat 04-JAN-2020...0 MISSING WEEKEND <br/>
John Doe.......Sun 05-JAN-2020...0 MISSING WEEKEND <br/>
John Doe.......Mon 06-JAN-2020...8 <br/>
John Doe.......Tue 07-JAN-2020...8 <br/>
John Doe.......Wed 08-JAN-2020...8

I require a query to include the missing weekends, Name and Hours of zero (as DBA will not change the structure of the table and add the weekends).

Have looked at CTE's (Recursives), CROSS JOINS, linking a calendar table, but cannot get this to work

Edit your question and show the results you want. What does "missing weekends" mean? And tag with the database you are using. — Gordon Linoff
– Gordon Linoff, Commented Nov 5, 2020 at 17:24
Pleas tag your question with the database you are running: mysql, oracle, postgresql...? — GMB
– GMB, Commented Nov 5, 2020 at 17:26
@SqlRookie . . . SQL is a language, not a database. Every database has its own dialect, which is important to answer your question so it is most useful to you. — Gordon Linoff
– Gordon Linoff, Commented Nov 5, 2020 at 17:29
Did you use a left outer join when attempting the calendar table? I can't imagine how that could have failed you. — shawnt00
– shawnt00, Commented Nov 5, 2020 at 18:01

Gordon Linoff · Accepted Answer · 2020-11-05 17:50:56Z

1

If I understand correctly, you can use union all to find Fridays where the user has another date. Then add two more rows.

I am going to assume that the date column actually has a date data type.

Here is the idea:

select user, date, hours
from t
union all
select user, dateadd(day, v.n, date), 0
from (select t.*,
             lead(date) over (partition by user order by date) as next_date
      from t
     ) t cross join
     (values (1), (2)) v(n)
where datepart(weekday, t.date) = 'Friday' and
      next_date is not null;

Date/time functions are notoriously database-dependent. This uses SQL Server syntax.

edited Nov 5, 2020 at 17:50

answered Nov 5, 2020 at 17:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add missing dates and values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related