Get all dates from dim_table join a fact

I have two tables in SQL server 2019

A table calendar:

date_calendar
01-01-22
02-01-22
03-01-22
04-01-22
05-01-22
06-01-22

And a fact table user with some duty:

user	date_work	duty
1	04-01-22	abc
1	04-01-22	def
2	03-01-22	xyz
2	03-01-22	fgt
2	04-01-22	mop
1	05-01-22	rfg
2	06-01-22	tuy

and this is the expected result:

date	user	date_work	duty
01-01-22	1	null	null
01-01-22	2	null	null
02-01-22	1	null	null
02-01-22	2	null	null
03-01-22	1	null	null
03-01-22	2	03-01-22	xyz
03-01-22	2	03-01-22	fgt
04-01-22	1	04-01-22	abc
04-01-22	1	04-01-22	def
04-01-22	2	04-01-22	mop
05-01-22	1	05-01-22	rfg
05-01-22	2	null	null
06-01-22	1	null	null
06-01-22	2	06-01-22	tuy

I tried this:

WITH PWR as
(
SELECT 
    user,date_work,duty
FROM Fact_Table
),
calendar as
(
select date_calendar
from Dim_Calendar
)
select 
     date_calendar
    ,pwr.user
    ,pwr.date_work
    ,pwr.duty
from calendar
left join pwr on calendar.date_calendar = pwr.date_work

CodePudding user response：

There's no need for your CTE, it actively does nothing other than alias the table names.

Then, to guarantee you have every user for every date, regardless of whether they're working, you need to CROSS JOIN the list of dates with the list of users.

Assuming you have a user table, that gives...

SELECT
     calendar.date_calendar
    ,user.user
    ,pwr.date_work
    ,pwr.duty
FROM
  dim_calendar   AS calendar
CROSS JOIN
  dim_user       AS user
LEFT JOIN
  fact_table     AS pwr
    ON  pwr.date_work = calendar.date_calendar
    AND pwr.user      = user.user

Then, if you need to filter by dates or users, you include the dimension tables in the where clause (not the fact table).

CodePudding user response：

If you don't have a Dim_Users table, you can certainly simulate one:

;WITH Dim_Users AS
(
  SELECT [user] 
  FROM dbo.Fact_Table
  GROUP BY [user]
)
SELECT * FROM Dim_Users 
  CROSS JOIN dbo.Dim_Calendar AS dates
  LEFT OUTER JOIN dbo.Fact_Table AS f
  ON f.date_work = dates.date_calendar
  AND Dim_Users.[user] = f.[user]
  ORDER BY dates.date_calendar, Dim_Users.[user];

CodePudding user response：

It looks like you need to start with a master list of each unique date of work and user.

select distinct
      c.date_calendar,
      f.user
   from
      calendar c
         cross join fact f

Now, that can be the basis of all possible dates and users. Now, just re-LEFT join back to the user table on the date/user combination

select
      AllUniq.date_calendar,
      AllUniq.user,
      f2.date_work,
      f2.duty
   from
      ( select distinct
              c.date_calendar,
              f.user
           from
              calendar c
                 cross join fact f ) AllUniq
         LEFT JOIN fact f2
            on AllUniq.date_calendar = f2.date_work
           AND AllUniq.user = f2.user