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



