Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag 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
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GPSPOW

ASKER

It worked beautifully.
Thank you

Glen