Force creation of records when exist in another table

[beckwiga]

I'm not sure if it's possible, but here is what I'm trying to do...

I have t_fact_pops table which contains records of pops. In this table, I have fields Expected and Available (among others). When a pop is expected but not available, no record is created in this table. So, in this table all records will have a 1 for Expected and a 1 for Available.

I have another table t_suc_pic_bra_pop which contains all possible brand, pop and suc_pic combinations. My goal is to refer to this table and return or create records for those pop ids which are not in my t_fact_pops table.

For example, please see my print screen. In this example, I would like my query to return the 2 rows in the t_suc_pic_bra which do not exist in t_fact_pops. Additionally, not all data in t_fact_pops is available in t_suc_pic_bra_pop which means I would like to use the values of the t_facts table for those columns.

Anybody have any idea if this is possible? Where would I start with this? Any suggestions are much appreciated.

greg

 

[ESquared]

How do I know which rows in t_suc_bra_pop apply to the which rows in t_fact_pops? I'm assuming it is only the columns in the t_suc_bra_pop table and besides those it is an effective cross join with the date, company, and cust_code? With that assumption...

SELECT
   x.date_sk,
   x.company_sk,
   x.cust_code,
   b.brand_id,
   b.pop_id,
   b.suc_pic_id,
   expected = Coalesce(p.expected, 0),
   available = Coalesce(p.available, 0)
FROM
   t_suc_pic_bra_pop b
   CROSS JOIN (
      SELECT DISTINCT
         date_sk,
         company_sk,
         cust_code
      FROM
         t_fact_pops p
   ) x
   LEFT JOIN t_fact_pops p
      ON b.brand_id = p.brand_code
      AND b.pop_id = p.pop_sk
      AND b.suc_pic_id = p.suc_pic_sk
      AND x.date_sk = p.date_sk
      AND x.company_sk = p.company_sk
      AND x.cust_code = p.cust_code

If you don't want every brand code for every date/company/cust combination, then you'll have to add it to the X derived table, to the X join condition, and to the P join condition.

Other questions are: what about missing dates for a company or customer? What about missing dates & companies for a customer? ... And every combination you can think of. I gathered what rows to magically fill in by getting the distinct first three columns of your pop table on the assumption that if any combination of these three is present, then all the brand_ids, pop_ids, and suc_pic_ids in tspbp should be applied to each combination.

However, maybe you have a different place to get the exhaustive list of the desired combinations of date, company, and customer. Or and brand. In that case, you'll need to modify the query to get those, so you can have all the desired invented rows show expected 0 and available 0.

Oh, and bravo on providing the data in a picture. Thank you for not just halfway describing the problem and hoping we could work miracles... knowing the data and the exact desired output makes it not only easy, but possible at all!

Erik

P.S. Your table and column names are very painful to me. t_suc_pic_bra_pop? I know you're not kidding... ouch! the Tee-suck-pick-bra-pop table? Say that ten times fast Also, the difference in column names between the tables makes for some very awkward join writing.