Adding a new records to missing rows

Transact-SQL
1

Hello SQL Expert,

Below is my simple SQL query:

SELECT Date, hours
FROM Timesheet_tbl

And here is the result set I ran for November. I have missing records because of the weekend. What I am trying to add is add missing row from previous row data. So Saturday will have Friday data, Sunday will have Saturday data.

Ex: 11/02/2019 (Saturday) will have the same date on 11/01/2019 and 11/03/2019 will have the same data on Saturday 11/02/2019.

The same concept apply for 11/09/2019, 11/10/2019, 11/16/2019 and 11/17/2019 (Saturday and Sunday)

Timesheet%20SQL

How do I add records to missing date records?

Thank you all

2

hi

i tried to do this .. hope this helps :slight_smile:

please click arrow to the left for drop create data 
drop table Timesheet_tbl
go 

create table Timesheet_tbl
(
[Date] date,
hours int 
)
go 

insert into Timesheet_tbl select '2019-11-01',3 
insert into Timesheet_tbl select '2019-11-04',5
insert into Timesheet_tbl select '2019-11-05',4
insert into Timesheet_tbl select '2019-11-06',8
insert into Timesheet_tbl select '2019-11-07',8
insert into Timesheet_tbl select '2019-11-08',5
insert into Timesheet_tbl select '2019-11-11',4
insert into Timesheet_tbl select '2019-11-12',2
insert into Timesheet_tbl select '2019-11-13',4
insert into Timesheet_tbl select '2019-11-14',2
insert into Timesheet_tbl select '2019-11-15',5
insert into Timesheet_tbl select '2019-11-18',7
insert into Timesheet_tbl select '2019-11-19',8
insert into Timesheet_tbl select '2019-11-20',3
go 


select 'Timesheet_tbl data ',* from Timesheet_tbl
go

image
image718×881 13.8 KB

this SQL is a bit long .. it can be made short....

please click arrow to the left for SQL 
; WITH cte 
     AS (SELECT d1= Min(date), 
                d2 = Max(date) 
         FROM   timesheet_tbl 
         UNION ALL 
         SELECT Dateadd(dd, 1, d1), 
                d2 
         FROM   cte 
         WHERE  Dateadd(dd, 1, d1) <= d2), 
     cte_sat 
     AS (SELECT d1, 
                Datename(weekday, d1) AS weekday, 
                b.date, 
                CASE 
                  WHEN Datename(weekday, d1) IN ( 'SATURDAY' ) THEN Lag(b.hours) 
                  OVER( 
                    ORDER BY d1) 
                  ELSE b.hours 
                END                   AS hours 
         FROM   cte a 
                LEFT JOIN timesheet_tbl b 
                       ON a.d1 = b.date), 
     cte_sun 
     AS (SELECT d1, 
                weekday, 
                CASE 
                  WHEN weekday IN ( 'SUNDAY' ) THEN Lag(hours) 
                                                      OVER( 
                                                        ORDER BY d1) 
                  ELSE hours 
                END AS hours 
         FROM   cte_sat) 
SELECT * 
FROM   cte_sun 

go

image
image932×1218 26 KB

3

hi

slightly smaller SQL ... same output

please click arrow to the left for Shorter SQL 
; WITH cte 
     AS (SELECT d1= Min(date), 
                d2 = Max(date) 
         FROM   timesheet_tbl 
         UNION ALL 
         SELECT Dateadd(dd, 1, d1), 
                d2 
         FROM   cte 
         WHERE  Dateadd(dd, 1, d1) <= d2), 
     cte_fin 
     AS (SELECT d1, 
                Datename(weekday, d1) AS weekday, 
                b.date, 
                CASE 
                  WHEN Datename(weekday, d1) IN ( 'SATURDAY' ) THEN Lag(b.hours) OVER( ORDER BY d1) 
                  WHEN Datename(weekday, d1) IN ( 'SUNDAY' ) THEN Lag(b.hours,2) OVER( ORDER BY d1) 
                  ELSE b.hours 
                END                   AS hours 
         FROM   cte a 
                LEFT JOIN timesheet_tbl b 
                       ON a.d1 = b.date)
SELECT d1,weekday,hours 
FROM   cte_fin 
go

image
image918×1213 27.1 KB