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

Question

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

Viewing 9 posts - 16 through 23 (of 23 total)

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

lmeinke SSCommitted Points: 1956 More actions · Answer 1

Yes, both of them have to "re-start" at appt#1 and day#1 when the criteria is met (>= 6 appts & >= 90 days).

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

Ugh it's dependent on calculated row values. Although painful to create, here's a WHILE loop which populates a new table '#myNewTable' with the ID and the derived theoretical_cycle column. It's SLOW like rusty hamster wheels but it appears to match the required output.

drop table if exists #myNewTable;
go
create table #myNewTable(
  ID                    int not null,
  theoretical_cycle     int);

declare
  @rem_rows       int,
  @min_id         int,
  @i              int=1;

select @rem_rows=count(*),
       @min_id=min(ID)
from #mytable;

while @rem_rows>0
    begin
        declare @id         table(id        int unique not null);

        ;with rule_cte as (
            select *, row_number() over (order by ID) rn,
                   datediff(day, Intake_Date, Subsequent_Date) sum_days
            from #mytable
            where ID>=@min_id)
        insert into #myNewTable(ID, theoretical_cycle)
        output inserted.ID into @id
        select ID, @i 
        from rule_cte
        where rn<=6 or sum_days<=90;

        select @rem_rows=@rem_rows-@@rowcount;

        select @min_id=min(ID)
        from #mytable t
        where not exists (select 1
                          from #myNewTable nt
                          where t.ID=nt.ID);
        select @i=@i+1;
    end

select * from #myNewTable;

IDtheoretical_cycle
11
21
31
41
51
61
71
81
92
102
112
122
132
142
153
163
173
183
193
203
214
224
234
244
254
264

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

lmeinke SSCommitted Points: 1956 More actions · Answer 3

Good Morning Steve,

Thank you so much for your suggestion and effort. I will get to this late this morning and update you this afternoon. I very much appreciate your willingness to help.

lmeinke SSCommitted Points: 1956 More actions · Answer 4

Hello Steve,

This gives me a start. The data provided were for one theoretical provider with one patient. In other words, the ID column pertains just to that provider/patient combination. I would need something to loop through a list of 80 individual providers with dozens of appointments per patient per provider to apply this to. I hate to think in terms of cursors or loops but that is where my brain is going at the moment. Thank you for your help and if you have any further ideas on cycling this logic through provider/patient combinations (roughly 55K records in the data set) I'd be happy to hear them.

Thanks

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

lmeinke SSCommitted Points: 1956 More actions · Answer 5

Hello Again Steve,

I had thought this code worked as designed but have found it does not take into account the 90 criteria in sequence (1st 90 days... 2nd 180 days....3rd 270 days etc...). I satisfied the initial request but have since found this error. I am anticipating having to do this with some frequency so am open to any modifications you might suggest. Since both conditions (>= 6 Appts and >= 90 days) must be met I thought changing the "or" to an "and" might work ... but this results in a primary key violation. I will plug away at this as time permits but also wanted to let you know ... just in case you live for a challenge :).

I ran these data painfully one by one but still need to fit this as an inner cursor loop inside an outer to truly make this functional.

Thank you,

Lonnie

Steve Collins SSCrazy Points: 2157 More actions · Answer 6

Hi Lonnie, thanks for the follow up. Since inside the WHILE loop the variable @i represents the theoretical cycle, perhaps it makes sense to change the WHERE clause to

WHERE rn<=6 or sum_days<=(@i*90);

But there are bigger issues imo. The scope seems to be creeping. Intentionally looping through tables in a relational database is very, very not recommended. It's equivalent to steering a car with your feet while taking a selfie. In this thread the first responses asked for additional clarification and to provide more data. That was actually a much better avenue to go down than having me toss in some loop-dee-do code. In my opinion it would be vastly better to take a step back and to consider the logical model. My policy is to try to assist wherever I can and to not disappoint anyone. But that's not always the best path forward for the questioner imo. In this case it seems vastly more advisable to provide enough information for recommendation of improvements to the logical model which would preclude the necessity for looping.

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

lmeinke SSCommitted Points: 1956 More actions · Answer 7

Thank you Steve. Your suggestion may work and I will give this some thought.

As to the rest of your text I absolutely agree. I have never written a cursor (that I actually used) in my life. But because of the dynamic updating required for this process I cannot find another way. Perhaps through a series of CTEs I suppose. Your opinion is greatly appreciated and couldn't agree more. There is a balance to find between too much information and too little. I guess I erred on the "too little" side.

Thank you,

Lonnie

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

Would you modify the sample data to include the "Theoretical Cycle Number", i.e. the desired cycle number you want added to each row.

I still don't fully understand all the criteria to be absolutely sure when a new cycle would begin, based on just the original raw data.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

lmeinke SSCommitted Points: 1956 More actions · Answer 9

Hello Scott,

I've added new data that is more reflective of the entire data set. There are fewer fields and now include the "Theoretical_Cycle" column (desired answer) you had asked for.

By simply adding the <=(@i * 90) you suggested the inner cursor now works correctly. In these "new" data you will notice a "UniqueChronID" column and a "UniqueID" column. Both of these represent the unique record set per "group" of data. These would be used to "grab" record set and pass it to the "inner loop" to determine the "Theoretical_Cycle". After determining this the code would return to the outer loop and grab the next record set.

In this example UniqueChronID 1-3 all contain is 6 records/rows per ID. This is run through the inner code and returns all "1"s.

UniqueChronID 4 would return 8 rows = 1

UniqueChronID 5 would return 8 rows = 1 and 6 rows = 2

UniqueChronID 6 would return 29 rows = 1, 6 rows = 2, 31 rows = 3, 8 rows = 4

All of these results would then be inserted into a table to be joined back to the original larger data set.

Thank you again for your effort Steve/Scott. It is very much appreciated.

I understand completely that cursors are NOT the ideal.

-Lonnie

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