Q:

Bring up a value for an entry that does not exist

I am struggling with a following issue (SQL Server).
I have a table that looks like this 
DATE    Type  max_orders   ID
-------------------------------
null    1       600       10000
null    2       600       10001
null    3       600       10002
4.11    1       500       10003
4.11    2       300       10004
4.11    3       250       10005
5.11    1       550       10006
5.11    2       500       10007
5.11    3       400       10008

What I want to do is to write a query, that will tell me what is the amount of max_orders for Today, Tomorrow and the day after Tomorrow.
Let's assume that today is the 3.11 and I have no entry for that date in my table which means that the max_orders for that day should be the same as where the date is null (600 in this case) 
Like this:
Type  max_orders_Today max_orders_tmrw  max_orders_dayafter
------------------------------------------------------------
    1       600              500                550
    2       600              300                500
    3       600              250                400

My faulty query looks like this:
with cap as 
(
    select 
        date, type, max_orders, ID 
    from 
        mytable
    where 
        type in ('1','2','3') 
)
select 
    d.type,
    case 
       when d.Date = GETDATE() then d.max_orders 
       when d.Date IS NULL then cap.max_orders 
    end as today,
    case 
       when d.Date = DATEADD(day, +1, GETDATE()) then d.max_orders 
       when d.Date IS NULL then cap.max_orders 
    end as tomorrow,
    case 
       when d.Date = DATEADD(day, +2, GETDATE()) then d.max_orders 
       when d.Date IS NULL then cap.max_orders 
    end as day_after
from 
    mytable AS d
left join 
    cap on d.Id = cap.Id

It returns a bunch of nulls everywhere. I'd appreciate any help I can get!

A:

First of all you want to select three types, 1, 2, and 3, no matter whether data is available for them. So start with creating records for these. Then join the default values. At last select the date specific values in subqueries in your SELECT clause.
select 
  type,
  coalesce
  (
    (
      select max_orders from mytable 
      where type = types.type and date = getdate()
    )
  ), defaults.max_orders) as max_orders_today,
  coalesce
  (
    (
      select max_orders from mytable 
      where type = types.type and date = dateadd(day, 1, getdate()) 
    )
  ), defaults.max_orders) as max_orders_tmrw,
  coalesce
  (
    (
      select max_orders from mytable 
      where type = types.type and date = dateadd(day, 2, getdate()) 
    )
  ), defaults.max_orders) as max_orders_dayafter
from
(
  select 1 as type union all 
  select 2 as type union all 
  select 3 as type 
) as types
left join
(
  select type, max_orders 
  from mytable 
  where date is null
) as defaults on defaults.type = types.type
order by types.type;

