johnnyaction
asked on
SQL Syntax
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????
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????
ASKER
I actually tried that but it didnt work...I will try again though
ASKER
ok when I did that I am getting the values from NDDataWeekly but they are doubled?
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,
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,w
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
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,
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,w
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
ASKER
I appreciate your help but Im a little confused on how to run this query from what you wrote?
ASKER
How am I referencing the weekly query? Should I create a view or something?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
a.[store id] = b.[store id] and a.region = b.region and a.[Bus Unit] = b.[Bus Unit]
?
Ss