We help IT Professionals succeed at work.

SQL loop will not complete

GPSPOW
GPSPOW asked
on
60 Views
Last Modified: 2019-01-06
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
Comment
Watch Question

Senior Database Administrator
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
It worked beautifully.
Thank you

Glen