SQL循环 [英] SQL Looping

问题描述

我正在使用Billboard公司的系统构建此报告. 他们有一个存储所有广告牌的表，除其他数据外，广告牌还具有开始日期和结束日期(都可以为null).
如果出于某种原因必须停止使用广告牌，则他们设置结束日期，并且该日期之后将无法使用.与开始日期相同(以防万一，他们只是设置了一个新板，在设置了开始日期之后可以使用).

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.

所以，可以说在我选择的时期内(例如4周)
*第1周有500个可用木板
*在第二周，一个板不可用(使498个板可用)
*在第3周，有两块木板可用(使501块木板可用)
*在第4周，一个板可用，而另一个板不可用(使501个板可用)

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)

因此，在此期间，我应该总共有1990个可用板，这就是我追求的结果.

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

如何在单个查询中获得此信息?
这些星期将来自HTML表单，我必须将它们转换为查询之前的日期，所以我只需要知道要多少个木板的日期即可.
获取一个特定日期的板数量的查询是这样的:

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')  

我不能只为每个日期添加AND和OR条件，因为我还需要为各个日期重新计数.我考虑过要使用WHILE，但无法弄清楚在这种情况下该怎么做.那就是我被困住的地方... 如果有人需要，我会发布更多详细信息.

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.

谢谢Gabe

推荐答案

不知道这是否是您要寻找的东西，但可能是朝着正确方向迈出的一步.

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  

这篇关于SQL循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！