I am trying to create a table in SQL where I reflect data for all days of a particular month.

For example, even if there is no Sale transaction for a particular day, the day for the particular employee should still be reflective in the table.

Scenario for the question. I am working with the following dates for this example: 1st, 2nd, 3rd and 4th Jan 2022.

The raw data is as follows:

As seen above, we only have data for the 1st and 3rd.

The outcome should look as follows:

As seen above, the 2nd and 4th should be included even though there was no activity for those days.

What I am currently trying:

I have a master date table which is being used as a RIGHT JOIN on the transaction table. However, the final outcome of my table is as follows:

As seen above, the 'Name' field returns as NULL. The SaleAmount however should reflect NULL to indicate no transactions happening.

I would appreciate any assistance on this.

CodePudding user response：

Seems like you want to

• Start with the date table
• Cross join to your employee/salesperson table so you now have one row for each salesperson on each date
• Left join the sales orders for that date salesperson combo to get the sum of their sales for that day. If they have none, it'll show null:

select emp.Name
      ,dat.Date
      ,sum(ord.Amount) as SaleAmount
from            dateList    dat
     cross join salesPerson emp
      left join salesOrder  ord on ord.OrderDate = dat.Date and ord.SalesPersonId = emp.SalesPersonId
group by emp.Name
        ,dat.Date

CodePudding user response：

you can create a list of dates on the fly. as example per month

Declare @year int = 2022, @month int = 7;
WITH numbers
as
(
    Select 1 as value
    UNion ALL
    Select value   1 from numbers
    where value   1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
)
SELECT datefromparts(@year,@month,numbers.value) Datum FROM numbers

then left join to your table.