August 10, 2006 at 2:27 pm
Hi experts,
I have the following query to return Daily comp sales, Weekly comp sales, and Period comp sales data for each store in a certain district. This query works fine.
Now, the report owner request to add a returning result : The total for the district.
For example: The original result
Location_2 Location_1 Daily WTD PTD
09486 D 1069 2148.05 4706.32 16257.52
01217 D 1069 2211.45 5211.45 16918.52
05983 D 1069 2173.71 4962.62 16834.89
01397 D 1069 2183.59 5182.63 17301.14
The new result should be like
Location_2 Location_1 Daily WTD PTD
D 1069 D 1069 8716.8 20063.02 67312.07
09486 D 1069 2148.05 4706.32 16257.52
01217 D 1069 2211.45 5211.45 16918.52
05983 D 1069 2173.71 4962.62 16834.89
01397 D 1069 2183.59 5182.63 17301.14
How to change the following query to achieve that??? PLEASE HELP!
SELECT D.Location_2,D.Location_1,
CASE WHEN D.PY = 0 THEN NULL ELSE Convert (Decimal(9,2),((D.TY - D.PY) / D.PY)*100) END Daily,
CASE WHEN W.PY = 0 THEN NULL ELSE Convert (Decimal(9,2),((W.TY - W.PY) / W.PY)*100) END WTD,
CASE WHEN P.PY = 0 THEN NULL ELSE Convert (Decimal(9,2),((P.TY - P.PY) / P.PY)*100) END PTD
FROM ( SELECT Location_1,Location_2,
SUM(CASE WHEN Measure_Name = 'A CompSales' THEN Measure_Value ELSE 0 END) TY,
SUM(CASE WHEN Measure_Name = 'PY WTD Net Sales' THEN Measure_Value ELSE 0 END) PY
FROM micros.Daily_Summary_table GROUP BY Location_1,Location_2
) D INNER JOIN
( SELECT Location_1,Location_2,
SUM(CASE WHEN Measure_Name = 'A CompSales' THEN Measure_Value ELSE 0 END) TY,
SUM(CASE WHEN Measure_Name = 'PY WTD Net Sales' THEN Measure_Value ELSE 0 END) PY
FROM micros.Weekly_Report_Table GROUP BY Location_1,Location_2
) W on D.Location_2 = W.Location_2 INNER JOIN
( SELECT Location_1,Location_2,
SUM(CASE WHEN Measure_Name = 'A CompSales' THEN Measure_Value ELSE 0 END) TY,
SUM(CASE WHEN Measure_Name = 'PY WTD Net Sales' THEN Measure_Value ELSE 0 END) PY
FROM micros.PTD_Summary_Tble GROUP BY Location_1, Location_2
) P on D.Location_2 = P.Location_2
where D.Location_1= (SELECT Align_lvl_Long FROM micros.Mgmt_Personnel_Table
WHERE fullname=’allen.m’))
August 10, 2006 at 2:34 pm
you want to use a sub select in this case, if you want the summary data from the select statement you made;
something like this should give you the idea:
SELECT Location_1 as Location_2,Location_1,SUM(Daily) as Daily,sum(WTD) as WTD,SUM(PTD) as PTD
from ([big sql you posted goes here tween the parenthesis] ) GROUP BY Location_1
UNION
[big sql you posted goes here ]
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply