0

I have created a while loop to check if my derived value is less than zero then enter into while loop, increment my Period ID to +1, pull the values from that period and run the logic again.

But my T SQL code is running forever with this logic with the 26000 records which is not a huge data.

Below is my code logic

SELECT @lclcurrentperiod = PERIOD_ID,
       @snop_item = FGA_ID,
       @lclfullfill_center = fulfillment_center,
       @lcldepo_wise_sawtd = DEPOT_WISE_SAWTD,
       @lclDemandType = DEMAND_TYPE,
       @lclDepo_wise_final = DEPOT_WISE_FINAL,
       @lcldepo_wise_forecast = DEPOT_WISE_FORECAST
FROM temp_calc (NOLOCK)
WHERE PERIOd_id IN (SELECT MIN(period_id)FROM temp_calc WHERE depot_wise_forecast > 0)
  AND FGA_ID = @fga_id
  AND fulfillment_center = @lclFulfillmentCenter
  AND DEMAND_TYPE = 'TRANSACTIONAL';

SET @net_offset_forecast = @lclDepo_wise_forecast - @forecast_consumed_by_sales;

INSERT INTO NET_OFFSET_FORECAST (PERIOD_ID,
                                 FGA_ID,
                                 FULFILLMENT_CENTER,
                                 DEMAND_TYPE,
                                 LEGACY_SSC,
                                 DEPOT_WISE_FORECAST,
                                 CUM_FORECAST,
                                 DRAFT_NET_FORECAST,
                                 FORECAST_TO_BE_CONSUMED,
                                 NET_OFFSET_FORECAST)
VALUES (@lclCurrentPeriod, @snop_item, @lclfullfill_center, @lclDemandType, @lclLegacySSC, @lclDepo_wise_forecast, @cum_forecast, @draft_net_forecast, @forecast_consumed_by_sales, @net_offset_forecast);

WHILE (@net_offset_forecast < 0)
BEGIN
    SET @lclcurrentperiod = @lclcurrentperiod + 1;
    SET @forecast_consumed_by_sales = ABS(@net_offset_forecast);

    SELECT @lclcurrentperiod = PERIOD_ID,
           @fga_id = FGA_ID,
           @lclFulfillmentCenter = FULFILLMENT_CENTER,
           @lclDepo_wise_forecast = DEPOT_WISE_FORECAST,
           @lclDemandType = DEMAND_TYPE
    FROM (SELECT *,
                 ROW_NUMBER() OVER (ORDER BY PERIOD_ID) AS RN
          FROM TEMP_CALC
          WHERE DEPOT_WISE_FORECAST > 0
            AND FGA_ID = @fga_id
            AND fulfillment_center = @lclFulfillmentCenter
            AND DEMAND_TYPE = 'TRANSACTIONAL') t
    WHERE RN = 1;

    SET @net_offset_forecast = @lcldepo_wise_forecast - @forecast_consumed_by_sales;

    INSERT INTO NET_OFFSET_FORECAST (PERIOD_ID,
                                     FGA_ID,
                                     FULFILLMENT_CENTER,
                                     DEMAND_TYPE,
                                     LEGACY_SSC,
                                     DEPOT_WISE_FORECAST,
                                     CUM_FORECAST,
                                     DRAFT_NET_FORECAST,
                                     FORECAST_TO_BE_CONSUMED,
                                     NET_OFFSET_FORECAST)
    VALUES (@lclCurrentPeriod, @snop_item, @lclfullfill_center, @lclDemandType, @lclLegacySSC, @lclDepo_wise_forecast, @cum_forecast, @draft_net_forecast, @forecast_consumed_by_sales, @net_offset_forecast);
END;

After deriving the net offset forecast it is going into while loop when the net offset forecast is less than zero and performing actions there and not coming up out with the data.

Need to improve the performance of the code to finish the loop and continue processing other data.

Thom A
  • 88,727
  • 11
  • 45
  • 75
VivekRaj Merugu
  • 9
  • 2
  • 2
    As a rule of thumb, you don't want to use loops in SQL. instead of inserting records one by one into the `NET_OFFSET_FORECAST` table, use `Insert...Select` to insert all the rows that match your criteria in one single batch. – Zohar Peled Sep 02 '19 at 07:19
  • Hi Zohar...I didnt get you. How do i insert all rows at once without processing my criteria. Can you please give me an example – VivekRaj Merugu Sep 02 '19 at 08:18
  • 4
    No. To give you an example would require me to read and understand your code, and that's a lot to ask without at least proper sample data and expected results. If you could [edit] your question to include a [mcve] that contains sample data as DDL+DML and desired results (Please note: Minimal is important) then perhaps I could provide an answer to your question. For more information, read the first three paragraphs of the [T-SQL tag info.](https://stackoverflow.com/tags/tsql/info) – Zohar Peled Sep 02 '19 at 08:28
  • Looks like the "order fulfillment" pattern. Unforrtunately I can't find the correct search terms to find an example (this has been done many times before of course) – Nick.Mc Sep 02 '19 at 10:10
  • i.e. like this.... equally difficult to understand without sample data but same pattern https://stackoverflow.com/questions/39136227/how-to-show-order-fulfilment-in-a-sql-server-2008-query – Nick.Mc Sep 02 '19 at 10:33
  • @VivekRajMerugu . . . I think you should ask a new question with sample data and desired results. I would suggest that you simplify the query as well, just to the columns and tables that you actually need. – Gordon Linoff Sep 02 '19 at 12:11

0 Answers0