Nsryjdtyk

I have a table where I have all the customers and a table where I have all their restrictions.

CUSTOMER



customer_id customer_name

1 name 1

2 name 2



CUSTOMER_RESTRICTIONS



rest_type day_of_week hour_start hour_stop customer_id



TYPE1  0  08:00  12:00  1

TYPE1  0  13:00  17:00  1

TYPE2  0  17:00  23:59  1

Problem: I only have a record for a restriction type and a customer when the customer has a restriction and this is a problem in the visualization I want to build.

I need every customer, every day and every restriction type, even when there is no restriction. In that case hour_start and hour_stop would be NULL.

For the tables shown, the output would be

rest_type day_of_week hour_start hour_stop customer_id



TYPE1  0  08:00  12:00  1

TYPE1  0  08:00  12:00  1

TYPE1  1  NULL  NULL  1

TYPE1  2  NULL  NULL  1

TYPE1  3  NULL  NULL  1

TYPE1  4  NULL  NULL  1

TYPE1  5  NULL  NULL  1

TYPE1  6  NULL  NULL  1

TYPE1  1  NULL  NULL  1

TYPE1  2  NULL  NULL  1

TYPE1  3  NULL  NULL  1

TYPE1  4  NULL  NULL  1

TYPE1  5  NULL  NULL  1

TYPE2  0  NULL  NULL  1

TYPE2  1  NULL  NULL  1

TYPE2  2  NULL  NULL  1

TYPE2  3  NULL  NULL  1

TYPE2  4  NULL  NULL  1

TYPE2  5  NULL  NULL  1

TYPE2  6  NULL  NULL  1

TYPE1  0  NULL  NULL  2

TYPE1  1  NULL  NULL  2

TYPE1  2  NULL  NULL  2

TYPE1  3  NULL  NULL  2

TYPE1  4  NULL  NULL  2

TYPE1  5  NULL  NULL  2

TYPE1  6  NULL  NULL  2

TYPE2  0  NULL  NULL  2

TYPE2  1  NULL  NULL  2

TYPE2  2  NULL  NULL  2

TYPE2  3  NULL  NULL  2

TYPE2  4  NULL  NULL  2

TYPE2  5  NULL  NULL  2

TYPE2  6  NULL  NULL  2

How can I achieve that? I couldn't even start to build this query.

Essentially you need to start with the data you must have and left join the optional data. E.g., something like this:

select c.customer_id

    ,r.[rest_type]

    ,d.[day_of_week]

    ,r.[hour_start]

    ,r.[hour_stop]

from CUSTOMER c

cross apply (

    select 0 as day_of_week

    union all select 1

    union all select 2

    union all select 3

    union all select 4

    union all select 5

    union all select 6

) d 

left join CUSTOMER_RESTRICTIONS r on c.customer_id = r.customer_id and d.day_of_week = r.day_of_week

Output:

customer_id rest_type day_of_week hour_start hour_stop

----------- --------- ----------- ---------- ---------

1           TYPE1     0           08:00      12:00

1           TYPE1     0           13:00      17:00

1           TYPE2     0           17:00      23:59

1           NULL      1           NULL       NULL

1           NULL      2           NULL       NULL

1           NULL      3           NULL       NULL

1           NULL      4           NULL       NULL

1           NULL      5           NULL       NULL

1           NULL      6           NULL       NULL

If there are only type rest_types, you don't have a lookup table for them, and you want to show a row for each, you would do:

select c.customer_id

    ,t.[rest_type]

    ,d.[day_of_week]

    ,r.[hour_start]

    ,r.[hour_stop]

from CUSTOMER c

cross apply (

    select 0 as day_of_week

    union all select 1

    union all select 2

    union all select 3

    union all select 4

    union all select 5

    union all select 6

) d 

cross apply (

    select 'TYPE1' as rest_type

    union all select 'TYPE2'

) t

left join CUSTOMER_RESTRICTIONS r on c.customer_id = r.customer_id 

    and d.day_of_week = r.day_of_week 

    and t.rest_type = r.rest_type

  (select rest_type, day_of_week,

   hour_start ,

   hour_stop 

   from table A

   where rest_type IS NOT NULL)

   Union

   (select rest_type, 

    day_of_week, 

    NULL ,NULL

    from table A 

     where rest_type IS NULL)

Is this what you want ?

First off, I wouldn't store rest type as you are, that is a bad habit, it should be a reference table!

You need to cross apply to get all your possible combinations, and then add in the values you DO have...

DECLARE @Customer TABLE (Id INT IDENTITY(1,1), Name NVARCHAR(100))

DECLARE @Rest TABLE (Id INT IDENTITY(1,1), Name NVARCHAR(100))

DECLARE @Restrictions TABLE (Id INT IDENTITY(1,1), RestID INT, CustomerID INT, Day_of_Week TINYINT, hour_start TIME, hour_end TIME)



INSERT INTO @Customer (NAME)

VALUES('JOHN'),('SUSAN')



INSERT INTO @Rest (NAME)

VALUES ('TYPE A'),('TYPE B')



INSERT INTO @Restrictions (RestID,CustomerID,Day_of_Week,hour_start,hour_end)

VALUES (1,1,0,'08:00','12:00'),

       (1,1,0,'13:00','17:00'),

       (1,2,0,'17:00','23:59')



;WITH DaysofWeek AS

(

    SELECT 0 AS dow

    UNION ALL

    SELECT dow+1

    FROM DaysofWeek

    WHERE dow<5

)



SELECT *

FROM @Customer C

CROSS APPLY @Rest R

CROSS APPLY DaysofWeek D

LEFT JOIN @Restrictions X

   ON X.Day_of_Week=D.dow

   AND X.CustomerID=C.Id

   AND X.RestID=R.Id

ORDER BY C.Id, D.dow, R.Id