SQL Syntax

Okay, let me see if I can restate your problem:

Finally, your question, if generalized a bit, is, "How do you get the Month To Date and Year To Date figures for the Ending Inventory and Ending Inventory LY for your reports?"

If the above is correct, then I think that you don't really need to [u]overwrite[/u] the data for the reports as much as you need to accumulate that data.  How are you accumulating the data for the MTD figures for this year?  Is it by the start or end date of the week bieing in a particulare month or is it based upon certain weeks of the year being defined as being in certain months?  

If you have the Weekly figures in a table and you create (or have) a Calendar table that indicates which month/quarter/year certain dates (whether it is the first day of the week or the last day of the week) fall into, then you should be able to sum the figures for the WeekToDate and WTD_LY and assign them to the appropriate month/quarter/year for the reports.

I have the data to perform WTD, MTD and YTD reports. The only problem is for whichever report is being run I need to have the latest week for 2 of the columns. The rest of the data is whatever is being queried.
For example:

WTD report
Bus Unit         Region            End Inventory       End Inventory LY(Last YEar)
latest weeks data applies with End Inventory and End Inventory LY summed

MTD report
Bus Unit        Region                End Inventory     End Inventory LY(Last YEar)
current weeks data applies except for End Inventory     End Inventory LY(Last YEar)
I would need the WTD values for these 2 columns instead of for the whole month Summed(see above)

YTD report
same as MTD report

FYI...I store integer values for Week, Month and Year in my fact table which are being used as parameters to generate the report and I do have a calendar table that I can join too...

Thanks for your help!!

Also I really didnt want to update anything. I was thinking to add 2 fields to the table and update those values leaving the originals in place..Just a thought

Bus Unit        Region                End Inventory     End Inventory LY(Last YEar)
current weeks data applies except for End Inventory     End Inventory LY(Last YEar)
I would need the WTD values for these 2 columns instead of for the whole month Summed(see above)

So, you want to sum the values for the first 2 columns but just use the most recent weeks values for the last 2 columns?

If that is true than, in the query you use to build the MTD and YTD values for the cube, simply don't sum the wWTD values.  Instead, sum the MTD/YTD values and then JOIN to the latest WTD values, selecting the MTD/YTD calculated values from the part of the overall query that sums theose and the WTD values from the part of the overall query that selects those.  Something like the following:

SELECT MTD.Bus_Unit
                ,MTD.Region
                ,WTD.End_Inventory
                ,WTD.End_Inventory_LY
FROM
(
  SELECT  1 DummyJoinValue
                   ,SUM(whatever) Bus_Unit
                   ,SUM(Somethingelse) Region
  FROM {wherever}
  WHERE {constraints}
) MTD
INNER JOIN
(
  SELECT  1 DummyJoinValue
                    ,End_Inventory
                    ,End_Inventory_LY
  FROM  {weekly datat) 
  WHERE {constraints}
) WTD
ON MTD.DummyJoinValue = WTD.DummyJoinValue

Select allOpen in new window

Well the first 2 values are grouped not summed but that is all done in the cube so there is no need to do any grouping or summing. I think I may have explained what I needed wrong because what I want is the ability to add the latest weeks End Inventory & LY  values to the existing records, for that month and year,  that these do not normally apply. I think this could explain this better.

Raw data (If the latest download week was 7)                                           2 possible new columns
Bus Unit    Region   End Inv.    End Inv. LY    Week    Month    Year             End Inv. New       End Inv LY New
------------------------------------------------------------------------------------           -------------------------------------------
Full Line     SE          120             123                 5           2          2011             87                        91
Full Line     NW         90               99                   6          2           2011             87                       91    
Full Line     SW         87               91                   7          2           2011             87                       91
Full Line     NE          104             101                 4          2           2011             87                       91

The latest week is 7 that is the End Inv and LY that I need for all the other weeks in that Month and Year. So I was thinking to update the new fields to the latest End Inv for use in MTD and YTD reports. I was lookin for an update to these 2 new fields but it would have to be by Bus Unit, Region, Month and Year.

Make sense?

Thansk for all your help

Well the first 2 values are grouped not summed

Can you explain the difference?  

Suppose you have the following:
Raw data (If the latest download week was 7)                                           2 possible new columns
Bus Unit    Region   End Inv.    End Inv. LY    Week    Month    Year             End Inv. New       End Inv LY New
------------------------------------------------------------------------------------           -------------------------------------------
Full Line     SE          120             123                 1           2          2011             87                        91
Full Line     SE            90               99                  3          2           2011             87                       91    
Full Line     SE            87               91                  4          2           2011             87                       91
Full Line     SE          104             101                 2          2           2011             87                       91

What is your MTD data going to look like for Full Line, SE, Month 2, of Year 2011?

From your example, it appears that you are maintaining only one Inventory for all regions but trying to report  for each region.

Bus Unit      Region     End Inv    End Inv. LY    Week    Month    Year
Full Line         SE           87              91                 5          2          2011

Your right, I am trying to maintain one inventory by the month. For instance, I would want to wee all the weeks in Month 2 for Bus Unit = Full Line and Region = SE to be the latest weeks values for Inventory. I will send you a screenshot of my WTD, MTD and YTD reports

Thanks for all your help

Just out of curiosity, if you look at the data I presented, there is no "Week 5" . . . so, how does your response relate to my question? :-/

Your right, I am trying to maintain one inventory by the month.
Does that mean that the inventory is for all regions?  I.e. all regions draw from the same available of products/wheatever?

I looked at the spreadsheet and I can't figure out how you got the MTD and YTD figures.  If you look at the figures just the figures for the SE Region:
Week 7 WTD indicates that Sales were 100 and Sales LY were 110.  Then the MTD Sales and Sales LY were 320 and 185.  Are these not summations of the WTD figures for February?  These are the ones I am talking about using the SUM() on.  Once that part has been calculated, you can eitherJOIN to the most recent WTD figures (for the Ending Inventory and the Ending Inventory LY) or to the MAX(WeekNumber) within that month (so that you can reproduce the report later on).

Yeah that was just some data I threw together to just show you the example of what I was trying to do. The numbers will not be correct. Plus, I dont have any issues with any of the data except for the Inventory columns. In your last sentence you stated to Join the most recent values? THAT is exactly what I am looking to do just not sure where since this is a cube? OR, maybe I could use my idea of adding the 2 columns with the latest WTD Inventory values for each record. I have attached you the actual reports for WTD, MTD and YTD.

I have to download these reports into excel so I can overwrite the End Inventory(EOH and EOH LY on report) values for MTD and YTD to be the values from the WTD report. That is why I came up with all this in the first place.

Thanks for all your help. I really appreciate it..

Im having trouble uploading my doc I will keep trying

here ya go!!
Reports.doc

What is the query that you use to gather the data for the MTD report?  

The criteria is selected from the cube with the Year, Month and Week values as parameters and in my table in the report I have the Business Unit and Region as my groupings with the other values(Sales, Sales LY, Receipts, Receipts LY, etc..) selected. If I had to query the values it would look something like this.

Select [Bus Unit], Region, SUM([sales $]) as 'Sales', SUM([LY Sales $]) as 'Sales LY', SUM([% Sale Chg to LY]) as 'Sales Change',
SUM([Receipts $]) as 'Receipts', SUM([LY Receipts $]) as 'Receipts LY', SUM([% Receipts Chg to LY]) as 'Receipts Change',
SUM([EOH $]) as 'EOH', SUM([LY EOH $]) as 'EOH LY', SUM([% EOH Chg to LY]) as 'EOH Change',SUM([% Sell Thru U]) as 'Sell Thru',
SUM([LY % Sell Thru U]) as 'Sell Thru LY'
from NordstromData
group by [Bus Unit], Region
order by [Bus Unit], Region

I can give you the MDX query if youd like?

How am I supposed to use this in the cube though? Thats why I wa thinking to add it to the table and use it from the cube.

How about building the cube on a view?  Can you do that?

Failing that, I guess adding the two columns and then executing an update every week would work but I can also see where that is going to complicate the cube's process.

I think all you would need to do is to add an Execute SQL Task with an UPDATE query at the end of your existing SSIS package.  

Yeah I can build the cube on a view and thats actually a good idea. Since I do this every monday I will just update the fields for this week coming up and then change the source to be a view instead of reading the whole table as the source. From what you have seen would it be best to just update the new columns with the latest weeks inventory? Thanks you were a big help..

thanks again!!

oh and by the way I never got what I needed...HAHAHAHA!!! How would I do the update to the latest weeks Inventory?

If you can build it on a View, try building it on the query that you said you couldn't use. ;-)  (Ref: Reponse #34975146)