Update Column Based on Criteria from Other Columns -- improved post included DDL

• lmeinke

  SSCommitted

  Points: 1956

  More actions

  January 5, 2021 at 11:57 pm

  #3827863

  I have a requirement where I need to identify compliance/non-compliance.  The business rule is they must update "Item X" "every 90 days  OR 6 appointments whichever is greater".  The raw data are only Rec#, Name, Intake_Date, Subs_Date (subsequent).  The other columns I have calculated in my fruitless attempts to mimic this business logic.

  I am attempting to determine "theoretical cycles" of  >=90 days (from intake) and >= 6 appointments.  This seems simple but is proving difficult for me.  If you look in the "Days_Cycle" and the "Appt_Cycle" you will see situations where the theoretical "cycle" will reach the "90 Day" criteria first, then the 6 appt. criteria as well as vise versa.  I have been attempting to create a column that represents the true theoretical cycle but it seems I need to update either the "Days_Cycle" or the "Appt Cycle" in the process.

  In the first 8 rows you will see the 90 Day requirement was not met until the 8th appointment.  I would then need to reset the "Appt Cycle" to start over after the 8th row.

  Any suggestions or directions would be greatly appreciated.

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

  DROP TABLE #mytable

  --===== Create the test table with

  CREATE TABLE #mytable22

  (

  UniqueChronID INT ,

  UniqueID NUMERIC(20,10),

  Chron_Appt INT,

  Chron_Days INT,

  Intake_Date DATETIME,

  Subs_Date DATETIME,

  Theoretical_Cycle INT

  )

  -- ===== Setup any special required conditions especially where dates are concerned

  -- SET DATEFORMAT MDY

  ---- ===== All Inserts into the IDENTITY column

  -- SET IDENTITY_INSERT #mytable ON

  -- ===== Insert the test data into the test table

  INSERT INTO #mytable22

  (UniqueChronID, UniqueID, Chron_Appt, Chron_Days, Intake_Date, Subs_Date, Theoretical_Cycle)

  SELECT 1, 441881.77, 1, 82, 1/3/2019 , 3/26/2019 , 1 UNION ALL

  SELECT 1, 441881.77, 2, 110, 1/3/2019 , 4/23/2019 , 1 UNION ALL

  SELECT 1, 441881.77, 3, 124, 1/3/2019 , 5/7/2019 , 1 UNION ALL

  SELECT 1, 441881.77, 4, 159, 1/3/2019 , 6/11/2019 , 1 UNION ALL

  SELECT 1, 441881.77, 5, 180, 1/3/2019 , 7/2/2019 , 1 UNION ALL

  SELECT 1, 441881.77, 6, 229, 1/3/2019 , 8/20/2019 , 1 UNION ALL

  SELECT 2, 515122.73, 1, 42, 7/2/2018 , 8/13/2018 , 1 UNION ALL

  SELECT 2, 515122.73, 2, 94, 7/2/2018 , 10/4/2018 , 1 UNION ALL

  SELECT 2, 515122.73, 3, 136, 7/2/2018 , 11/15/2018 , 1 UNION ALL

  SELECT 2, 515122.73, 4, 179, 7/2/2018 , 12/28/2018 , 1 UNION ALL

  SELECT 2, 515122.73, 5, 219, 7/2/2018 , 2/6/2019 , 1 UNION ALL

  SELECT 2, 515122.73, 6, 261, 7/2/2018 , 3/20/2019 , 1 UNION ALL

  SELECT 3, 532846.06, 1, 19, 4/12/2019 , 5/1/2019 , 1 UNION ALL

  SELECT 3, 532846.06, 2, 53, 4/12/2019 , 6/4/2019 , 1 UNION ALL

  SELECT 3, 532846.06, 3, 82, 4/12/2019 , 7/3/2019 , 1 UNION ALL

  SELECT 3, 532846.06, 4, 116, 4/12/2019 , 8/6/2019 , 1 UNION ALL

  SELECT 3, 532846.06, 5, 147, 4/12/2019 , 9/6/2019 , 1 UNION ALL

  SELECT 3, 532846.06, 6, 179, 4/12/2019 , 10/8/2019 , 1 UNION ALL

  SELECT 4, 543082.89, 1, 16, 7/24/2018 , 8/9/2018 , 1 UNION ALL

  SELECT 4, 543082.89, 2, 52, 7/24/2018 , 9/14/2018 , 1 UNION ALL

  SELECT 4, 543082.89, 3, 77, 7/24/2018 , 10/9/2018 , 1 UNION ALL

  SELECT 4, 543082.89, 4, 146, 7/24/2018 , 12/17/2018 , 1 UNION ALL

  SELECT 4, 543082.89, 5, 195, 7/24/2018 , 2/4/2019 , 1 UNION ALL

  SELECT 4, 543082.89, 6, 251, 7/24/2018 , 4/1/2019 , 1 UNION ALL

  SELECT 4, 543082.89, 6, 251, 7/24/2018 , 4/1/2019 , 1 UNION ALL

  SELECT 4, 543082.89, 6, 251, 7/24/2018 , 4/1/2019 , 1 UNION ALL

  SELECT 5, 579540.23, 1, 7, 9/17/2018 , 9/24/2018 , 1 UNION ALL

  SELECT 5, 579540.23, 2, 15, 9/17/2018 , 10/2/2018 , 1 UNION ALL

  SELECT 5, 579540.23, 3, 22, 9/17/2018 , 10/9/2018 , 1 UNION ALL

  SELECT 5, 579540.23, 4, 29, 9/17/2018 , 10/16/2018 , 1 UNION ALL

  SELECT 5, 579540.23, 5, 35, 9/17/2018 , 10/22/2018 , 1 UNION ALL

  SELECT 5, 579540.23, 6, 50, 9/17/2018 , 11/6/2018 , 1 UNION ALL

  SELECT 5, 579540.23, 7, 64, 9/17/2018 , 11/20/2018 , 1 UNION ALL

  SELECT 5, 579540.23, 8, 70, 9/17/2018 , 11/26/2018 , 1 UNION ALL

  SELECT 5, 579540.23, 9, 113, 9/17/2018 , 1/8/2019 , 2 UNION ALL

  SELECT 5, 579540.23, 10, 120, 9/17/2018 , 1/15/2019 , 2 UNION ALL

  SELECT 5, 579540.23, 11, 127, 9/17/2018 , 1/22/2019 , 2 UNION ALL

  SELECT 5, 579540.23, 12, 134, 9/17/2018 , 1/29/2019 , 2 UNION ALL

  SELECT 5, 579540.23, 13, 142, 9/17/2018 , 2/6/2019 , 2 UNION ALL

  SELECT 5, 579540.23, 14, 149, 9/17/2018 , 2/13/2019 , 2 UNION ALL

  SELECT 6, 705897.12, 1, 2, 9/17/2018 , 9/19/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 2, 3, 9/17/2018 , 9/20/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 3, 7, 9/17/2018 , 9/24/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 5, 8, 9/17/2018 , 9/25/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 4, 8, 9/17/2018 , 9/25/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 6, 9, 9/17/2018 , 9/26/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 7, 10, 9/17/2018 , 9/27/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 8, 15, 9/17/2018 , 10/2/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 9, 16, 9/17/2018 , 10/3/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 11, 17, 9/17/2018 , 10/4/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 10, 17, 9/17/2018 , 10/4/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 12, 21, 9/17/2018 , 10/8/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 13, 22, 9/17/2018 , 10/9/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 14, 23, 9/17/2018 , 10/10/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 16, 24, 9/17/2018 , 10/11/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 15, 24, 9/17/2018 , 10/11/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 17, 35, 9/17/2018 , 10/22/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 18, 36, 9/17/2018 , 10/23/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 20, 37, 9/17/2018 , 10/24/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 19, 37, 9/17/2018 , 10/24/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 21, 38, 9/17/2018 , 10/25/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 22, 42, 9/17/2018 , 10/29/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 23, 43, 9/17/2018 , 10/30/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 25, 44, 9/17/2018 , 10/31/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 24, 44, 9/17/2018 , 10/31/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 26, 50, 9/17/2018 , 11/6/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 27, 57, 9/17/2018 , 11/13/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 28, 71, 9/17/2018 , 11/27/2018 , 1 UNION ALL

  SELECT 6, 705897.12, 29, 171, 9/17/2018 , 3/7/2019 , 2 UNION ALL

  SELECT 6, 705897.12, 30, 175, 9/17/2018 , 3/11/2019 , 2 UNION ALL

  SELECT 6, 705897.12, 31, 176, 9/17/2018 , 3/12/2019 , 2 UNION ALL

  SELECT 6, 705897.12, 33, 177, 9/17/2018 , 3/13/2019 , 2 UNION ALL

  SELECT 6, 705897.12, 32, 177, 9/17/2018 , 3/13/2019 , 2 UNION ALL

  SELECT 6, 705897.12, 34, 178, 9/17/2018 , 3/14/2019 , 2 UNION ALL

  SELECT 6, 705897.12, 35, 182, 9/17/2018 , 3/18/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 36, 183, 9/17/2018 , 3/19/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 37, 184, 9/17/2018 , 3/20/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 38, 184, 9/17/2018 , 3/20/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 39, 185, 9/17/2018 , 3/21/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 40, 189, 9/17/2018 , 3/25/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 41, 190, 9/17/2018 , 3/26/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 42, 191, 9/17/2018 , 3/27/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 43, 192, 9/17/2018 , 3/28/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 45, 196, 9/17/2018 , 4/1/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 44, 196, 9/17/2018 , 4/1/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 46, 197, 9/17/2018 , 4/2/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 47, 199, 9/17/2018 , 4/4/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 48, 203, 9/17/2018 , 4/8/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 50, 204, 9/17/2018 , 4/9/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 49, 204, 9/17/2018 , 4/9/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 51, 205, 9/17/2018 , 4/10/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 52, 206, 9/17/2018 , 4/11/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 53, 210, 9/17/2018 , 4/15/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 54, 211, 9/17/2018 , 4/16/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 55, 212, 9/17/2018 , 4/17/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 57, 213, 9/17/2018 , 4/18/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 56, 213, 9/17/2018 , 4/18/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 58, 218, 9/17/2018 , 4/23/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 59, 218, 9/17/2018 , 4/23/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 60, 225, 9/17/2018 , 4/30/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 61, 232, 9/17/2018 , 5/7/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 62, 246, 9/17/2018 , 5/21/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 63, 253, 9/17/2018 , 5/28/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 64, 260, 9/17/2018 , 6/4/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 65, 267, 9/17/2018 , 6/11/2019 , 3 UNION ALL

  SELECT 6, 705897.12, 66, 274, 9/17/2018 , 6/18/2019 , 4 UNION ALL

  SELECT 6, 705897.12, 67, 281, 9/17/2018 , 6/25/2019 , 4 UNION ALL

  SELECT 6, 705897.12, 68, 288, 9/17/2018 , 7/2/2019 , 4 UNION ALL

  SELECT 6, 705897.12, 69, 295, 9/17/2018 , 7/9/2019 , 4 UNION ALL

  SELECT 6, 705897.12, 70, 302, 9/17/2018 , 7/16/2019 , 4 UNION ALL

  SELECT 6, 705897.12, 71, 316, 9/17/2018 , 7/30/2019 , 4 UNION ALL

  SELECT 6, 705897.12, 72, 344, 9/17/2018 , 8/27/2019 , 4 UNION ALL

  SELECT 6, 705897.12, 73, 351, 9/17/2018 , 9/3/2019 , 4

   

   

  • This topic was modified 3 years, 3 months ago by  lmeinke. Reason: Added DDL
  • This topic was modified 3 years, 3 months ago by  lmeinke.
  • This topic was modified 3 years, 3 months ago by  lmeinke. Reason: Added image of "desired" results with red arrow. Green/Blue arrows are the meaningful data
  • This topic was modified 3 years, 2 months ago by  lmeinke. Reason: New Data - more complete examples - Added "Theoretical_Cycle" column with desired answer

  Attachments:

  You must be logged in to view attached files.

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  January 6, 2021 at 1:35 am

  #3827891

  If you want coded help, please see the article at the first link in my signature line below for one way to create "Readily Consumable Data".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jcelko212 32090

  SSCrazy Eights

  Points: 9294

  More actions

  January 6, 2021 at 3:34 am

  #3827930

  Why did you fail to post DDL? Have you ever read any of the posting rules on any SQL forum for the past 30+ years? Why do you think anyone's going to open up your "Cycle_Example.xlsx" attachment unless they know and trust you? Please tell me you don't open such things at work.

  What you posted is so vague as to be useless. There's apparently something called an appointment in your data model. And it has a date. But to whom does an appointment belong? What is the key that this table must have by definition? It looks like appointments are being reset instead of sequentially numbered; that makes no sense to me.

  Would you like to try again? And please don't assume we know what you're thinking.

  Please post DDL and follow ANSI/ISO standards when asking for help. 

