Query join questions

  • 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

  • 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

  • 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?

     

  • 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

  • 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