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
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
It worked beautifully.
Thank you

Glen
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.