• Phil Parkin

  SSC Guru

  Points: 246686

  More actions

  January 6, 2021 at 10:24 am

  #3828011

  With >1,700 points, you should know better. As already requested by others, please post consumable DDL, sample data in the form of INSERT statements and desired results based on your sample data.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• lmeinke

  SSCommitted

  Points: 1956

  More actions

  January 6, 2021 at 2:14 pm

  #3828098

  The truth is I've been out of coding and development for a few years.  I haven't posted many questions and when looking at the forums the proper path is not readily apparent.  I appreciate Jeff's comments as he was curteous.  Jcelko... not sure what your issue is, we all get tired of seeing repeat mistakes but there are times for discretion and actual help as well.  Phil, it has been a long time since I posted and it is not intuitive to navigate where to post.  I apologize for not following protocol.

• jcelko212 32090

  SSCrazy Eights

  Points: 9294

  More actions

  January 6, 2021 at 3:04 pm

  #3828105

  >> Jcelko... not sure what your issue is, we all get tired of seeing repeat mistakes but there are times for discretion and actual help as well. <<

  I'm a grumpy old man, who helped write the standards for this language 🙂 . After doing this for over 30 years, I found that if you don't call people on repeated errors, they keep repeating. I make the assumption that someone wants to actually learn and improve themselves. If all you want is a quick answer in the kludge, then I am not your guy. If you think I'm brutal, then read stories about Dykstra teaching freshman programming 🙁 .

  Please post DDL and follow ANSI/ISO standards when asking for help. 

• lmeinke

  SSCommitted

  Points: 1956

  More actions

  January 6, 2021 at 3:34 pm

  #3828150

  Jcelko... I thank you for helping to write the standards for this language.  You have something to be very proud of.  Your behavior/communication style... not so much.

• Phil Parkin

  SSC Guru

  Points: 246686

  More actions

  January 6, 2021 at 4:14 pm

  #3828159

  Well done with posting consumable data.

  Can you also post desired results, based on your sample data?

  Like Joe and many others here, I am averse to opening spreadsheets from untrusted sources. If your desired results are already in there, please post them again, in text or image format.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• lmeinke

  SSCommitted

  Points: 1956

  More actions

  January 6, 2021 at 4:59 pm

  #3828175

  After getting past my initial offense I can see how lacking my original post was.  I've added an image for "desired" results and indicated the column with a red arrow.  The blue/green arrows are the meaningful data for calculations.  All other columns are calculated by me in an effort to reproduce the logic... you may ignore or consider on your own.  I also corrected "TheDays" column as it was recalculating everytime another condition was met... this was an oversight of mine initially.

   

  I need to end the "Theoretical Cycle" when both the 90 day criteria AND the 6 appointment criteria has been met.  Then, I need to synchronize the counters to begin together.

  • This reply was modified 3 years, 3 months ago by  lmeinke.

  Attachments:

  You must be logged in to view attached files.

• lmeinke

  SSCommitted

  Points: 1956

  More actions

  January 13, 2021 at 8:57 pm

  #3832120

  This is one last plea for help on this post.  I was hoping someone in the community may have run into this type of scenario in the past?  My thought at the moment is to experiment with some CTEs that attempt to calculate this outside of the data and join it back into the data set at a later point.  Still would appreciate any insight anyone may have.

  Thank you,

• Steve Collins

  SSCrazy

  Points: 2068

  More actions

  January 14, 2021 at 5:33 pm

  #3833447

  Integer division comes in handy when creating groupings based on ranges of integers,  aka "buckets".  Something like this

  select t.*, v.*, cycle.*
  from #mytable t
       cross apply (values ((TheDays-1)/90, (Appt_Seq-1)/6)) v(days_grp, appt_grp)
       cross apply (select max(vu.grp_val)+1 max_val 
                    from (values (v.days_grp), (v.appt_grp)) vu(grp_val)) cycle;

   

  • This reply was modified 3 years, 3 months ago by  Steve Collins.

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• lmeinke

  SSCommitted

  Points: 1956

  More actions

  January 14, 2021 at 6:07 pm

  #3833465

  Thank you Steve for this suggestion.  I quickly tested this and am not getting the results wanted in the "Theoretical_Cycle" column I indicated in the image.  I will return to this later this afternoon and investigate more.  Either way, I appreciate your input.

• Steve Collins

  SSCrazy

  Points: 2068

  More actions

  January 14, 2021 at 7:21 pm

  #3833484

  The 'max_val' column matches the 'Days_Cycle' column in the table provided.  The 'theoritical_cycle' (in rows where ID is greater than 19) doesn't seem to follow the rules you're suggesting

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• lmeinke

  SSCommitted

  Points: 1956

  More actions

  January 14, 2021 at 9:12 pm

  #3833533

  Thank you again Steve.  I believe the image follows the rules I suggested.  We need to always have 6 "rows" or appointments as that is one of the criteria.  In the case where reaching 90 days includes > 6 appts the "Theoretical_Cycle" needs to be synchronized so that the next appt BEGINS at that point.  In the image you can see the first cycle has to last for 8 appts (to reach the 90 day criteria).  The 2nd cycle of appts would then need to begin in row 9 and continue for a minumum of 6 more rows/appts.  Hopefully you can follow what I'm attempting to communicate.

• Steve Collins

  SSCrazy

  Points: 2068

  More actions

  January 14, 2021 at 10:26 pm

  #3833559

  Does it also restart at 0 when aggregating the days?

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können