Solved

SQL Syntax

Posted on 2011-02-22
20
320 Views
Last Modified: 2012-06-27
ok heres what I got...
I have a table that I append to every week with the latest sales data from a website. From this I have to generate a 'Week To Date', 'Month To Date' and 'Year To Date' reports that I merge into one and send it to all the big wigs in my company. I have SSIS packages that I upload my data into my datawarehouse and process the info into a cube from which I report from. Ive run into a few problems but the biggest one is manual work I have to do for a few columns I have to manipulate. The problem is that each week when I download the data, there are 2 columns in which I need to use the latest data for all reports. The data is for the Week I just downloaded but I have to apply this weeks data, for 2 columns to the MTD and YTD reports as well. I have attached a quick example of my report that I have to run every week. Basically I have 2 columns that I made bold(Ending Inventory and Ending Inventory LY). I have to manually overwrite the  Month to date and Year to date report with the 2 column values from the Week report and its killing me. What I was thinking was to add 2 columns to my source table EndInvNEW and EndInvLYNew and run some kind of update every week to update the 2 new columns to the latest weeks values. I hope this makes sense. I hope this makes sense. I think if you look at the spreadsheet attached it may explain more..Thanks in advance for your help experts!!!
ExpExchExample.xls
0
Comment
Question by:johnnyaction
  • 12
  • 8
20 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34956379
Okay, let me see if I can restate your problem:
Every week, you download the latest sales data from a website;
You load that data into a data warehouse;
From this data warehouse, you need to then create a Week To Date, Month To Date, and a Year To Date report (i.e. 3 reports);
The data you download has the Week To Date Ending Inventory and Ending Inventory LY (Last year?)

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.
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34957046
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!!

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34957055
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
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34962960
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

Open in new window

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34964938
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
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34965308
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.
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34965612
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
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34971832
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).
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34973068
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
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34973073
here ya go!!
Reports.doc
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 22

Expert Comment

by:8080_Diver
ID: 34973229
What is the query that you use to gather the data for the MTD report?  

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34974439
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

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34974444
I can give you the MDX query if youd like?
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 34975146
The query you provided did not constrain the output by a month.  I added that facet in the attached.

The query does get a bit complicated in order to extract the last week of the month and include it as the last two columns of the output.  This is because you have to have the query that does the summations left as is (except for the additional GROUP BY parameter for the [Month]).  Then you have to create a subquery that gets the last week of the moth's inventory data by self joining with a query that finds the last week of the month.

Finally, joining the summation query with the query to extract the inventory answers, you can get the overall answer.
SELECT S.[Bus Unit]
  ,S.Region
  ,S.[Sales]
  ,S.Sales LY]
  ,S.[Sales Change]
  ,S.Receipts
  ,S.[Receipts LY]
  ,S.[Receipts Change]
  ,S.EOH
  ,S.EOH LY]
  ,S.[EOH Change]
  ,S.[Sell Thru]
  ,S.[Sell Thru LY]
  ,W.End_Inventory
  ,W.End_Inventory_LY
FROM
    (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'
      ,[Month]
    FROM NordstromData
    WHERE [Month] = @MonthParameter
    GROUP BY [Bus Unit]
      ,Region
      ,[Month]
    ) S
INNER JOIN
    (SELECT N1[Bus Unit]
      ,N1Region
      ,N1.End_Inventory
      ,N1.End_Inventory_LY
      ,N1.[Month]
    FROM
    FROM NordstromData N1
    INNER JOIN
        (SELECT [Bus Unit]
          ,Region
          ,[Month]
          ,MAX([Week])
        FROM NordstromData
        WHERE [Month] = @MonthParameter
        GROUP BY [Bus Unit]
          ,Region
          ,[Month]
        ) N2
    ON  N1.[Bus Unit] = N2.[Bus Unit]
        AND N1.Region = N2.Region N1.[Month] = N2.[Month]
    ) W
ON  S.[Bus Unit] = W.[Bus Unit] S.Region = W.Region S.[Month] = W.[Month]
ORDER BY [Bus Unit]
  ,Region

Open in new window

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34975727
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.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34981099
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.  
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34982077
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..
0
 
LVL 1

Author Closing Comment

by:johnnyaction
ID: 34982088
thanks again!!
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34982182
oh and by the way I never got what I needed...HAHAHAHA!!! How would I do the update to the latest weeks Inventory?
0
 
LVL 22

Expert Comment

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now