SQL Current status for given day (FOR loop)

Question

For simplicity lets assume that I have a view with three fields

date_in (date)
Container (varchar)
date_out (date)

Now, the container is IN if the date_in is lesser or equal to given date and date_out is null or greater than given date. Now I am trying to count the containers for given time period. In pseudocode between two values STARTDATE and ENDDATE it would be something like

FOR X =STARTDATE, X<= ENDDADE, X++ {
if date_in <=X and date_out>x
count (container) 
}

or closer to SQL:

declare  @startdate date,
@d date;
set  @startdate = '1/01/2014'
set @d = @startdate
"FOR on the @d variable would go here" {



select @d as SNAP_DATE, count (container) where date_in <@d 
and (date_out is null or   date_out> @d)

}

It might be simple - I guess I could make a new table and manually do multiple SELECT INTO (and later query from this new table) but its not very elegant solution.

Edit: just to precise - in the end I'd like to have something like:

DATE      Count
1/02/2014   10
2/02/2014   15
 ...
7/03/2014   19

shree.pat18 · Answer 1 · 2014-04-08T03:53:21.013

0

You could do this procedurally as follows:

Use a while loop to loop from start to end date.
Use a table variable to store each date-count pair.

Select from the table variable to get the summarised result.

declare @start date = '1/01/2014'
declare @end date = '7/03/2014'
declare @tbl table(Date date, Count int)

while(@start < @end)
 begin
  insert into @tbl
  select @start, count(*)
  from your_view
  where (in_date < @start) 
  and ((out_date is null) or (out_date > @start))

  set @start = dateadd(day, 1, @start)
 end

select * from @tbl

edited Apr 08 '14 at 03:53

answered Apr 08 '14 at 02:51

shree.pat18

21,449
3
43
63

Yes, but that would give me value for one day, I need for a whole period. – Yasskier Apr 08 '14 at 03:00
Would it help if you passed a second parameter and compared that with output date i.e. compare start of range with in date and end with out date? – shree.pat18 Apr 08 '14 at 03:03
Not really sure what you mean: In SQL I have Declare startdate date D date; set startdate = '1/01/2014' set d=startdate select d, count (container) from VIEW where date_in<= d and time_out.... Now I have to keep changing the @D in the FOR loop somehow – Yasskier Apr 08 '14 at 03:14

score 0 · Answer 2 · edited May 23 '17 at 12:28

You might be able to do something like the following. It uses a numbers table, which can be a real or derived table. It contains rows of integers. You need a table that begins with 0 and has enough values to cover your date range. Check here for more information on a numbers table.

DECLARE @StartDate DATE = '1/2/2014'
DECLARE @EndDate DATE = '1/4/2014'

SELECT DATEADD(d, n.num, @StartDate) AS DATE, COUNT(*) AS COUNT
FROM Numbers n
JOIN MyView mv ON mv.date_in < DATEADD(d, n.num, @StartDate)
    AND (mv.date_out IS NULL OR mv.date_out > DATEADD(d, n.num, @StartDate))
WHERE DATEADD(d, n.num, @StartDate) BETWEEN @StartDate AND @EndDate
GROUP BY DATEADD(d, n.num, @StartDate)
ORDER BY DATEADD(d, n.num, @StartDate)

The numbers in the numbers table are converted to the list of dates between the date range. Each date is joined to your view based on the criteria you need.

SQL Current status for given day (FOR loop)

2 Answers2