mburk1968
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.
My archived data query. I receive 23 rows which are attached.
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
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
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
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
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..
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TIP: Instead of using spreadsheet attachments, text tables provide a convenient way to share your sample data:
Production:
Just paste the data into the top text box, voila, the lower text box is the table.
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 |
+--------+-------------+--------+--------------+
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 |
+--------+-------------+--------+--------------+
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.
ASKER
Thank you for the tips. Thank you for the solution.
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..
Open in new window