Cursor and while loop in the same procedure sql server-CodePudding

first let me explain my schema simply a doctor write a prescription for a patient every prescription have multiple drugs each drug has a frequency (Taken every X hour) and also has a start and end i have added SQL Fiddle of my tables

my requirement is loop through the table and for every prescription
create a report (Stored Procedure)

ex patient 1 has drug 1 every 6 hours for 2 days from 20-11-2021 20:00

Patient  Drug    Dose 
1          1      1 / 20-11-2021 20:00 (Dose Number / Dose Datetime)
1          1      2 / 21-11-2021 02:00  (Add 6 hour to get next dose)
1          1      3 / 21-11-2021 08:00 
1          1      4 / 21-11-2021 14:00

this is my sample query

DECLARE @minDate datetime , @MaxDate datetime,
@CurrDate datetime,@index int,@hour int 
SELECT @minDate = min(startdate) , @MaxDate = max(EndDate) ,
@hour=max(Frequency.HourValue)  
FROM Prescription_Detail inner join Frequency on     
Prescription_Detail.FreqID= Frequency.Id 
WHERE Prescription_Detail.Id = 1
set @CurrDate= @minDate set 
@index=1 while @CurrDate<=@MaxDate begin PRINT CONVERT(varchar, @index)  '/' CONVERT(VARCHAR,@CurrDate) set @CurrDate= DATEADD(HOUR,@hour,cast(@CurrDate as datetime)) set @index=@index 1 end

as Per the first comment i will be more specefic
first table Patients
second table Prescription (one to one with patient)
third table prescription detail (detail for table prescription )
this table has the drug and its start date(date field) start time (time field) end Date, end time , frequencyID (one to one with table frequency) fourth table frequency has the number of hours
My requirement to for every drug based on start date and time and frequency and end date and time
calculate number of dosage and the time for every dosage
I have updated Fiddler
Thank You

CodePudding user response：

You don't need cursor or while loop. All you need is a number / tally table.

You can use permanent one or dynamically create one using recursive cte. The query below uses a permanent number table

the cross apply is to calculate the StartDateTime from the StartDate and StartTime to simplified further calculation

From the StartDateTime and EndDateTime together with the dose frequency you can calculate the total number of dose required

select p.id, d.DrugID, 
       Dose_No = n.n   1,       
       Dose_DateTime = dateadd(hour, n * f.HourValue, dt.StartDateTime)
from   Patient p
       inner join Prescription r on r.PatientID = p.id
       inner join Prescription_Detail d on d.PrescriptionID = r.id
       inner join Frequency f on d.FreqID = f.id
       cross apply
       (
           select StartDateTime = cast(d.StartDate as datetime)   cast(d.StartTime as datetime),
                  EndDateTime   = cast(d.EndDate as datetime)   cast(d.EndTime as datetime)
       ) dt
       inner join number n on n.n <= datediff(hour, dt.StartDateTime, dt.EndDateTime) / f.HourValue
order by p.id, d.DrugId, Dose_no

fiddle