Addition of values from two fields
-
I have an inventory usage table that holds values for different locations and periods. The values are calculated by the ERP so I cannot change the SQL values, but in certain instances, I want to combine the values for two locations into one location and zero the usage for the original location. This would be queried into Excel, so I don't want to change the SQL table values, just export and combine at the same time. In this case, I want to combine the usage from location 9999 into that of location 1. Just looking for a little guidance getting started. Thanks.
Sample data:
create table temp_usage (location_id decimal(10,0),item_id varchar(40), period decimal(2,0), year_for_period decimal (4,0), usage decimal (10,0))
insert into temp_usage (location_id, item_id, period, year_for_period, usage)
SELECT 1,'4rea90-metv',5,2020,400 UNION ALL
SELECT 1,'4rea90-metv',6,2020,200 UNION ALL
SELECT 1,'4rea90-metv',7,2020,300 UNION ALL
SELECT 1,'4rea90-metv',8,2020,150 UNION ALL
SELECT 1,'4rea90-metv',9,2020,200 UNION ALL
SELECT 30,'4rea90-metv',5,2020,195 UNION ALL
SELECT 30,'4rea90-metv',6,2020,205 UNION ALL
SELECT 30,'4rea90-metv',7,2020,350 UNION ALL
SELECT 30,'4rea90-metv',8,2020,200 UNION ALL
SELECT 30,'4rea90-metv',9,2020,100 UNION ALL
SELECT 9999,'4rea90-metv',5,2020,20 UNION ALL
SELECT 9999,'4rea90-metv',6,2020,25 UNION ALL
SELECT 9999,'4rea90-metv',7,2020,10 UNION ALL
SELECT 9999,'4rea90-metv',8,2020,5 UNION ALL
SELECT 9999,'4rea90-metv',9,2020,20Expected results:
-
There's probably a more elegant way of doing this, but this seems to work (I fixed up your data types too)
DROP TABLE IF EXISTS #temp_usage;
CREATE TABLE #temp_usage
(
location_id INT
,item_id VARCHAR(40)
,PERIOD tinyint
,year_for_period SMALLINT
,usage INT
);
INSERT #temp_usage
(
location_id
,item_id
,period
,year_for_period
,usage
)
VALUES
(1, '4rea90-metv', 5, 2020, 400)
,(1, '4rea90-metv', 6, 2020, 200)
,(1, '4rea90-metv', 7, 2020, 300)
,(1, '4rea90-metv', 8, 2020, 150)
,(1, '4rea90-metv', 9, 2020, 200)
,(30, '4rea90-metv', 5, 2020, 195)
,(30, '4rea90-metv', 6, 2020, 205)
,(30, '4rea90-metv', 7, 2020, 350)
,(30, '4rea90-metv', 8, 2020, 200)
,(30, '4rea90-metv', 9, 2020, 100)
,(9999, '4rea90-metv', 5, 2020, 20)
,(9999, '4rea90-metv', 6, 2020, 25)
,(9999, '4rea90-metv', 7, 2020, 10)
,(9999, '4rea90-metv', 8, 2020, 5)
,(9999, '4rea90-metv', 9, 2020, 20);
DECLARE @MasterId INT = 1;
DECLARE @CombineId INT = 9999;
WITH combined
AS (SELECT tu.item_id
,tu.period
,tu.year_for_period
,usage = SUM(tu.usage)
FROM #temp_usage tu
WHERE tu.location_id = @MasterId
OR tu.location_id = @CombineId
GROUP BY tu.item_id
,tu.period
,tu.year_for_period)
SELECT location_id = @MasterId
,combined.item_id
,combined.period
,combined.year_for_period
,combined.usage
FROM combined
UNION ALL
SELECT tu.location_id
,tu.item_id
,tu.period
,tu.year_for_period
,usage = IIF(tu.location_id = @CombineId, 0, tu.usage)
FROM #temp_usage tu
WHERE tu.location_id <> @MasterId;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here's a version without requiring UNION:
SELECT tu.location_id
,tu.item_id
,tu.period
,tu.year_for_period
,usage = IIF(tu.location_id = @CombineId
,0
,SUM(tu.usage) OVER (PARTITION BY loc.CombinedId, tu.item_id, tu.period, tu.year_for_period))
FROM #temp_usage tu
CROSS APPLY
(
SELECT CombinedId = IIF(tu.location_id = @CombineId, @MasterId, tu.location_id)
) loc
ORDER BY tu.location_id
,tu.period;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I used a table for the location_id(s) to be combined, so that it would be easy to do multiple combinings. For example, if you uncomment the (1, 30), you'll see that the 30 locations also get combined in location 1.
IF OBJECT_ID('tempdb.dbo.#combine_locations') IS NOT NULL
DROP TABLE #combine_locations;
CREATE TABLE #combine_locations
(
location_id_combined decimal(10, 0) NOT NULL,
location_id_to_combine decimal(10, 0) NOT NULL,
PRIMARY KEY ( location_id_to_combine )
)
INSERT INTO #combine_locations VALUES
(1, 9999)/*,(1, 30)--*/
SELECT
ca1.location_id,
ca1.item_id, ca1.period, ca1.year_for_period,
SUM(ca1.usage) AS usage
FROM temp_usage tu
LEFT OUTER JOIN #combine_locations cl ON tu.location_id = cl.location_id_to_combine
CROSS APPLY (
SELECT
CASE WHEN cl.location_id_to_combine = tu.location_id
THEN cl.location_id_combined ELSE tu.location_id END AS location_id,
tu.item_id, tu.period, tu.year_for_period,
tu.usage
UNION ALL
SELECT tu.location_id, tu.item_id, tu.period, tu.year_for_period, 0 AS usage
WHERE cl.location_id_to_combine IS NOT NULL
) AS ca1
--/*
GROUP BY
ca1.location_id,
ca1.item_id, ca1.period, ca1.year_for_period
--*/
ORDER BY
ca1.location_id, ca1.item_id, ca1.period, ca1.year_for_periodSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Thanks guys. Looks like I've got to do some searching and brush up on CROSS APPLY. I'll test these out.
-
I need to add a little twist to the data. When the item is considered stockable at loc9999, I need it to have it's own usage. When it's not stockable there, I need the usage to roll up into loc1. There is a Y/N flag for stockable. I though I could do it with the IIF but I just can't get there. Let's forget about the other location and just look at either 1 or 9999. Here's updated info for the temp table.
DROP TABLE IF EXISTS #temp_usage;
CREATE TABLE #temp_usage
(
location_id INT
,item_id VARCHAR(40)
,PERIOD tinyint
,year_for_period SMALLINT
,usage INT
,stock varchar(1)
);
INSERT #temp_usage
(
location_id
,item_id
,period
,year_for_period
,usage
)
VALUES
(1, '4rea90-metv', 5, 2020, 36,'Y')
,(1, '4rea90-metv', 6, 2020, 63,'Y')
,(1, '4rea90-metv', 7, 2020, 138,'Y')
,(1, '4rea90-metv', 8, 2020, 22,'Y')
,(1, '4rea90-metv', 9, 2020, 36,'Y')
,(1, 'USA440X35+5RD-CAPC', 5, 2020, 0,'Y')
,(1, 'USA440X35+5RD-CAPC', 6, 2020, 0,'Y')
,(1, 'USA440X35+5RD-CAPC', 7, 2020, 18,'Y')
,(1, 'USA440X35+5RD-CAPC', 8, 2020, 0,'Y')
,(1, 'USA440X35+5RD-CAPC', 9, 2020, 0,'Y')
,(9999, '4rea90-metv', 5, 2020, 20,'N')
,(9999, '4rea90-metv', 6, 2020, 24,'N')
,(9999, '4rea90-metv', 7, 2020, 0,'N')
,(9999, '4rea90-metv', 8, 2020, 36,'N')
,(9999, '4rea90-metv', 9, 2020, 44,'N')
,(9999, 'USA440X35+5RD-CAPC', 5, 2020, 5,'Y')
,(9999, 'USA440X35+5RD-CAPC', 6, 2020, 30,'Y')
,(9999, 'USA440X35+5RD-CAPC', 7, 2020, 0,'Y')
,(9999, 'USA440X35+5RD-CAPC', 8, 2020, 22,'Y')
,(9999, 'USA440X35+5RD-CAPC', 9, 2020, 0,'Y');I thought I could use a variation of Phil's IIF and get something like this, but It wants to roll up for one location. I need it to key off of the stock flag and only roll to loc1 if loc9999 is not check Y on the stock flag.
-
Not 100% sure about this, but please try it:
DROP TABLE IF EXISTS #temp_usage;
CREATE TABLE #temp_usage
(
location_id INT
,item_id VARCHAR(40)
,PERIOD tinyint
,year_for_period SMALLINT
,usage INT
,stock CHAR(1)
);
INSERT #temp_usage
(
location_id
,item_id
,period
,year_for_period
,usage
,stock
)
VALUES
(1, '4rea90-metv', 5, 2020, 36, 'Y')
,(1, '4rea90-metv', 6, 2020, 63, 'Y')
,(1, '4rea90-metv', 7, 2020, 138, 'Y')
,(1, '4rea90-metv', 8, 2020, 22, 'Y')
,(1, '4rea90-metv', 9, 2020, 36, 'Y')
,(1, 'USA440X35+5RD-CAPC', 5, 2020, 0, 'Y')
,(1, 'USA440X35+5RD-CAPC', 6, 2020, 0, 'Y')
,(1, 'USA440X35+5RD-CAPC', 7, 2020, 18, 'Y')
,(1, 'USA440X35+5RD-CAPC', 8, 2020, 0, 'Y')
,(1, 'USA440X35+5RD-CAPC', 9, 2020, 0, 'Y')
,(9999, '4rea90-metv', 5, 2020, 20, 'N')
,(9999, '4rea90-metv', 6, 2020, 24, 'N')
,(9999, '4rea90-metv', 7, 2020, 0, 'N')
,(9999, '4rea90-metv', 8, 2020, 36, 'N')
,(9999, '4rea90-metv', 9, 2020, 44, 'N')
,(9999, 'USA440X35+5RD-CAPC', 5, 2020, 5, 'Y')
,(9999, 'USA440X35+5RD-CAPC', 6, 2020, 30, 'Y')
,(9999, 'USA440X35+5RD-CAPC', 7, 2020, 0, 'Y')
,(9999, 'USA440X35+5RD-CAPC', 8, 2020, 22, 'Y')
,(9999, 'USA440X35+5RD-CAPC', 9, 2020, 0, 'Y');
DECLARE @MasterId INT = 1;
DECLARE @CombineId INT = 9999;
SELECT *
FROM #temp_usage tu;
SELECT tu.location_id
,tu.item_id
,tu.period
,tu.year_for_period
,usage = IIF(
tu.location_id = @CombineId
AND tu.stock = 'N'
,0
,SUM(tu.usage) OVER (PARTITION BY loc.CombinedId, tu.item_id, tu.period, tu.year_for_period))
,tu.stock
FROM #temp_usage tu
CROSS APPLY
(
SELECT CombinedId = IIF(tu.location_id = @CombineId AND tu.stock = 'N', @MasterId, tu.location_id)
) loc
ORDER BY tu.location_id
,tu.period;Note that the INSERT statement you provided contained the wrong number of columns.
And I have changed VARCHAR(1) to CHAR(1) in line with good practice.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks Phil. This looks like the results I expected. Now to incorporate it into my main query. I appreciate the guidance on char vs varchar. I did not know, but I did some more research and see the difference and performance issues between the two.
On a separate note, do you prefer temp tables or CTEs when doing large queries? I learned with CTEs, but have been seeing a lot more use of temp tables. Is there a trade off in performance? Should I be using temp tables instead of CTEs in my larger queries? Thanks for all the help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply