Avatar of GPSPOW
GPSPOW
Flag for United States of America asked on

SQL loop will not complete

Attached is the dataset I am using for the SQL nested loop to calculate patient length of stays for patients on a calendar day.

When I run the script, it never completes.

Please review the script and let me know if anyone sees a problem that keeps it from completing correctly.




  if OBJECT_ID(N'tempdb..#IPats') is not null
            Begin
                  drop table #IPats
            End

create table #IPats(
VisitID varchar (255),
AccountNumber varchar (255),
AdmitDate datetime,
Discharge datetime);
insert into #IPats
SELECT [VisitID]
      ,[AccountNumber]
      ,[AdmitDateTime]
      ,[DischargeDateTime]
 
  FROM [livedb].[dbo].[AbstractData]
  where AdmitDateTime>'2018-11-01' AND PtStatus='IN'


select * from #IPats


 if OBJECT_ID(N'tempdb..#LOSDATE') is not null
            Begin
                  drop table #LOSDATE
            End

create table #LOSDATE(
VID varchar (255),
LOSDATE datetime,
LOS int);

declare @CYB  as datetime;


select @CYB = '2018-10-30'
begin
while @CYB<getdate()
      Declare @n  INT;
      declare @i  int;
      select @n = count(*) from #IPats
      select @i=0
begin
while @i<@n
      insert into #LOSDATE(VID,LOSDATE,LOS)
          select PAT.AccountNumber,
            @CYB as LOSDATE,
               case when PAT.AdmitDate>@CYB then 0 else
                    case when PAT.Discharge<=@CYB then 0 else
                             case when PAT.Discharge is null then datediff(d,PAT.AdmitDate,getdate()) else
                                    Case when PAT.Discharge >= @CYB then datediff(d,PAT.AdmitDate,@CYB) else datediff(d,PAT.AdmitDate,PAT.Discharge) end end end end as LOS
             from #IPats PAT
             set @i=@i+1;
            

end
             set @CYB = @CYB+1;
end

select *
from #LOSDATE


Thank you

Glen
test2.xlsx
SQL

Avatar of undefined
Last Comment
GPSPOW

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
David Todd

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
GPSPOW

ASKER
It worked beautifully.
Thank you

Glen
Your help has saved me hundreds of hours of internet surfing.
fblack61