Problem with Logic for new column

  • Hi guys,

    I have the following table

    DDL/DML

    CREATE TABLE #DailyOnHandForecast (Id INT,
    ItemNo_ NVARCHAR(24),
    OH_Qty INT,
    OH_CONT Decimal (38,20),
    OH_$ Decimal (38,20),
    [Date] Date,
    Arrival_Date Date,
    [Forecast Daily Qty Sold] Decimal(38,20),
    [Forecast Daily COG] Decimal(38,20),
    [Forecast Daily Sales] Decimal(38,20),
    [Forecast Daily Container Sold] Decimal(38,20),
    [DayOOS] INT
    )

    INSERT INTO #DailyOnHandForecast
    VALUES (387909,50363,29,0.08065,3042.05418,'2021-02-26',NULL,2.333,246.222,547,0.006489,NULL),
    (387910,50363,26,0.074161,2797.2912,'2021-02-27',NULL,2.333,246.222,547,0.006489,NULL),
    (387911,50363,24,0.067672,2552.52822,'2021-02-28',NULL,2.333,246.222,547,0.006489,NULL),
    (387912,50363,22,0.061183,2307.76524,'2021-03-01',NULL,2.333,246.222,547,0.006489,NULL),
    (387913,50363,19,0.054694,2063.00226,'2021-03-02',NULL,2.333,246.222,547,0.006489,NULL),
    (387914,50363,17,0.048205,1818.23928,'2021-03-03',NULL,2.333,246.222,547,0.006489,NULL),
    (387915,50363,15,0.041716,1573.4763,'2021-03-04',NULL,2.333,246.222,547,0.006489,NULL),
    (387916,50363,12,0.035226,1328.71332,'2021-03-05',NULL,2.333,246.222,547,0.006489,NULL),
    (387917,50363,10,0.028737,1083.95034,'2021-03-06',NULL,2.333,246.222,547,0.006489,NULL),
    (387918,50363,8,0.022248,839.18736,'2021-03-07',NULL,2.333,246.222,547,0.006489,NULL),
    (387919,50363,5,0.015759,594.42438,'2021-03-08',NULL,2.333,246.222,547,0.006489,NULL),
    (387920,50363,3,0.00927,349.6614,'2021-03-09',NULL,2.333,246.222,547,0.006489,NULL),
    (387921,50363,1,0.002781,104.89842,'2021-03-10',NULL,2.333,246.222,547,0.006489,NULL),
    (387922,50363,0,0,0,'2021-03-11',NULL,0,0,0,0,1),
    (387923,50363,0,0,0,'2021-03-12',NULL,0,0,0,0,1),
    (387924,50363,0,0,0,'2021-03-13',NULL,0,0,0,0,1),
    (387925,50363,0,0,0,'2021-03-14',NULL,0,0,0,0,1),
    (387926,50363,0,0,0,'2021-03-15',NULL,0,0,0,0,1),
    (387927,50363,0,0,0,'2021-03-16',NULL,0,0,0,0,1),
    (387928,50363,0,0,0,'2021-03-17',NULL,0,0,0,0,1),
    (387929,50363,0,0,0,'2021-03-18',NULL,0,0,0,0,1),
    (387930,50363,0,0,0,'2021-03-19',NULL,0,0,0,0,1),
    (387931,50363,0,0,0,'2021-03-20',NULL,0,0,0,0,1),
    (387932,50363,0,0,0,'2021-03-21',NULL,0,0,0,0,1),
    (387933,50363,0,0,0,'2021-03-22',NULL,0,0,0,0,1),
    (387934,50363,0,0,0,'2021-03-23',NULL,0,0,0,0,1),
    (387935,50363,0,0,0,'2021-03-24',NULL,0,0,0,0,1),
    (387936,50363,0,0,0,'2021-03-25',NULL,0,0,0,0,1),
    (387937,50363,0,0,0,'2021-03-26',NULL,0,0,0,0,1),
    (387938,50363,0,0,0,'2021-03-27',NULL,0,0,0,0,1),
    (387939,50363,0,0,0,'2021-03-28',NULL,0,0,0,0,1),
    (387940,50363,0,0,0,'2021-03-29',NULL,0,0,0,0,1),
    (387941,50363,0,0,0,'2021-03-30',NULL,0,0,0,0,1),
    (387942,50363,0,0,0,'2021-03-31',NULL,0,0,0,0,1),
    (387943,50363,0,0,0,'2021-04-01',NULL,0,0,0,0,1),
    (387944,50363,0,0,0,'2021-04-02',NULL,0,0,0,0,1),
    (387945,50363,0,0,0,'2021-04-03',NULL,0,0,0,0,1),
    (387946,50363,0,0,0,'2021-04-04',NULL,0,0,0,0,1),
    (387947,50363,0,0,0,'2021-04-05',NULL,0,0,0,0,1),
    (387948,50363,0,0,0,'2021-04-06',NULL,0,0,0,0,1),
    (387949,50363,0,0,0,'2021-04-07',NULL,0,0,0,0,1),
    (387950,50363,0,0,0,'2021-04-08',NULL,0,0,0,0,1),
    (387951,50363,0,0,0,'2021-04-09',NULL,0,0,0,0,1),
    (387952,50363,0,0,0,'2021-04-10',NULL,0,0,0,0,1),
    (387953,50363,0,0,0,'2021-04-11',NULL,0,0,0,0,1),
    (387954,50363,0,0,0,'2021-04-12',NULL,0,0,0,0,1),
    (387955,50363,0,0,0,'2021-04-13',NULL,0,0,0,0,1),
    (387956,50363,0,0,0,'2021-04-14',NULL,0,0,0,0,1),
    (387957,50363,0,0,0,'2021-04-15',NULL,0,0,0,0,1),
    (387958,50363,0,0,0,'2021-04-16',NULL,0,0,0,0,1),
    (387959,50363,0,0,0,'2021-04-17',NULL,0,0,0,0,1),
    (387960,50363,0,0,0,'2021-04-18',NULL,0,0,0,0,1),
    (387961,50363,0,0,0,'2021-04-19',NULL,0,0,0,0,1),
    (387962,50363,0,0,0,'2021-04-20',NULL,0,0,0,0,1),
    (387963,50363,0,0,0,'2021-04-21',NULL,0,0,0,0,1),
    (387964,50363,0,0,0,'2021-04-22',NULL,0,0,0,0,1),
    (387965,50363,0,0,0,'2021-04-23',NULL,0,0,0,0,1),
    (387966,50363,0,0,0,'2021-04-24',NULL,0,0,0,0,1),
    (387967,50363,0,0,0,'2021-04-25',NULL,0,0,0,0,1),
    (387968,50363,0,0,0,'2021-04-26',NULL,0,0,0,0,1),
    (387969,50363,0,0,0,'2021-04-27',NULL,0,0,0,0,1),
    (387970,50363,0,0,0,'2021-04-28',NULL,0,0,0,0,1),
    (387971,50363,0,0,0,'2021-04-29',NULL,0,0,0,0,1),
    (387972,50363,0,0,0,'2021-04-30',NULL,0,0,0,0,1),
    (387973,50363,0,0,0,'2021-05-01',NULL,0,0,0,0,1),
    (387974,50363,0,0,0,'2021-05-02',NULL,0,0,0,0,1),
    (387975,50363,0,0,0,'2021-05-03',NULL,0,0,0,0,1),
    (387976,50363,0,0,0,'2021-05-04',NULL,0,0,0,0,1),
    (387977,50363,0,0,0,'2021-05-05',NULL,0,0,0,0,1),
    (387978,50363,0,0,0,'2021-05-06',NULL,0,0,0,0,1),
    (387979,50363,0,0,0,'2021-05-07',NULL,0,0,0,0,1),
    (387980,50363,0,0,0,'2021-05-08',NULL,0,0,0,0,1),
    (387981,50363,0,0,0,'2021-05-09',NULL,0,0,0,0,1),
    (387982,50363,0,0,0,'2021-05-10',NULL,0,0,0,0,1),
    (387983,50363,0,0,0,'2021-05-11',NULL,0,0,0,0,1),
    (387984,50363,0,0,0,'2021-05-12',NULL,0,0,0,0,1),
    (387985,50363,0,0,0,'2021-05-13',NULL,0,0,0,0,1),
    (387986,50363,0,0,0,'2021-05-14',NULL,0,0,0,0,1),
    (387987,50363,0,0,0,'2021-05-15',NULL,0,0,0,0,1),
    (387988,50363,0,0,0,'2021-05-16',NULL,0,0,0,0,1),
    (387989,50363,0,0,0,'2021-05-17',NULL,0,0,0,0,1),
    (387990,50363,0,0,0,'2021-05-18',NULL,0,0,0,0,1),
    (387991,50363,0,0,0,'2021-05-19',NULL,0,0,0,0,1),
    (387992,50363,0,0,0,'2021-05-20',NULL,0,0,0,0,1),
    (387993,50363,0,0,0,'2021-05-21',NULL,0,0,0,0,1),
    (387994,50363,0,0,0,'2021-05-22',NULL,0,0,0,0,1),
    (387995,50363,0,0,0,'2021-05-23',NULL,0,0,0,0,1),
    (387996,50363,0,0,0,'2021-05-24',NULL,0,0,0,0,1),
    (387997,50363,0,0,0,'2021-05-25',NULL,0,0,0,0,1),
    (387998,50363,0,0,0,'2021-05-26',NULL,0,0,0,0,1),
    (387999,50363,0,0,0,'2021-05-27',NULL,0,0,0,0,1),
    (388000,50363,0,0,0,'2021-05-28',NULL,0,0,0,0,1),
    (388001,50363,0,0,0,'2021-05-29',NULL,0,0,0,0,1),
    (388002,50363,0,0,0,'2021-05-30',NULL,0,0,0,0,1),
    (388003,50363,0,0,0,'2021-05-31',NULL,0,0,0,0,1),
    (388004,50363,0,0,0,'2021-06-01',NULL,0,0,0,0,1),
    (388005,50363,0,0,0,'2021-06-02',NULL,0,0,0,0,1),
    (388006,50363,0,0,0,'2021-06-03',NULL,0,0,0,0,1),
    (388007,50363,0,0,0,'2021-06-04',NULL,0,0,0,0,1),
    (388008,50363,0,0,0,'2021-06-05',NULL,0,0,0,0,1),
    (388009,50363,0,0,0,'2021-06-06',NULL,0,0,0,0,1),
    (388010,50363,0,0,0,'2021-06-07',NULL,0,0,0,0,1),
    (388011,50363,0,0,0,'2021-06-08',NULL,0,0,0,0,1),
    (289710,50363,0,0,0,'2021-06-09',NULL,0,0,0,0,1),
    (289711,50363,0,0,0,'2021-06-10',NULL,0,0,0,0,1),
    (289712,50363,0,0,0,'2021-06-11',NULL,0,0,0,0,1),
    (289713,50363,80,0.222483,8391.8736,'2021-06-12','2021-06-12',2.333,246.222,547,0.006489,NULL),
    (289714,50363,77,0.215994,8147.11062,'2021-06-13',NULL,2.333,246.222,547,0.006489,NULL),
    (289715,50363,75,0.209505,7902.34764,'2021-06-14',NULL,2.333,246.222,547,0.006489,NULL),
    (289716,50363,133,0.369878,13951.48986,'2021-06-15','2021-06-15',2.333,246.222,547,0.006489,NULL),
    (289717,50363,130,0.363389,13706.72688,'2021-06-16',NULL,2.333,246.222,547,0.006489,NULL),
    (289718,50363,128,0.356899,13461.9639,'2021-06-17',NULL,2.333,246.222,547,0.006489,NULL),
    (289719,50363,126,0.35041,13217.20092,'2021-06-18',NULL,2.333,246.222,547,0.006489,NULL),
    (289720,50363,123,0.343921,12972.43794,'2021-06-19',NULL,2.333,246.222,547,0.006489,NULL),
    (289721,50363,121,0.337432,12727.67496,'2021-06-20',NULL,2.333,246.222,547,0.006489,NULL),
    (289722,50363,119,0.330943,12482.91198,'2021-06-21',NULL,2.333,246.222,547,0.006489,NULL),
    (289723,50363,176,0.491316,18532.0542,'2021-06-22','2021-06-22',2.333,246.222,547,0.006489,NULL),
    (289724,50363,174,0.484827,18287.29122,'2021-06-23',NULL,2.333,246.222,547,0.006489,NULL),
    (289725,50363,172,0.478338,18042.52824,'2021-06-24',NULL,2.333,246.222,547,0.006489,NULL),
    (289726,50363,169,0.471849,17797.76526,'2021-06-25',NULL,2.333,246.222,547,0.006489,NULL),
    (289727,50363,167,0.46536,17553.00228,'2021-06-26',NULL,2.333,246.222,547,0.006489,NULL),
    (289728,50363,165,0.458871,17308.2393,'2021-06-27',NULL,2.333,246.222,547,0.006489,NULL),
    (289729,50363,162,0.452382,17063.47632,'2021-06-28',NULL,2.333,246.222,547,0.006489,NULL),
    (289730,50363,280,0.779617,29406.52374,'2021-06-29','2021-06-29',2.333,246.222,547,0.006489,NULL),
    (289731,50363,278,0.773128,29161.76076,'2021-06-30',NULL,2.333,246.222,547,0.006489,NULL),
    (289732,50363,275,0.766639,28916.99778,'2021-07-01',NULL,2.333,246.222,547,0.006489,NULL),
    (289733,50363,273,0.760149,28672.2348,'2021-07-02',NULL,2.333,246.222,547,0.006489,NULL),
    (289734,50363,271,0.75366,28427.47182,'2021-07-03',NULL,2.333,246.222,547,0.006489,NULL),
    (289735,50363,268,0.747171,28182.70884,'2021-07-04',NULL,2.333,246.222,547,0.006489,NULL),
    (289736,50363,266,0.740682,27937.94586,'2021-07-05',NULL,2.333,246.222,547,0.006489,NULL),
    (289737,50363,264,0.734193,27693.18288,'2021-07-06',NULL,2.333,246.222,547,0.006489,NULL),
    (289738,50363,261,0.727704,27448.4199,'2021-07-07',NULL,2.333,246.222,547,0.006489,NULL),
    (289739,50363,319,0.888077,33497.56212,'2021-07-08','2021-07-08',2.333,246.222,547,0.006489,NULL),
    (289740,50363,317,0.881588,33252.79914,'2021-07-09',NULL,2.333,246.222,547,0.006489,NULL),
    (289741,50363,314,0.875099,33008.03616,'2021-07-10',NULL,2.333,246.222,547,0.006489,NULL),
    (289742,50363,312,0.86861,32763.27318,'2021-07-11',NULL,2.333,246.222,547,0.006489,NULL),
    (289743,50363,310,0.862121,32518.5102,'2021-07-12',NULL,2.333,246.222,547,0.006489,NULL),
    (289744,50363,307,0.855632,32273.74722,'2021-07-13',NULL,2.333,246.222,547,0.006489,NULL),
    (289745,50363,305,0.849143,32028.98424,'2021-07-14',NULL,2.333,246.222,547,0.006489,NULL),
    (289746,50363,303,0.842653,31784.22126,'2021-07-15',NULL,2.333,246.222,547,0.006489,NULL),
    (289747,50363,300,0.836164,31539.45828,'2021-07-16',NULL,2.333,246.222,547,0.006489,NULL),
    (289748,50363,298,0.829675,31294.6953,'2021-07-17',NULL,2.333,246.222,547,0.006489,NULL),
    (289749,50363,296,0.823186,31049.93232,'2021-07-18',NULL,2.333,246.222,547,0.006489,NULL),
    (289750,50363,293,0.816697,30805.16934,'2021-07-19',NULL,2.333,246.222,547,0.006489,NULL),
    (289751,50363,291,0.810208,30560.40636,'2021-07-20',NULL,2.333,246.222,547,0.006489,NULL),
    (289752,50363,289,0.803719,30315.64338,'2021-07-21',NULL,2.333,246.222,547,0.006489,NULL),
    (289753,50363,286,0.79723,30070.8804,'2021-07-22',NULL,2.333,246.222,547,0.006489,NULL),
    (289754,50363,284,0.790741,29826.11742,'2021-07-23',NULL,2.333,246.222,547,0.006489,NULL),
    (289755,50363,282,0.784252,29581.35444,'2021-07-24',NULL,2.333,246.222,547,0.006489,NULL),
    (289756,50363,279,0.777763,29336.59146,'2021-07-25',NULL,2.333,246.222,547,0.006489,NULL),
    (289757,50363,277,0.771274,29091.82848,'2021-07-26',NULL,2.333,246.222,547,0.006489,NULL),
    (289758,50363,275,0.764784,28847.0655,'2021-07-27',NULL,2.333,246.222,547,0.006489,NULL),
    (289759,50363,272,0.758295,28602.30252,'2021-07-28',NULL,2.333,246.222,547,0.006489,NULL),
    (289760,50363,270,0.751806,28357.53954,'2021-07-29',NULL,2.333,246.222,547,0.006489,NULL),
    (289761,50363,268,0.745317,28112.77656,'2021-07-30',NULL,2.333,246.222,547,0.006489,NULL),
    (289762,50363,265,0.738828,27868.01358,'2021-07-31',NULL,2.333,246.222,547,0.006489,NULL),
    (289763,50363,263,0.732339,27623.2506,'2021-08-01',NULL,2.333,246.222,547,0.006489,NULL),
    (289764,50363,261,0.72585,27378.48762,'2021-08-02',NULL,2.333,246.222,547,0.006489,NULL),
    (289765,50363,258,0.719361,27133.72464,'2021-08-03',NULL,2.333,246.222,547,0.006489,NULL),
    (289766,50363,256,0.712872,26888.96166,'2021-08-04',NULL,2.333,246.222,547,0.006489,NULL),
    (289767,50363,254,0.706383,26644.19868,'2021-08-05',NULL,2.333,246.222,547,0.006489,NULL),
    (289768,50363,251,0.699894,26399.4357,'2021-08-06',NULL,2.333,246.222,547,0.006489,NULL),
    (289769,50363,249,0.693405,26154.67272,'2021-08-07',NULL,2.333,246.222,547,0.006489,NULL),
    (289770,50363,247,0.686916,25909.90974,'2021-08-08',NULL,2.333,246.222,547,0.006489,NULL),
    (289771,50363,244,0.680426,25665.14676,'2021-08-09',NULL,2.333,246.222,547,0.006489,NULL),
    (289772,50363,242,0.673937,25420.38378,'2021-08-10',NULL,2.333,246.222,547,0.006489,NULL),
    (289773,50363,240,0.667448,25175.6208,'2021-08-11',NULL,2.333,246.222,547,0.006489,NULL),
    (289774,50363,237,0.660959,24930.85782,'2021-08-12',NULL,2.333,246.222,547,0.006489,NULL),
    (289775,50363,235,0.65447,24686.09484,'2021-08-13',NULL,2.333,246.222,547,0.006489,NULL),
    (289776,50363,233,0.647981,24441.33186,'2021-08-14',NULL,2.333,246.222,547,0.006489,NULL),
    (289777,50363,230,0.641492,24196.56888,'2021-08-15',NULL,2.333,246.222,547,0.006489,NULL),
    (289778,50363,228,0.635003,23951.8059,'2021-08-16',NULL,2.333,246.222,547,0.006489,NULL),
    (289779,50363,226,0.628514,23707.04292,'2021-08-17',NULL,2.333,246.222,547,0.006489,NULL),
    (289780,50363,223,0.622025,23462.27994,'2021-08-18',NULL,2.333,246.222,547,0.006489,NULL),
    (289781,50363,221,0.615536,23217.51696,'2021-08-19',NULL,2.333,246.222,547,0.006489,NULL),
    (289782,50363,219,0.609047,22972.75398,'2021-08-20',NULL,2.333,246.222,547,0.006489,NULL),
    (289783,50363,216,0.602557,22727.991,'2021-08-21',NULL,2.333,246.222,547,0.006489,NULL),
    (289784,50363,214,0.596068,22483.22802,'2021-08-22',NULL,2.333,246.222,547,0.006489,NULL),
    (289785,50363,212,0.589579,22238.46504,'2021-08-23',NULL,2.333,246.222,547,0.006489,NULL),
    (289786,50363,209,0.58309,21993.70206,'2021-08-24',NULL,2.333,246.222,547,0.006489,NULL),
    (289787,50363,207,0.576601,21748.93908,'2021-08-25',NULL,2.333,246.222,547,0.006489,NULL),
    (289788,50363,205,0.570112,21504.1761,'2021-08-26',NULL,2.333,246.222,547,0.006489,NULL),
    (289789,50363,202,0.563623,21259.41312,'2021-08-27',NULL,2.333,246.222,547,0.006489,NULL),
    (289790,50363,200,0.557134,21014.65014,'2021-08-28',NULL,2.333,246.222,547,0.006489,NULL),
    (289791,50363,198,0.550645,20769.88716,'2021-08-29',NULL,2.333,246.222,547,0.006489,NULL),
    (289792,50363,195,0.544156,20525.12418,'2021-08-30',NULL,2.333,246.222,547,0.006489,NULL),
    (289793,50363,193,0.537667,20280.3612,'2021-08-31',NULL,2.333,246.222,547,0.006489,NULL),
    (289794,50363,191,0.531178,20035.59822,'2021-09-01',NULL,2.333,246.222,547,0.006489,NULL),
    (289795,50363,188,0.524689,19790.83524,'2021-09-02',NULL,2.333,246.222,547,0.006489,NULL),
    (289796,50363,186,0.518199,19546.07226,'2021-09-03',NULL,2.333,246.222,547,0.006489,NULL),
    (289797,50363,184,0.51171,19301.30928,'2021-09-04',NULL,2.333,246.222,547,0.006489,NULL),
    (289798,50363,181,0.505221,19056.5463,'2021-09-05',NULL,2.333,246.222,547,0.006489,NULL),
    (289799,50363,179,0.498732,18811.78332,'2021-09-06',NULL,2.333,246.222,547,0.006489,NULL),
    (289800,50363,177,0.492243,18567.02034,'2021-09-07',NULL,2.333,246.222,547,0.006489,NULL),
    (289801,50363,174,0.485754,18322.25736,'2021-09-08',NULL,2.333,246.222,547,0.006489,NULL),
    (289802,50363,172,0.479265,18077.49438,'2021-09-09',NULL,2.333,246.222,547,0.006489,NULL),
    (289803,50363,170,0.472776,17832.7314,'2021-09-10',NULL,2.333,246.222,547,0.006489,NULL),
    (289804,50363,167,0.466287,17587.96842,'2021-09-11',NULL,2.333,246.222,547,0.006489,NULL),
    (289805,50363,165,0.459798,17343.20544,'2021-09-12',NULL,2.333,246.222,547,0.006489,NULL),
    (289806,50363,163,0.453309,17098.44246,'2021-09-13',NULL,2.333,246.222,547,0.006489,NULL),
    (289807,50363,160,0.44682,16853.67948,'2021-09-14',NULL,2.333,246.222,547,0.006489,NULL),
    (289808,50363,158,0.44033,16608.9165,'2021-09-15',NULL,2.333,246.222,547,0.006489,NULL),
    (289809,50363,156,0.433841,16364.15352,'2021-09-16',NULL,2.333,246.222,547,0.006489,NULL),
    (289810,50363,153,0.427352,16119.39054,'2021-09-17',NULL,2.333,246.222,547,0.006489,NULL),
    (289811,50363,151,0.420863,15874.62756,'2021-09-18',NULL,2.333,246.222,547,0.006489,NULL),
    (289812,50363,149,0.414374,15629.86458,'2021-09-19',NULL,2.333,246.222,547,0.006489,NULL),
    (289813,50363,146,0.407885,15385.1016,'2021-09-20',NULL,2.333,246.222,547,0.006489,NULL),
    (289814,50363,144,0.401396,15140.33862,'2021-09-21',NULL,2.333,246.222,547,0.006489,NULL),
    (289815,50363,142,0.394907,14895.57564,'2021-09-22',NULL,2.333,246.222,547,0.006489,NULL),
    (289816,50363,139,0.388418,14650.81266,'2021-09-23',NULL,2.333,246.222,547,0.006489,NULL),
    (289817,50363,137,0.381929,14406.04968,'2021-09-24',NULL,2.333,246.222,547,0.006489,NULL),
    (289818,50363,135,0.37544,14161.2867,'2021-09-25',NULL,2.333,246.222,547,0.006489,NULL),
    (289819,50363,132,0.368951,13916.52372,'2021-09-26',NULL,2.333,246.222,547,0.006489,NULL),
    (289820,50363,130,0.362461,13671.76074,'2021-09-27',NULL,2.333,246.222,547,0.006489,NULL),
    (289821,50363,128,0.355972,13426.99776,'2021-09-28',NULL,2.333,246.222,547,0.006489,NULL),
    (289822,50363,125,0.349483,13182.23478,'2021-09-29',NULL,2.333,246.222,547,0.006489,NULL),
    (289823,50363,123,0.342994,12937.4718,'2021-09-30',NULL,2.333,246.222,547,0.006489,NULL),
    (289824,50363,121,0.336505,12692.70882,'2021-10-01',NULL,2.333,246.222,547,0.006489,NULL),
    (289825,50363,118,0.330016,12447.94584,'2021-10-02',NULL,2.333,246.222,547,0.006489,NULL),
    (289826,50363,116,0.323527,12203.18286,'2021-10-03',NULL,2.333,246.222,547,0.006489,NULL),
    (289827,50363,114,0.317038,11958.41988,'2021-10-04',NULL,2.333,246.222,547,0.006489,NULL),
    (289828,50363,111,0.310549,11713.6569,'2021-10-05',NULL,2.333,246.222,547,0.006489,NULL),
    (289829,50363,109,0.30406,11468.89392,'2021-10-06',NULL,2.333,246.222,547,0.006489,NULL),
    (289830,50363,107,0.297571,11224.13094,'2021-10-07',NULL,2.333,246.222,547,0.006489,NULL),
    (289831,50363,104,0.291082,10979.36796,'2021-10-08',NULL,2.333,246.222,547,0.006489,NULL),
    (289832,50363,102,0.284593,10734.60498,'2021-10-09',NULL,2.333,246.222,547,0.006489,NULL),
    (289833,50363,100,0.278103,10489.842,'2021-10-10',NULL,2.333,246.222,547,0.006489,NULL),
    (289834,50363,97,0.271614,10245.07902,'2021-10-11',NULL,2.333,246.222,547,0.006489,NULL),
    (289835,50363,95,0.265125,10000.31604,'2021-10-12',NULL,2.333,246.222,547,0.006489,NULL),
    (289836,50363,93,0.258636,9755.55306,'2021-10-13',NULL,2.333,246.222,547,0.006489,NULL),
    (289837,50363,90,0.252147,9510.79008,'2021-10-14',NULL,2.333,246.222,547,0.006489,NULL),
    (289838,50363,88,0.245658,9266.0271,'2021-10-15',NULL,2.333,246.222,547,0.006489,NULL),
    (289839,50363,86,0.239169,9021.26412,'2021-10-16',NULL,2.333,246.222,547,0.006489,NULL),
    (289840,50363,83,0.23268,8776.50114,'2021-10-17',NULL,2.333,246.222,547,0.006489,NULL),
    (289841,50363,81,0.226191,8531.73816,'2021-10-18',NULL,2.333,246.222,547,0.006489,NULL),
    (289842,50363,79,0.219702,8286.97518,'2021-10-19',NULL,2.333,246.222,547,0.006489,NULL),
    (289843,50363,76,0.213213,8042.2122,'2021-10-20',NULL,2.333,246.222,547,0.006489,NULL),
    (289844,50363,74,0.206724,7797.44922,'2021-10-21',NULL,2.333,246.222,547,0.006489,NULL),
    (289845,50363,72,0.200234,7552.68624,'2021-10-22',NULL,2.333,246.222,547,0.006489,NULL),
    (289846,50363,69,0.193745,7307.92326,'2021-10-23',NULL,2.333,246.222,547,0.006489,NULL),
    (289847,50363,67,0.187256,7063.16028,'2021-10-24',NULL,2.333,246.222,547,0.006489,NULL),
    (289848,50363,65,0.180767,6818.3973,'2021-10-25',NULL,2.333,246.222,547,0.006489,NULL),
    (289849,50363,62,0.174278,6573.63432,'2021-10-26',NULL,2.333,246.222,547,0.006489,NULL),
    (289850,50363,60,0.167789,6328.87134,'2021-10-27',NULL,2.333,246.222,547,0.006489,NULL),
    (289851,50363,58,0.1613,6084.10836,'2021-10-28',NULL,2.333,246.222,547,0.006489,NULL),
    (289852,50363,55,0.154811,5839.34538,'2021-10-29',NULL,2.333,246.222,547,0.006489,NULL),
    (289853,50363,53,0.148322,5594.5824,'2021-10-30',NULL,2.333,246.222,547,0.006489,NULL),
    (289854,50363,51,0.141833,5349.81942,'2021-10-31',NULL,2.333,246.222,547,0.006489,NULL),
    (289855,50363,48,0.135344,5105.05644,'2021-11-01',NULL,2.333,246.222,547,0.006489,NULL),
    (289856,50363,46,0.128855,4860.29346,'2021-11-02',NULL,2.333,246.222,547,0.006489,NULL),
    (289857,50363,44,0.122366,4615.53048,'2021-11-03',NULL,2.333,246.222,547,0.006489,NULL),
    (289858,50363,41,0.115876,4370.7675,'2021-11-04',NULL,2.333,246.222,547,0.006489,NULL),
    (289859,50363,39,0.109387,4126.00452,'2021-11-05',NULL,2.333,246.222,547,0.006489,NULL),
    (289860,50363,37,0.102898,3881.24154,'2021-11-06',NULL,2.333,246.222,547,0.006489,NULL),
    (289861,50363,34,0.096409,3636.47856,'2021-11-07',NULL,2.333,246.222,547,0.006489,NULL),
    (289862,50363,32,0.08992,3391.71558,'2021-11-08',NULL,2.333,246.222,547,0.006489,NULL),
    (289863,50363,30,0.083431,3146.9526,'2021-11-09',NULL,2.333,246.222,547,0.006489,NULL),
    (289864,50363,27,0.076942,2902.18962,'2021-11-10',NULL,2.333,246.222,547,0.006489,NULL),
    (289865,50363,25,0.070453,2657.42664,'2021-11-11',NULL,2.333,246.222,547,0.006489,NULL),
    (289866,50363,23,0.063964,2412.66366,'2021-11-12',NULL,2.333,246.222,547,0.006489,NULL),
    (289867,50363,20,0.057475,2167.90068,'2021-11-13',NULL,2.333,246.222,547,0.006489,NULL),
    (289868,50363,18,0.050986,1923.1377,'2021-11-14',NULL,2.333,246.222,547,0.006489,NULL),
    (289869,50363,16,0.044497,1678.37472,'2021-11-15',NULL,2.333,246.222,547,0.006489,NULL),
    (289870,50363,13,0.038007,1433.61174,'2021-11-16',NULL,2.333,246.222,547,0.006489,NULL),
    (289871,50363,11,0.031518,1188.84876,'2021-11-17',NULL,2.333,246.222,547,0.006489,NULL),
    (289872,50363,9,0.025029,944.08578,'2021-11-18',NULL,2.333,246.222,547,0.006489,NULL),
    (289873,50363,6,0.01854,699.3228,'2021-11-19',NULL,2.333,246.222,547,0.006489,NULL),
    (289874,50363,4,0.012051,454.55982,'2021-11-20',NULL,2.333,246.222,547,0.006489,NULL),
    (289875,50363,2,0.005562,209.79684,'2021-11-21',NULL,2.333,246.222,547,0.006489,NULL),
    (289876,50363,0,0,0,'2021-11-22',NULL,0,0,0,0,1),
    (289877,50363,0,0,0,'2021-11-23',NULL,0,0,0,0,1),
    (289878,50363,0,0,0,'2021-11-24',NULL,0,0,0,0,1),
    (289879,50363,0,0,0,'2021-11-25',NULL,0,0,0,0,1),
    (289880,50363,0,0,0,'2021-11-26',NULL,0,0,0,0,1),
    (289881,50363,0,0,0,'2021-11-27',NULL,0,0,0,0,1),
    (289882,50363,0,0,0,'2021-11-28',NULL,0,0,0,0,1),
    (289883,50363,0,0,0,'2021-11-29',NULL,0,0,0,0,1),
    (289884,50363,0,0,0,'2021-11-30',NULL,0,0,0,0,1)

    If we order by the [Date] column in ascending order, we can see that:

    1. The DayOOS column has a value of 1 from Date ('2021-03-11') to Date ('2021-06-11') (Let's call this group 1)
    2. It also has a value of 1 from Date ('2021-11-22') to Date ('2021-11-30') (Let's call this group 2)

    I want to add a new column (Column1) that will only show the date ( from the [Date] column) for the first row of each group (group 1 and group 2) and Null for all the other rows, when ordered by the date column

    in ascending order. So in this case, row 14 and row 270 of the new column would have date values.

    Is this possible?

  • Something like this maybe

    SELECT	[RowNo] = ROW_NUMBER()
    OVER
    (
    ORDER BY [Date]
    )
    , [Column1] = CASE WHEN DayOOS IS NOT NULL AND [T_Column1] = 1 THEN [Date] ELSE NULL END
    , Id
    , [Date]
    , DayOOS
    FROM
    (
    SELECT [T_Column1] = ROW_NUMBER()
    OVER
    (
    PARTITION BY X_Group
    ORDER BY [Date]
    )
    , Id
    , [Date]
    , DayOOS
    FROM #DailyOnHandForecast
    CROSS APPLY
    (
    SELECT [X_Group] = CASE WHEN DayOOS = 1 AND [Date] >= '20210311' AND [Date] <= '20210611' THEN 1
    WHEN DayOOS = 1 AND [Date] >= '20211122' AND [Date] <= '20211130' THEN 2
    ELSE 999
    END
    ) AS X1
    ) AS T
    ORDER BY [Date]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply