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.

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

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') 

CodePudding user response：

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

fiddle