August 8, 2006 at 4:27 pm
Hi,
I have 3 tables: Daily_Summary_Table, Weekly_Report_Table, and PTD_Summary_Tble. They all have same table structures. They're loaded same datas except Measure_Value column.
The following query works fine for join the 3 tables together
SELECT D.Location_2, D.Location_1, D.Measure_Name, D.Measure_Value, W.Measure_Value AS WTD_Value, P.Measure_Value AS PTD_Value
FROM micros.Daily_Summary_Table D INNER JOIN
micros.Weekly_Report_Table W ON D.Location_2 = W.Location_2 AND D.Measure_Name = W.Measure_Name INNER JOIN
micros.PTD_Summary_Tble P ON D.Location_2 = P.Location_2 AND D.Measure_Name = P.Measure_Name
Now, I need to have a report for comp sales which takes the percentage of Measure_Name='A CompSales' and Measure_Name='PY WTD Net Sales'. The formula = ('A CompSales' -'PY WTD Net Sales')/'PY WTD Net Sales'. And the report will contains 3 tables' values.
Every table has 4 columns : Location_1, Location_2, Measure_Name, Measure_value
I tried to join 2 tables but it doesn't work (the values are nulls). Please point me out where did I do wrong!
-------------------------------------------
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_Value,
CASE WHEN W.PY = 0 THEN NULL ELSE Convert(Decimal(9,2),((W.TY - W.PY) / W.PY)*100) END WTD_Value
FROM (
SELECT Location_1,
Location_2,
Measure_Name,
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,Measure_Name
) D INNER JOIN
(
SELECT Location_1,
Location_2,
Measure_Name,
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,Measure_Name
) W on D.Location_2 = W.Location_2 AND D.Measure_Name = W.Measure_Name
August 9, 2006 at 4:15 am
What does your data look like if you just select D.PY and W.PY instead of calculating the percentage? You say in your calculation that if either is 0, then it should be set to NULL, and if anything in there is null, then the whole result becomes null.
Could it be something with your data/numbers that messes it up? (ie getting zeroes where not expected to)
/Kenneth
August 9, 2006 at 7:28 am
The data is all there, before I joined the 2 tables, I ran the query and the result is correct:
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_Value
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
---------------------
Any suggestions?
August 10, 2006 at 3:35 am
Can you provide a sample script of the tables and some sample data that reproduces your problem with the query? It's pretty hard to just guess without the 'hard facts' to test upon.
/Kenneth
August 10, 2006 at 7:51 am
Thank you. I've figured it out. I shouldn't put measure_name in the join.
here's my finals.
SELECT CASE WHEN LEFT(D.Location_2, 1) = 's' THEN substring(D.Location_2, 5, 5) WHEN LEFT(D.Location_2, 1) = 'D' THEN LEFT(D.Location_2, 1)+ substring(D.Location_2, 14, 2)
WHEN LEFT(D.Location_2, 1)= 'A' THEN CASE WHEN D.Location_2 = 'A Chicago North' THEN 'CN' WHEN D.Location_2 = 'A Chicago South' THEN 'CS' WHEN D.Location_2 = 'A Chicago West'
THEN 'CW' WHEN D.Location_2 = 'A Southern Illiana' THEN 'SI' WHEN D.Location_2 = 'A Charlotte' THEN 'CA' WHEN D.Location_2 = 'A Raleigh' THEN 'RA' WHEN
D.Location_2 = 'A Wisconsin' THEN 'WI' END END as 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_Value,
CASE WHEN W.PY = 0 THEN NULL ELSE Convert(Decimal(9,2),((W.TY - W.PY) / W.PY)*100) END WTD_Value,
CASE WHEN P.PY = 0 THEN NULL ELSE Convert(Decimal(9,2),((P.TY - P.PY) / P.PY)*100) END PTD_Value
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
where Measure_Name in ('A CompSales','PY WTD Net Sales')
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply