?
Solved

SQL Syntax

Posted on 2011-03-17
8
Medium Priority
?
291 Views
Last Modified: 2012-05-11
Ok Im having an issue...
I have a table that has sales data from different stores and regions that I download every week with the latest weeks data and I need to show the values for WTD, MTD and YTD summed. For each report I need to show the values grouped by Bus Unit and Region with all the dollar values summed for Week Month and Year grouped. One of the problems Im having is that I need to have 3 columns(All EOH columns, the last 3) with the "Weeks" data regardless of the report. so for instance..
WTD Report
Bus Unit            Region      EOH                  EOH LY                    EOH Change
FULL LINE           CT              1068126.53          1446077.99      -4.97
FULL LINE     HAWAII       44390                    71590                       -0.37
FULL LINE            LA             581614.68              724635.7                       -2.42

MTD (I need the last 3 columns to be from the WTD data)
Bus Unit           Region             EOH                          EOH LY                EOH Change
FULL LINE             CT           21318372.58      27481436.2      -77.15
FULL LINE             HAWAII      923357.98      1675513.45      -8.61
FULL LINE        LA                       11592798.98      13672902.39      -28.36

So what I tried to do was when I download the last weeks data I truncate a seperate table and insert the weeks values for each store into a seperate table(called NDDataWeekly). The table that I append to every week is called NDData. I cant seem to get the weeks values, for these 3 columns, when I try to run the Month and Year reports though.

Heres what I tried for the Month report:
SELECT a.[Bus Unit],a.[Region],
sum(a.[EOH $]) 'EOH',sum(a.[LY EOH $]) 'EOH LY',sum(a.[% EOH Chg to LY]) 'EOH Change',
--THIS IS WHERE I TRY TO GRAB THE WEEKS VALUES from another table
sum(b.[EOH]) 'EOH',sum(b.[EOH LY]) 'EOH LY',sum(b.[EOH Change]) 'EOH Change'
FROM [NDData] a
INNER JOIN [NDDataWeekly] b on a.[Store ID]= b.[store id]  
WHERE a.[MONTH] =3 AND a.[Week] =10  
GROUP BY a.[Bus Unit],a.[Region]
  ORDER BY [Bus Unit], region
 
Anybody got any idea where Im messing up????
0
Comment
Question by:johnnyaction
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 35157772
Shouldn't your join be something like ...

a.[store id] = b.[store id] and a.region = b.region and a.[Bus Unit] = b.[Bus Unit]

?

Ss
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 35158250
I actually tried that but it didnt work...I will try again though
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 35158294
ok when I did that I am getting the values from NDDataWeekly but they are doubled?
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35159897
you need 2 subqueries so that you can join the data in a 1:1 relationship
otherwise you will always end up "double" counting some of the figures as you suffer
from a 1:M or M:M relationship....


so create weeklyquery

select storeid,busunit,...,month,week,
sum(b.[EOH]) 'EOH',sum(b.[EOH LY]) 'EOH LY',sum(b.[EOH Change]) 'EOH Change'
from
[NDDataWeekly]  as b
group by storeid,busunit,...month,week


then you final query becomes


SELECT a.[Bus Unit],a.[Region],
a. 'EOH', a.'EOH LY',a. 'EOH Change',

b.[EOH],b.[EOH LY],b.[EOH Change]
FROM [basequery a
INNER JOIN weeklyquery b on a.[Store ID]= b.[store id]
and a.busunit=b.busunit
...  
WHERE a.[MONTH] =3 AND a.[Week] =10  
GROUP BY a.[Bus Unit],a.[Region]
  ORDER BY a. [Bus Unit], a.region
 
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 35160133
I appreciate your help but Im a little confused on how to run this query from what you wrote?
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 35160136
How am I referencing the weekly query? Should I create a view or something?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 35160778
you reference the "subqueries" as if they where tables in the main query...

just use the query name...
0
 
LVL 1

Author Closing Comment

by:johnnyaction
ID: 35190015
thanks
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

801 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