Search code examples
sqlsql-serverdatesql-server-2012left-join

Get the missing dates substituted with 0 in one column and showing a varchar value in another column

I have 3 columns in TableData, namely CDate, Drivername, Trips. Now I am creating a monthly report in order to show the list of drivers with their trips. Also I want to show the driver's data (showing trips=0) who did not work on a specific day(s) during this month.
I have tried "join" methods, coalesce function but all in vain... I appreciate if anyone here could help me fix this issue.

I have seen so many sample queries here which yields one column result that sets the value to zero or getting the aggregate of single column.

TableData contains the actual data

TableCalc the table that contains Dates

TableOutput is what am looking for

I actually need for the complete month; but for easier reference i have used the date till 8th of May 2022. Please note, there are around 45 drivers in the list..
Below are some workarounds from me:

select CDATE,DriverName from(
select CDate=convert(date,CDate) from TableCal where month(CDate)=5 AND year(CDate)=2022
)AllDays left join
(select TDate,Drivername,Trips=count(*) from TableData where month(TDate)=5 and year(TDate)=2022 group by drivername,TDate
)tm on CDate=TDate group by Cdate,DriverName
 ORDER BY Drivername,Cdate

Wrong Output

Sample DDL and DML as follows:

    CREATE TABLE [dbo].[tableData](
    [CDate] [date] NULL,
    [DriverName] [nvarchar](20) NULL,
    [Trips] [int] NULL
) ON [PRIMARY]

INSERT INTO tableData(CDate,DriverName,Trips)     VALUES('2022-05-01','Michael',5)  
INSERT INTO tableData(CDate,DriverName,Trips)     VALUES('2022-05-03','Michael',7)  
INSERT INTO tableData(CDate,DriverName,Trips)     VALUES('2022-05-04','Michael',8)  
INSERT INTO tableData(CDate,DriverName,Trips)     VALUES('2022-05-05','Michael',13) 

INSERT INTO tableData(CDate,DriverName,Trips)     VALUES('2022-05-01','Sam',5)  
INSERT INTO tableData(CDate,DriverName,Trips)     VALUES('2022-05-04','Sam',5)  
INSERT INTO tableData(CDate,DriverName,Trips)     VALUES('2022-05-05','Sam',13)  
INSERT INTO tableData(CDate,DriverName,Trips)     VALUES('2022-05-06','Sam',9)  


CREATE TABLE [dbo].[TableCal](
    [CDate] [date] NULL
) ON [PRIMARY]


INSERT INTO TableCal(CDate)     VALUES('2022-05-01')
INSERT INTO TableCal(CDate)     VALUES('2022-05-02')  
INSERT INTO TableCal(CDate)     VALUES('2022-05-03')  
INSERT INTO TableCal(CDate)     VALUES('2022-05-04')  
INSERT INTO TableCal(CDate)     VALUES('2022-05-05')  
INSERT INTO TableCal(CDate)     VALUES('2022-05-06')  
INSERT INTO TableCal(CDate)     VALUES('2022-05-07')  
INSERT INTO TableCal(CDate)     VALUES('2022-05-08')
Solution

  • With a CTE you don't need a calender table, you can still used it, if you have dates to exclude for example

    But you should know that Sql SERVER 2012 has reached its end of Life last July(2022) see https://learn.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-2012

    So you should think about migrating

    with cte as (
    select 
        DriverName, 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, min(CDate)), 0)  dt
        , DATEADD(MONTH, DATEDIFF(MONTH, -1, min(CDate)), -1) maxDt 
        from tableData
        WHERE month(CDate)=5 AND year(CDate)=2022 
        group by DriverName
    union all
    select 
        DriverName,  
        dateadd(DAY, 1, dt),
        maxDt
    from cte
    where dt < maxDt
)
select c.DriverName,  FORMAT (c.dt, 'yyyy-MM-dd'), coalesce(t.Trips, 0) avalue 
from cte c
left join tableData t 
    on  t.DriverName = c.DriverName
    and t.CDate = c.dt  
order by c.DriverName,  c.dt
    DriverName (No column name) avalue
    Michael 2022-05-01 5
    Michael 2022-05-02 0
    Michael 2022-05-03 7
    Michael 2022-05-04 8
    Michael 2022-05-05 13
    Michael 2022-05-06 0
    Michael 2022-05-07 0
    Michael 2022-05-08 0
    Michael 2022-05-09 0
    Michael 2022-05-10 0
    Michael 2022-05-11 0
    Michael 2022-05-12 0
    Michael 2022-05-13 0
    Michael 2022-05-14 0
    Michael 2022-05-15 0
    Michael 2022-05-16 0
    Michael 2022-05-17 0
    Michael 2022-05-18 0
    Michael 2022-05-19 0
    Michael 2022-05-20 0
    Michael 2022-05-21 0
    Michael 2022-05-22 0
    Michael 2022-05-23 0
    Michael 2022-05-24 0
    Michael 2022-05-25 0
    Michael 2022-05-26 0
    Michael 2022-05-27 0
    Michael 2022-05-28 0
    Michael 2022-05-29 0
    Michael 2022-05-30 0
    Michael 2022-05-31 0
    Sam 2022-05-01 5
    Sam 2022-05-02 0
    Sam 2022-05-03 0
    Sam 2022-05-04 5
    Sam 2022-05-05 13
    Sam 2022-05-06 9
    Sam 2022-05-07 0
    Sam 2022-05-08 0
    Sam 2022-05-09 0
    Sam 2022-05-10 0
    Sam 2022-05-11 0
    Sam 2022-05-12 0
    Sam 2022-05-13 0
    Sam 2022-05-14 0
    Sam 2022-05-15 0
    Sam 2022-05-16 0
    Sam 2022-05-17 0
    Sam 2022-05-18 0
    Sam 2022-05-19 0
    Sam 2022-05-20 0
    Sam 2022-05-21 0
    Sam 2022-05-22 0
    Sam 2022-05-23 0
    Sam 2022-05-24 0
    Sam 2022-05-25 0
    Sam 2022-05-26 0
    Sam 2022-05-27 0
    Sam 2022-05-28 0
    Sam 2022-05-29 0
    Sam 2022-05-30 0
    Sam 2022-05-31 0

    fiddle