Spreading monthly inserts to weekly grain

Question

Post reply

Spreading monthly inserts to weekly grain

Matthew Cushing

SSCrazy

Points: 2898
More actions
August 21, 2017 at 3:24 pm

#328043

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.

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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: 63428 More actions · Answer 2

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 More actions · Answer 3

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: 1003863 More actions · Answer 4

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.

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)