Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Syntax

Posted on 2011-03-17
8
Medium Priority
?
300 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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