CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
FLICKER
FLICKER

Reputation: 6683

Add missing rows to result set

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?

Upvotes: 1

Views: 75

Answers (1)

akuiper
akuiper

Reputation: 214957

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.

Upvotes: 1

Related Questions