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

  • 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 #mytable

    (

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

    Rec_Num CHAR(15),

    TheName CHAR(15),

    TheDays INT,

    Appt_Seq INT,

    Appt_Cycle INT,

    Days_Cycle INT,

    DaysInCycle INT,

    Current_Cycle INT,

    Intake_Date DATETIME,

    Subsequent_Date DATETIME,

    )

    --===== 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 #mytable

    (ID, Rec_Num, TheName, TheDays, Appt_Seq, Appt_Cycle, Days_Cycle, DaysInCycle, Current_Cycle, Intake_Date, Subsequent_Date)

    SELECT 1, '12345678' , 'Person123' , 13 , 1 , 1 , 1 , 1 , 1 , '02/01/2018 12:00AM' , '02/14/2018 12:00AM' UNION ALL

    SELECT 2, '12345678' , 'Person123' , 27 , 2 , 1 , 1 , 2 , 1 , '02/01/2018 12:00AM' , '02/28/2018 12:00AM' UNION ALL

    SELECT 3, '12345678' , 'Person123' , 41 , 3 , 1 , 1 , 3 , 1 , '02/01/2018 12:00AM' , '03/14/2018 12:00AM' UNION ALL

    SELECT 4, '12345678' , 'Person123' , 48 , 4 , 1 , 1 , 4 , 1 , '02/01/2018 12:00AM' , '03/21/2018 12:00AM' UNION ALL

    SELECT 5, '12345678' , 'Person123' , 55 , 5 , 1 , 1 , 5 , 1 , '02/01/2018 12:00AM' , '03/28/2018 12:00AM' UNION ALL

    SELECT 6, '12345678' , 'Person123' , 62 , 6 , 1 , 1 , 6 , 2 , '02/01/2018 12:00AM' , '04/04/2018 12:00AM' UNION ALL

    SELECT 7, '12345678' , 'Person123' , 76 , 7 , 2 , 1 , 7 , 2 , '02/01/2018 12:00AM' , '04/18/2018 12:00AM' UNION ALL

    SELECT 8, '12345678' , 'Person123' , 90 , 8 , 2 , 1 , 8 , 2 , '02/01/2018 12:00AM' , '05/02/2018 12:00AM' UNION ALL

    SELECT 9, '12345678' , 'Person123' , 97 , 9 , 2 , 2 , 1 , 2 , '02/01/2018 12:00AM' , '05/09/2018 12:00AM' UNION ALL

    SELECT 10, '12345678' , 'Person123' , 118 , 10 , 2 , 2 , 2 , 2 , '02/01/2018 12:00AM' , '05/30/2018 12:00AM' UNION ALL

    SELECT 11, '12345678' , 'Person123' , 146 , 11 , 2 , 2 , 3 , 2 , '02/01/2018 12:00AM' , '06/27/2018 12:00AM' UNION ALL

    SELECT 12, '12345678' , 'Person123' , 160 , 12 , 2 , 2 , 4 , 3 , '02/01/2018 12:00AM' , '07/11/2018 12:00AM' UNION ALL

    SELECT 13, '12345678' , 'Person123' , 174 , 13 , 3 , 2 , 5 , 3 , '02/01/2018 12:00AM' , '07/25/2018 12:00AM' UNION ALL

    SELECT 14, '12345678' , 'Person123' , 188 , 14 , 3 , 3 , 1 , 3 , '02/01/2018 12:00AM' , '08/08/2018 12:00AM' UNION ALL

    SELECT 15, '12345678' , 'Person123' , 230 , 15 , 3 , 3 , 2 , 3 , '02/01/2018 12:00AM' , '09/19/2018 12:00AM' UNION ALL

    SELECT 16, '12345678' , 'Person123' , 244 , 16 , 3 , 3 , 3 , 3 , '02/01/2018 12:00AM' , '10/03/2018 12:00AM' UNION ALL

    SELECT 17, '12345678' , 'Person123' , 272 , 17 , 3 , 4 , 1 , 3 , '02/01/2018 12:00AM' , '10/31/2018 12:00AM' UNION ALL

    SELECT 18, '12345678' , 'Person123' , 287 , 18 , 3 , 4 , 2 , 4 , '02/01/2018 12:00AM' , '11/15/2018 12:00AM' UNION ALL

    SELECT 19, '12345678' , 'Person123' , 300 , 19 , 4 , 4 , 3 , 4 , '02/01/2018 12:00AM' , '11/28/2018 12:00AM' UNION ALL

    SELECT 20, '12345678' , 'Person123' , 307 , 20 , 4 , 4 , 4 , 4 , '02/01/2018 12:00AM' , '12/05/2018 12:00AM' UNION ALL

    SELECT 21, '12345678' , 'Person123' , 321 , 21 , 4 , 4 , 5 , 4 , '02/01/2018 12:00AM' , '12/19/2018 12:00AM' UNION ALL

    SELECT 22, '12345678' , 'Person123' , 377 , 22 , 4 , 5 , 1 , 4 , '02/01/2018 12:00AM' , '02/13/2019 12:00AM' UNION ALL

    SELECT 23, '12345678' , 'Person123' , 391 , 23 , 4 , 5 , 2 , 4 , '02/01/2018 12:00AM' , '02/27/2019 12:00AM' UNION ALL

    SELECT 24, '12345678' , 'Person123' , 419 , 24 , 4 , 5 , 3 , 5 , '02/01/2018 12:00AM' , '03/27/2019 12:00AM' UNION ALL

    SELECT 25, '12345678' , 'Person123' , 454 , 25 , 5 , 6 , 1 , 5 , '02/01/2018 12:00AM' , '05/01/2019 12:00AM' UNION ALL

    SELECT 26, '12345678' , 'Person123' , 489 , 26 , 5 , 6 , 2 , 5 , '02/01/2018 12:00AM' , '06/05/2019 12:00AM'

     

     

    • This topic was modified 2 weeks, 6 days ago by  lmeinke. Reason: Added DDL
    • This topic was modified 2 weeks, 6 days ago by  lmeinke.
    • This topic was modified 2 weeks, 6 days ago by  lmeinke. Reason: Added image of "desired" results with red arrow. Green/Blue arrows are the meaningful data
    Attachments:
    You must be logged in to view attached files.
  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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. 

  • 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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.

  • >> 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. 

  • 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.

  • 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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 2 weeks, 6 days ago by  lmeinke.
    Attachments:
    You must be logged in to view attached files.
  • 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,

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

     

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

  • 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.

  • 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

  • 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.

  • Does it also restart at 0 when aggregating the days?

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

Viewing 15 posts - 1 through 15 (of 19 total)

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