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
Solved

SQL Syntax

Posted on 2011-03-17
8
273 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

840 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