Solved

SQL Syntax

Posted on 2011-03-17
8
267 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 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