Link to home
Start Free TrialLog in
Avatar of mburk1968
mburk1968Flag for United States of America

asked on

Help with a SQL comparison Query

I have the following two queries below. What I am attempting to do is compare my results from current production to archived data from the week prior. My goal is to compare sales by season OTSWIP and SUM of the std_cost. However when I attempt to join the two queries together my results get exaggerated. I'm not certain what I am doing wrong?

Production Query is as follows. I Receive 23 rows. Results are attached.

SELECT  cs.season ,
        cs.season_name ,
	    SUM(CONVERT(INT, cs.OTSWIP)) AS OTSWIP ,
	    SUM(cs.Ext_std_cost) AS Ext_std_cost
FROM    ( SELECT    c.DIVISION ,
                    c.STYLE ,
                    c.COLOR_CODE ,
                    c.LBL_CODE ,
                    c.std_cost ,
                    c.season ,
                    CASE WHEN c.season IN ( '20121', '20122', '20123', '20124',
                                            '20125', '20131', '20132', '20133',
                                            '20134', '20135', '20141', '20142',
                                            '20143', '20144', '20145', '20151',
                                            '20152', '20153', '20154', '20155',
                                            '20161', '20162', '20163', '20164',
                                            '20165', '20171', '20172', '20173',
                                            '20174', '20175', '20181', '20182',
                                            '20183', '20184', '20185', '20191',
                                            '20192', '20193', '20194', '20195' )
                         THEN LEFT(c.season, LEN(c.season) - 1)
                         ELSE c.season
                    END AS Sea_Year ,
                    CASE WHEN LEFT(Sea.seas_name,
                                   CHARINDEX(' ', Sea.seas_name) - 1) = 'SPRING'
                         THEN 'SPRING'
                         WHEN LEFT(Sea.seas_name,
                                   CHARINDEX(' ', Sea.seas_name) - 1) = 'SUMMER'
                         THEN 'SUMMER'
                         WHEN Sea.seas_name LIKE ( 'FALL 1%' ) THEN 'TRANS'
                         WHEN LEFT(Sea.seas_name,
                                   CHARINDEX(' ', Sea.seas_name) - 1) = 'FALL'
                         THEN 'FALL'
                         WHEN LEFT(Sea.seas_name,
                                   CHARINDEX(' ', Sea.seas_name) - 1) = 'HOLIDAY'
                         THEN 'HOLIDAY'
                         WHEN LEFT(Sea.seas_name,
                                   CHARINDEX(' ', Sea.seas_name) - 1) = 'ANY'
                         THEN 'ANY'
                    END AS season_name ,
                    c.Main_Label ,
                    c.EndLocation ,
                    SUM(CONVERT(INT, c.OTSWIP)) AS OTSWIP ,
                    ( SUM(CONVERT(INT, c.OTSWIP)) * c.std_cost ) AS Ext_std_cost
          FROM      [KLL Cut and Sold OTS Sum - New Rollup] c
                    INNER JOIN zzxseasr Sea ON c.DIVISION = Sea.division
                                               AND c.season = Sea.season
          WHERE     c.DIVISION = 'KLL'
                    AND c.season IN ( '20121', '20122', '20123', '20124',
                                      '20125', '20131', '20132', '20133',
                                      '20134', '20135', '20141', '20142',
                                      '20143', '20144', '20145', '20151',
                                      '20152', '20153', '20154', '20155',
                                      '20161', '20162', '20163', '20164',
                                      '20165', '20171', '20172', '20173',
                                      '20174', '20175', '20181', '20182',
                                      '20183', '20184', '20185', '20191',
                                      '20192', '20193', '20194', '20195',
                                      'ADC', 'ANY' )
          GROUP BY  c.DIVISION ,
                    c.STYLE ,
                    c.COLOR_CODE ,
                    c.LBL_CODE ,
                    c.std_cost ,
                    c.season ,
                    c.Main_Label ,
                    c.EndLocation ,
                    Sea.seas_name
          HAVING    SUM(CONVERT(INT, c.OTSWIP)) > 0
        ) cs
GROUP BY cs.season ,
        cs.season_name

Open in new window


My archived data query. I receive 23 rows which are attached.

USE [KLL_Cust]
SELECT season ,
       season_name ,
	   SUM(CONVERT(INT, p.OTSWIP)) AS Prv_OTSWIP ,
	   SUM(p.Ext_std_cost) AS Prv_Ext_std_cost
FROM (
SELECT  Prv.DIVISION ,

        Prv.STYLE ,
        Prv.COLOR_CODE ,
        Prv.LBL_CODE ,
        Prv.std_cost ,
        Prv.season ,
        CASE WHEN Prv.season IN ( '20121', '20122', '20123', '20124', '20125',
                                '20131', '20132', '20133', '20134', '20135',
                                '20141', '20142', '20143', '20144', '20145',
                                '20151', '20152', '20153', '20154', '20155',
                                '20161', '20162', '20163', '20164', '20165',
                                '20171', '20172', '20173', '20174', '20175',
                                '20181', '20182', '20183', '20184', '20185',
                                '20191', '20192', '20193', '20194', '20195' )
             THEN LEFT(Prv.season, LEN(Prv.season) - 1)
             ELSE Prv.season
        END AS Sea_Year ,
        CASE WHEN LEFT(Sea.seas_name, CHARINDEX(' ', Sea.seas_name) - 1) = 'SPRING'
             THEN 'SPRING'
             WHEN LEFT(Sea.seas_name, CHARINDEX(' ', Sea.seas_name) - 1) = 'SUMMER'
             THEN 'SUMMER'
             WHEN Sea.seas_name LIKE ( 'FALL 1%' ) THEN 'TRANS'
             WHEN LEFT(Sea.seas_name, CHARINDEX(' ', Sea.seas_name) - 1) = 'FALL'
             THEN 'FALL'
             WHEN LEFT(Sea.seas_name, CHARINDEX(' ', Sea.seas_name) - 1) = 'HOLIDAY'
             THEN 'HOLIDAY'
             WHEN LEFT(Sea.seas_name, CHARINDEX(' ', Sea.seas_name) - 1) = 'ANY'
             THEN 'ANY'
        END AS season_name ,
	    Prv.Main_Label ,
        SUM(CONVERT(INT, Prv.OTSWIP)) AS OTSWIP ,
		(SUM(CONVERT(INT, Prv.OTSWIP)) * std_cost) AS Ext_std_cost
FROM    [KLL_Cust].[dbo].[KLLInventoryPosition] Prv
        INNER JOIN [DataKLL].[dbo].[zzxseasr] Sea ON Prv.DIVISION = Sea.division
                                   AND Prv.season = Sea.season
GROUP BY Prv.DIVISION ,
        Prv.STYLE ,
        Prv.COLOR_CODE ,
        Prv.LBL_CODE ,
        Prv.std_cost ,
        Prv.season ,
		Sea.seas_name ,
        Prv.Main_Label ) p
GROUP BY p.season ,
       p.season_name

Open in new window


When I attempt to join these either in a left Join or Right Join my results get exaggerated? I know this is simple however I am struggling to grasp what is wrong.

What I'm looking to do is determine the difference between OTSWIP this week from last and the percentage change between last week and this week in dollars.
Production.xlsx
Archived.xlsx
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

>> When I attempt to join these either in a left Join or Right Join my results get exaggerated?

I hope you meant exaggerated as getting additional records, if so, then we need to ensure that the JOIN condition is missing some key columns..
Lets say you have both these result sets inserted into 2 temp tables named Prod and Archive, then try the below query..
SELECT p.season, p.season_name, p.OTSWIP, p.Ext_std_cost, a.OTSWIP, a.Ext_std_cost -- do all calculations on OTSWIP and Ext_std_cost columns as required..
FROM prod p
FULL JOIN archive a on p.season = a.season and p.season_name = a.season_nam

Open in new window

Avatar of mburk1968

ASKER

Tried a full join. My Production OTSWIP goes to 768. That is wrong. Production OTSWIP is 128 and my Archive OTSWIP should be 138
>> Tried a full join. My Production OTSWIP goes to 768. That is wrong

kindly provide more details as whether the query I've provided brings 768 records, if so, then we can modify the query accordingly..
Also clarify whether you wish to compare based upon season, season_name and OTSWIP, if so, then we might need to put OTSWIP also in the join condition..
Provide your expected result
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
TIP: Instead of using spreadsheet attachments, text tables provide a convenient way to share your sample data:

