Matthew Cushing - Tuesday, August 22, 2017 8:40 AM

My apologies, I appreciate you getting back to me with so little to work with 🙂

Essentially what I'm trying to do is take the information in Table1 and Table2 and combine them to become the source for a merge statement for Table 3. The entries in Table1 are done weekly for the most part, but they do have some that are monthly. What I'm attempting is to take the monthly entries and spread them evenly across the weeks for that fiscal month and ignore the monthly entries.

Table1
itemID not null varchar(10),
CustID not null varchar(20),
Quantity not null int,
StartDate not null date,
EndDate not null date

Table2 & Table3
itemKey not null int,
CustomerKey not null int,
Quantity not null int,
dateKey not null int (equates to the startdate)

The tables are basically the same, table1 has joins to dimensions to grab keys that insert into the final table, much like table2. So the dilemma is this: How do I ignore the row in Table1 that corresponds to the spread values in Table2? I tried a union, but the merge tries to update the same row twice and fails. In this case, it's the table2.datekey = 20170814 because it's already been updated by the row in Table1.

Table1
ItemID    CustAccountId    Quantity       StartDate    EndDate    
775000    1101085                100        8/14/2017    8/20/2017
                
Table2                
ItemKey    CustomerKey        Quantity    DateKey    
250            11480            20        20170731    
250            11480            20        20170807    
250            11480            20        20170814    
250            11480            20        20170821    
250            11480            20        20170828    

Instead of unioning to Table1, I'm thinking I need to check for rows in Table1 for rows in Table2 for that fiscal month, and if they exist in table2, don't bring them back. Was thinking of using a view that used the Fiscal_Month_Start in our dimDate to see if it exists in Table2, but I'm not sure that'd work. In the above example, Fiscal_Month_Start for both tables would be 7/31/2017.

To make it even more fun, in a few cases, I noticed that there are two monthly entries, both in the same fiscal month. Thinking that the logic I outlined above might work, but again, I'm still working it out.

Hopefully this is a little clearer. Again, my apologies for the vagueness of the original post.