SQL Looping

Question

I'm building this report in a system for a Billboard company. They have a table that stores all their billboards, and among other data, billboards have a Start Date and a Finish Date (both can be null).
If for some reason a billboard has to stop beeing used, they set a finish date and it will become unnavaiable to be used after that date. Same with Start date (in case they just set up a new board and it will be avaiable for use after a set Start Date).

In this report I have to get the sum of all possible advertising spots in a given period of time.

So, lets say that for the period I chose (4 weeks, for exemple)
* In week 1 there are 500 avaiable boards
* In week 2 one board became unavaiable (making 498 boards avaiable)
* In week 3 two boards became avaiable (making 501 boards avaiable)
* In week 4 one board became avaiable and one other became unavaiable (making 501 boards avaiable)

So then I should have a total of 1990 avaiable boards in this period, that's the result I'm after.

How can I get this in a single query?
The weeks will come from a HTML form and I have to convert them to dates before the query, so I'll just have the dates I want to know how many boards are avaiable at.
The query to get the amount of boards for ONE specific date is like this:

SELECT  
  COUNT(IdBillboard)  
FROM  
  tBillboards  
WHERE  
  (StartDate IS NULL OR StartDate <= '2009-01-05 00:00:00')  
  AND (FinishDate IS NULL OR FinishDate >= '2009-01-05 00:00:00')

I can't just add AND and OR conditionals for each date because I kinda need a new recount for the separate dates. I thought about using a WHILE but couldn't figure out exactly how to do it for this case. So that's where I'm stuck... I'll post more details if anyone needs them.

Thanks, Gabe

Answer 1

Don't know if this is what you're looking for, but could be a step in the right direction.

SELECT  
  SUM(CASE WHEN 
   (StartDate IS NULL OR StartDate <= '2009-01-05 00:00:00')  
      AND (FinishDate IS NULL OR FinishDate >= '2009-01-05 00:00:00')
    THEN 1
    ELSE 0 END)) Week1Count,
  SUM(CASE WHEN 
   (StartDate IS NULL OR StartDate <= '2009-01-12 00:00:00')  
      AND (FinishDate IS NULL OR FinishDate >= '2009-01-12 00:00:00')
    THEN 1
    ELSE 0 END)) Week2Count
FROM  
  tBillboards

Answer 2

If you can get the dates from the form into a (possibly temporary) table then your query can be:

SELECT  
  COUNT(t.IdBillboard)  
FROM  
  tBillboards r, tDates d
WHERE  
  (t.StartDate IS NULL OR StartDate <= d.date)  
  AND (t.FinishDate IS NULL OR FinishDate >= d.date)

Answer 3

SELECT  COUNT(*)
FROM
    (
    SELECT DATEADD(ww, num, @initdate) AS wd
    FROM (
     SELECT 0 AS num
     UNION ALL
     SELECT 1
     UNION ALL
     SELECT 2
     UNION ALL
     SELECT 3
     ) w
    ) weeks, Billboards bb
WHERE wd BETWEEN NULLIF(bb.StartDate, wd) AND NULLIF(bb.EndDate, wd)

Answer 4

It's not an answer to your question, but you should consider using BETWEEN instead of the ANDs.

WHERE  
  (StartDate IS NULL) OR 
  StartDate BETWEEN '2009-01-05' AND '2009-01-05'

IMO this is much more readable.

Answer 5

There is no WHILE in SQL. To loop, you join to another table.

You don't specify it but I suppose that billboards change availability on one day of the week, say Sunday, and they can't change multiple times per week.

What you need to do is to create a table of all possible weeks when this change can happen, let's call it DT for date, with a single date column x that has one date per week (Sunday?).

Then do something like this:

select count(*) from tBillboards b
inner join DT d
    on (b.startdate is null or b.startdate <= d.x)
       and (b.finishdate is null or b.finishdate < d.x)
where d.x between period_start and period_end

Answer 6

Assuming you are using SQL Server 2005 or later, you can do this using a CTE as in the following example. In my example you need to set a date and the number of weeks that you wish to look back from this date:

DECLARE @dtNow DATETIME
DECLARE @iNumberOfWeeks INT
SET @dtNow = '2009-01-21'
SET @iNumberOfWeeks = 2;


WITH CountForPeriod (IdBillBoard, StartDate, FinishDate, NumberOfWeeksBack) AS
(
    SELECT IdBillBoard, StartDate, FinishDate, 1 AS NumberOfWeeksBack
    FROM tBillBoards
    WHERE (StartDate <= @dtNow)
    AND (FinishDate >= @dtNow)

    UNION ALL

    SELECT b.IdBillBoard, b.StartDate, b.FinishDate, 1 + NumberOfWeeksBack
    FROM tBillBoards b
    JOIN CountForPeriod c
    ON (b.StartDate <= DATEADD(dd, -7 * c.NumberOfWeeksBack, @dtNow))
    AND (b.FinishDate >= DATEADD(dd, -7 * c.NumberOfWeeksBack, @dtNow))
)
SELECT NumberOfWeeksBack, COUNT(*)
FROM CountForPeriod
WHERE NumberOfWeeksBack <= @iNumberOfWeeks
GROUP BY NumberOfWeeksBack

Answer 7

This will give you the total for the first 4 weeks in 2009. To check for another year, change the two occurrences of 2009 in the query below to the year you are interested in.

select sum(Count) as Total
from (
    SELECT w.Week, Count(*) as Count
    FROM tBillboards b
    inner join (
     select 1 as Week
     union all
     select 2
     union all
     select 3
     union all
     select 4
    ) w on StartDate is null or datepart(ww, StartDate) <= w.Week
     and FinishDate is null or datepart(ww, FinishDate) >= w.Week
    where isnull(year(StartDate), 2009) <= 2009
     and isnull(year(FinishDate), 2009) >= 2009
    group by w.Week
) a

If you need more than the first 4 weeks, continue to add numbers to the inner query with the UNIONs in it.