Production:
+--------+-------------+--------+--------------+
| season | season_name | OTSWIP | Ext_std_cost |
+--------+-------------+--------+--------------+
| 20145  | HOLIDAY     |    128 |    486.34657 |
| 20145  | HOLIDAY     |    134 |    506.77099 |
| 20152  | SUMMER      |    282 |    2397.5757 |
| 20153  | TRANS       |   1250 |  10410.12465 |
| 20153  | TRANS       |   1268 |  10569.88797 |
| 20154  | FALL        |   1315 |   8302.62928 |
| 20155  | HOLIDAY     |    106 |   2374.69303 |
| 20162  | SUMMER      |     26 |    209.61638 |
| 20163  | TRANS       |   4260 |  31698.36658 |
| 20164  | FALL        |   4499 |  73234.14828 |
| 20164  | FALL        |   4505 |  73285.74396 |
| 20165  | HOLIDAY     |    877 |   6854.33514 |
| 20165  | HOLIDAY     |    883 |   6904.54866 |
| 20171  | SPRING      |    134 |   1448.73368 |
| 20171  | SPRING      |    176 |   1736.51684 |
| 20172  | SUMMER      |   2077 |  11041.65994 |
| 20173  | TRANS       |   1962 |   14019.1685 |
| 20173  | TRANS       |   1997 |   14287.9518 |
| 20174  | FALL        |   6190 |  46075.16361 |
| 20174  | FALL        |   6239 |  46516.90581 |
| 20175  | HOLIDAY     |   6100 |  58829.08071 |
| 20175  | HOLIDAY     |   6107 |  58889.71712 |
| 20181  | SPRING      |   7210 |  53374.59972 |
| 20181  | SPRING      |   7216 |   53423.0274 |
| 20182  | SUMMER      |   2111 |  13615.49227 |
| 20183  | TRANS       |  28863 |  230025.1085 |
| 20183  | TRANS       |  28965 |  230939.1536 |
| 20184  | FALL        |  35876 |  263668.6386 |
| 20184  | FALL        |  35894 |  263801.2464 |
| 20185  | HOLIDAY     |  56209 |  455344.5588 |
| 20185  | HOLIDAY     |  56623 |   459021.804 |
| 20191  | SPRING      |  76358 |  552158.6146 |
| 20191  | SPRING      |  78996 |   549125.959 |
| 20192  | SUMMER      |  13708 |   72699.0142 |
| 20192  | SUMMER      |  17524 |  86976.69796 |
| 20193  | TRANS       |  22395 |  110015.7173 |
| 20193  | TRANS       |  22575 |  111531.9176 |
| ANY    | ANY         |   2716 |  24747.86585 |
+--------+-------------+--------+--------------+

Open in new window

Archived:
+--------+-------------+--------+--------------+
| season | season_name | OTSWIP | Ext_std_cost |
+--------+-------------+--------+--------------+
| 20145  | HOLIDAY     |    128 |    486.34657 |
| 20152  | SUMMER      |    282 |    2397.5757 |
| 20153  | TRANS       |   1250 |  10410.12465 |
| 20154  | FALL        |   1315 |   8302.62928 |
| 20155  | HOLIDAY     |    106 |   2374.69303 |
| 20162  | SUMMER      |     26 |    209.61638 |
| 20163  | TRANS       |   4260 |  31698.36658 |
| 20164  | FALL        |   4499 |  73234.14828 |
| 20165  | HOLIDAY     |    877 |   6854.33514 |
| 20171  | SPRING      |    134 |   1448.73368 |
| 20172  | SUMMER      |   2077 |  11041.65994 |
| 20173  | TRANS       |   1962 |   14019.1685 |
| 20174  | FALL        |   6190 |  46075.16361 |
| 20175  | HOLIDAY     |   6100 |  58829.08071 |
| 20181  | SPRING      |   7210 |  53374.59972 |
| 20182  | SUMMER      |   2111 |  13615.49227 |
| 20183  | TRANS       |  28863 |  230025.1085 |
| 20184  | FALL        |  35876 |  263668.6386 |
| 20185  | HOLIDAY     |  56209 |  455344.5588 |
| 20191  | SPRING      |  76358 |  552158.6146 |
| 20192  | SUMMER      |  13708 |   72699.0142 |
| 20193  | TRANS       |  22575 |  111531.9176 |
| ANY    | ANY         |   2716 |  24747.86585 |
+--------+-------------+--------+--------------+

Open in new window

These were created by using this url: https://ozh.github.io/ascii-tables/
Just paste the data into the top text box, voila, the lower text box is the table.
Thank you for the tips. Thank you for the solution.