SQL update table loop

Question

I have this following query that gets me a small result set

SELECT 
    LOC, PLAN, FiscalYear, FiscalPeriod, SALES 
FROM
    #CurrentPrd PrdAg
WHERE 
    NOT EXISTS (SELECT AGE.ECPLAN 
                FROM ECPG_BAK AGE 
                WHERE PrdAg.LOC = AGE.STORE 
                  AND PrdAg.PLAN = AGE.PLAN 
                  AND PrdAg.FiscalYear = AGE.FiscalYear 
                  AND PrdAg.FiscalPeriod = AGE.FiscalPeriod)

The result set looks like this:

LOC PLAN    FiscalYear  FiscalPeriod    SALES
---------------------------------------------------
 5  6       2031        5               -0.206232
12  6       2031        5                5.243052
12  8       2020        4                1.699716
12  8       2020        5                1.699716
14  6       2031        5                0.299972
19  6       2031        5                1.549812
19  8       2020        5               20.114116
33  6       2031        5                2.159767
33  8       2020        5               23.796883
34  6       2031        5                1.142360
34  8       2020        5                9.348583
................................................

Then I have this other query that gets me a number that I need to add to the SALES column. For example, the query below, I used fixed loc and plan to come up with a number:

select 
    (select SALES 
     from #TOT 
     where loc = 12 and PLAN = 6) - (select sum(sales) 
                                     from #CurrentPrd 
                                     where store = 12 and PLAN = 6) as Comp

Let's assume this query above gets me 10, then I need to add it to line 2 of the result set above, making it

LOC PLAN    FiscalYear  FiscalPeriod    SALES
----------------------------------------------
12  6       2031        5               15.243052

My goal is to make it somewhat dynamic and do the whole process in a simple way, so for each LOC and PLAN combination, I would plug those values into the second select to retrieve the correct number to add to SALES, then update #CurrentPrd. Writing the new number to a new temp table is also an option.

I hope I was able to explain what I'm trying to do. Any help would be appreciated.

Thanks.

I'm unsure why you need something dynamic if it's completely static... e.g. Can't you just use a subquery in your select statement like SELECT LOC, PLAN, FiscalYear, FiscalPeriod, SALES + (SELECT Sales FROM #TOT WHERE LOC = PrdAg.LOC AND PLAN = PrdAg.PLAN) - (SELECT SUM(Sales) FROM #CurrentPrd WHERE Store = PrdAg.LOC AND PLAN = PrdAg.PLAN) FROM #CurrentPrd AS PrdAg ...? — ZLK
– ZLK, Commented Jul 18, 2017 at 22:01

Jason A. Long · Accepted Answer · 2017-07-18 22:36:23Z

1

Without any actual test data, it's hard to say for sure but I think something like the following should work for you...

SELECT 
    PrdAg.LOC, 
    PrdAg.[PLAN], 
    PrdAg.FiscalYear, 
    PrdAg.FiscalPeriod, 
    SALES = PrdAg.SALES + (tx.SALES - cpx.SALES)
FROM 
    #CurrentPrd PrdAg
    CROSS APPLY (SELECT TOP 1 T.SALES FROM #TOT T WHERE PrdAg.LOC = T.LOC AND PrdAg.[PLAN] = t.[PLAN]) tx
    CROSS APPLY (SELECT SALES = SUM(CP.SALES) FROM #CurrentPrd CP WHERE PrdAg.LOC = CP.LOC AND PrdAg.[PLAN] = CP.[PLAN]) cpx
WHERE 
    NOT EXISTS (
            SELECT 1 
            FROM 
                ECPG_BAK AGE 
            WHERE 
                PrdAg.LOC = AGE.STORE 
                AND PrdAg.[PLAN] = AGE.[PLAN] 
                AND PrdAg.FiscalYear = AGE.FiscalYear 
                AND PrdAg.FiscalPeriod = AGE.FiscalPeriod
            );

answered Jul 18, 2017 at 22:36

Jason A. Long

4,4421 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J. Doe Over a year ago

This is perfect. Thanks!

Jason A. Long Over a year ago

Glad to help. :)

Collectives™ on Stack Overflow

SQL update table loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related