List of dates from while loop

Question

I have the following while loop:

DECLARE @Date Date

DECLARE @Trucks INT

DECLARE @MaxTrucks INT

SET @Date = DATEADD(mm, -1,DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0)) 

SET @Trucks = 0

SET @MaxTrucks = 0

WHILE @Date < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 

BEGIN
SET @Trucks = (

SELECT COUNT(DISTINCT([TruckID]))

FROM TruckAvailability

WHERE CONVERT(Date,PlannedStartDT) <= @Date and CONVERT(Date,PlannedEndDT) >=

 @Date and TotalTicks > 0)

IF @Trucks > @MaxTrucks BEGIN SET @MaxTrucks = @Trucks END

SET @Date = DATEADD(day,1,@Date)

END

What I need right now is MaxTrucks per Date. So a column with dates and a column with corresponding MaxTrucks.

Kind regards

More often than not, loops can be (and should be) avoided. Sample data and desired results would be more helpful. — John Cappelletti, May 01 '17 at 14:57

score 1 · Answer 1 · answered May 01 '17 at 15:21

You can use a calendar or dates table for this sort of thing.

For only 152kb in memory, you can have 30 years of dates in a table with this:

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK  cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date on dbo.Dates([Date]);

Without taking the actual step of creating a table, you can generate an adhoc tables of dates using a common table expression with just this:

declare @fromdate date = dateadd(month, datediff(month, 0, getdate() )-1, 0);
declare @thrudate date = dateadd(day,-1,dateadd(month, datediff(month, 0, getdate() ), 0));
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

Use either like so:

select top 1
    d.Date
  , MaxTrucks = count(distinct(ta.TruckId)) desc
from TruckAvailability ta
  inner join dates d
    on d.Date >= convert(date,ta.PlannedStartDT) 
   and d.Date <= convert(date,ta.PlannedEndDT)
where ta.TotalTicks > 0
group by d.Date
order by count(distinct(ta.TruckId)) desc

Number and Calendar table reference:

List of dates from while loop

1 Answers1