CODEWITHSUNDEEP
sql-serverdynamicsql-update
J. Doe
J. Doe

Reputation: 15

SQL update table loop

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.

Upvotes: 1

Views: 215

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

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

Upvotes: 1

Related Questions