Solved

SQL Syntax

Posted on 2011-03-17
8
257 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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

16 Experts available now in Live!

Get 1:1 Help Now