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,20

    Expected results:

    Pic1

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

    • This reply was modified 3 years, 4 months ago by  jcobb 20350.
  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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