Add missing rows to result set

Question

I asked similar questions hour ago. Previous Question

This time situation is different

I have a fact and dim table. Comparing previous question, my fact table has an extra field called Amount

create table #fact (SKey int, HT varchar(5), TitleId int, Amount decimal(15,2))

insert into #fact values
(201707, 'HFI', 1, 15000),
(201707, 'HFI', 3, 16000),
(201707, 'HFI', 5, 17000),
(201707, 'HFI', 6, 18000),
(201707, 'REO', 1, 19000),
(201707, 'REO', 2, 20000),
(201707, 'REO', 4, 21000),
(201707, 'REO', 5, 22000)

create table #dim (TitleId int, Title varchar(10))
insert into #dim values
(1, 'UK'),
(2, 'AF'),
(3, 'LQ'),
(4, 'AL'),
(5, 'GT'),
(6, 'ML')

using below query

select #fact.SKey, #fact.HT, #fact.TitleId, #dim.Title, #fact.Amount
from #fact
    inner join #dim on #dim.TitleId = #fact.TitleId
order by #fact.SKey, #fact.HT, #fact.TitleId, #dim.Title

which returns me following data

   SKey    HT    TitleId   Title    Amount   
 -------- ----- --------- ------- ---------- 
  201707   HFI         1   UK      15000.00  
  201707   HFI         3   LQ      16000.00  
  201707   HFI         5   GT      17000.00  
  201707   HFI         6   ML      18000.00  
  201707   REO         1   UK      19000.00  
  201707   REO         2   AF      20000.00  
  201707   REO         4   AL      21000.00  
  201707   REO         5   GT      22000.00

You see there are missing Titles in the result. for example, I don't have 'AF' and 'AL' for the first set ('HFI' set) and don't have 'LQ' and 'ML' for 'REO' part.

In summary I'm going to generate below result:

   SKey    HT    TitleId   Title    Amount   
 -------- ----- --------- ------- ---------- 
  201707   HFI         1   UK      15000.00  
  201707   HFI         2   AF          0.00  -- missing from first result
  201707   HFI         3   LQ      16000.00  
  201707   HFI         4   AL          0.00  -- missing from first result
  201707   HFI         5   GT      17000.00  
  201707   HFI         6   ML      18000.00  
  201707   REO         1   UK      19000.00  
  201707   REO         2   AF      20000.00  
  201707   REO         3   LQ          0.00  -- missing from first result
  201707   REO         4   AL      21000.00  
  201707   REO         5   GT      22000.00  
  201707   REO         6   ML          0.00  -- missing from first result

for missing rows I want to show Amound as 0.00

currently I'm store the first result into a temp table and then use a loop/cursor to add missing rows into int.

Is there any way we use just one query to get the final result?

akuiper · Accepted Answer · 2017-11-04 01:55:36Z

1

If the previous suggestion works for you, then you could do something similar by join the result back with the fact table, and use coalesce to replace null Amount with 0:

;with f as (
  select SKey, HT from fact
  group by SKey, HT
), combns as (
  select f.SKey, f.HT, dim.TitleId, dim.Title
  from f, dim
) 
select combns.*, coalesce(fact.Amount, 0) as Amount
from combns
left join fact 
on combns.SKey=fact.SKey and
   combns.HT=fact.HT and
   combns.TitleId=fact.TitleId

See the fiddle here.

answered Nov 4, 2017 at 1:55

akuiper

216k33 gold badges363 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FLICKER Over a year ago

Worked fine. Thanks again.

Collectives™ on Stack Overflow

Add missing rows to result set

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related