Spreading monthly inserts to weekly grain

  • Matthew Cushing

    SSCrazy

    Points: 2898

    I have a table that have different grains - in most cases it's weekly, except in some cases for some customers, they put in a monthly entry.  In most cases, I have an entry for each week during the month, but occasionally there is only one entry and I need to spread that across all weeks in that particular month.  I've got the spreading across all weeks calculating properly in it's own view, my issue is ignoring the monthly entries and only use the weekly entries I've calc'd in another view.

    So if there's an entry for 20$ on one week for that month and there are four weeks in that month, I want to remove the 20$ entry and break it out into four 5$ entries.  The issue is that there isn't much to tie them together except the customer and the date.  My thought was to test to see if there's only one entry for that month and I have a weekly spread of entries, to just ignore it, but in some cases there are more than one entry in that month.

    any suggestions would be welcome.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    How about a sample table, sample data and expected final data state?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • pietlinden

    SSC Guru

    Points: 62677

    Are you doing your analysis is Excel or PowerBI? (You can use DAX for that... I'd check on Ferrari & Russo's website(s) for answers on DAX... I know there's one on handling different granularities on http://www.daxpatterns.com.

    But without a lot more information, it's hard to tell.

    Are you trying to do this purely in T-SQL?  What's your presentation layer - Excel, PowerBI, SSRS? Something else?

    Here's the link to the F& R's DAXPatterns page...

  • Matthew Cushing

    SSCrazy

    Points: 2898

    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.

  • Jeff Moden

    SSC Guru

    Points: 995652

    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.

    See the first article in my signature line below under "Helpful Links", Matt.  You'll get a lot more help a lot more quickly that way.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply