AnswerBun.com

SQL query to loop through records

Stack Overflow Asked by Doe on December 22, 2020

I have a table with a million records. This is the structure of the table with some example data points –

patient   claim   thru_dt   cd   start     
322       65      20200201  42   20181008  
322       65      20200202  42             
322       95      20200203  52             
122       05      20200105  23             
122       05      20200115  42   20190102  
122       05      20200116  42            

I need to write a query that would produce this output –

patient   claim   thru_dt   cd   start     
322       65      20200201  42   20181008    
322       65      20200202  42   20181008    
322       95      20200203  52   20181008    
122       05      20200105  23               
122       05      20200115  42   20190102    
122       05      20200416  42      

The reason the second claim of patient 322 was given 20181008 is because both the first and the second one have the same cd value.

The reason the third claim of patient 322 was also given 20181008 value even though it doesn’t have the same cd value is because it is the last claim for the patient.

The reason the first claim of patient 122 is still a NULL is because that claim’s cd value is not equal to 42.

The reason the third claim of patient 122 was NOT given a value of 20190102 even though it has the same cd value is because the thru_dt in their prior claim is more than 30 days apart.

This is what I have tried so far –

--This orders claims using row_number
DECLARE @min_record int;
DECLARE @max_record int;

select 
    @min_record = MIN(row_num), 
    @max_record = MAX(row_num) 
from 
(
    select *, 
    row_number() over(partition by patient order by thru_dt) as row_num
    from 
    table
)

while @min_record <= @max_record
begin 
    --Logic I need help with 
    SET @min_record = @min_record + 1
end 

One Answer

I think a lateral join and conditional expressions make it simpler to implement the logic you want:

select t.*,
    case 
        when t.start is null and (
            s.cd = t.cd 
            or row_number() over(partition by t.patient order by t.thru_dt desc) = 1
        )
        then s.start
        else t.start
    end new_start
from mytable t
outer apply (
    select top (1) s.*
    from mytable s
    where 
        s.patient = t.patient 
        and s.start is not null
        and s.thru_dt >= dateadd(day, -30, t.thru_dt)
    order by s.thru_dt desc
) s
order by patient desc, thru_dt

Demo on DB Fiddle:

patient | claim | thru_dt    | cd | start      | new_start 
------: | ----: | :--------- | -: | :--------- | :---------
    322 |    65 | 2020-02-01 | 42 | 2018-10-08 | 2018-10-08
    322 |    65 | 2020-02-02 | 42 | null       | 2018-10-08
    322 |    95 | 2020-02-03 | 52 | null       | 2018-10-08
    122 |     5 | 2020-01-05 | 23 | null       | null      
    122 |     5 | 2020-01-15 | 42 | 2019-01-02 | 2019-01-02
    122 |     5 | 2020-04-16 | 42 | null       | null      

Correct answer by GMB on December 22, 2020

Add your own answers!

Related Questions

Efficiently Zip multiple list in python/pandas

3  Asked on December 14, 2020 by sunni

   

Pandas Excel groupby/count

1  Asked on December 14, 2020 by nathaniel

   

How to call a php function in javascript using Ajax?

2  Asked on December 14, 2020 by mr-skan

     

Angular class names based on item values

2  Asked on December 14, 2020 by mafortis

     

is there any way to give name object?

1  Asked on December 14, 2020 by beginner-coder

 

HTML hidden not submitting

2  Asked on December 14, 2020 by connor-gaymon

     

how to find and remove array using jquery

1  Asked on December 13, 2020 by sultan-achmad

   

Laravel GraphQL and Sanctum – how to combine it?

0  Asked on December 13, 2020 by kamilon123s

     

How to save multiple figures built with a loop and mapplot packages in r?

1  Asked on December 13, 2020 by juan-carlos-rubio-polania

         

Pick from both sides?

2  Asked on December 13, 2020 by helloworld

       

how to return only the True values?

2  Asked on December 13, 2020 by hnakashima96

       

multiple image galleries on one page

2  Asked on December 13, 2020 by flw

     

System.MissingMethodException: Method not found?

34  Asked on December 13, 2020 by user603007

         

Use auto for only one variable with structured binding

3  Asked on December 13, 2020 by hkon-hgland

   

What does the & symbol mean here and whats going on here?

4  Asked on December 13, 2020 by aarat-chopra

   

How to get suffix of filename in javascript quickly?

2  Asked on December 13, 2020 by daniel-stephens

 

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP