Problem with Logic for new column

Question

Post reply

Problem with Logic for new column

kobinarashid

Old Hand

Points: 337

March 1, 2021 at 1:55 am

Go to Answer

#3851458

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:

The DayOOS column has a value of 1 from Date ('2021-03-11') to Date ('2021-06-11') (Let's call this group 1)
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?

Kristen-173977

SSCrazy Eights

Points: 8687

March 1, 2021 at 7:41 am

Answer

#3851536

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 1 (of 1 total)

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