SQL loop will not complete

GPSPOW
GPSPOW used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Senior Database Administrator
Commented:
Hi,

I don't think the inner loop is necessary - so I've commented the looping out. As your data set was 692 rows it was inserting 692 rows for each i. Menas you have a massive results set.

You had the begins on the loops in the wrong places.

The case statement was badly written

I've added a debug print or two.

HTH
  David
use EE	
go

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

create table #IPats(
	VisitID varchar (255),
	AccountNumber varchar (255),
	AdmitDate datetime,
	Discharge datetime
);

insert into #IPats
SELECT [VisitID]
      ,[AccountNumber]
      ,[AdmitDateTime]
      ,[DischargeDateTime]
  
FROM [dbo].[AbstractData]
where 
	AdmitDateTime>'2018-11-01' AND PtStatus='IN'


select * from #IPats


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

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

declare @CYB  as datetime;


select @CYB = '2018-10-30'

while @CYB<getdate() begin
	Declare @n  INT;
	declare @i  int;
	select @n = count(*) from #IPats
	select @i=0

	--while @i<@n begin
		insert into #LOSDATE(VID,LOSDATE,LOS)
		select 
			PAT.AccountNumber
			, @CYB as LOSDATE
			, case 
				when PAT.AdmitDate>@CYB then 0 
				when PAT.Discharge<=@CYB then 0 
				when PAT.Discharge is null then datediff(d,PAT.AdmitDate,getdate()) 
				when PAT.Discharge >= @CYB then datediff(d,PAT.AdmitDate,@CYB) else datediff(d,PAT.AdmitDate,PAT.Discharge)    
			end as LOS
		from #IPats PAT
		;

		--set @i=@i+1;
            
		--print @i

	--end

	set @CYB = @CYB+1;

	print @CYB 
end

select * 
from #LOSDATE

Open in new window

GPSPOW

Author

Commented:
It worked beautifully.
Thank you

Glen

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial