我有一个表格,在其中有两列,一列为开始日期,另一列为结束日期。我需要编写一个查询,返回每年的每个月份以及该列的值是1,如果月份在开始和结束日期之间,否则为0。似乎PIVOT语句是我在这里寻找的,但从我最好的理解来看,PIVOT子句正在寻找匹配的值,而不是检查值是否介于两个值之间。PIVOT子句是正确的构造吗?还是我需要拆分并编写12个case语句,然后汇总它们?
我认为我已经找到了解决方案。有三个基本步骤:
为了获取12个日期,即每个月的一个日期,我使用了递归WITH语句创建了一个临时表,其中包含12个日期的1列:
WITH months (date) AS (
SELECT GETDATE() AS date
UNION ALL
SELECT
DATEADD(MONTH,1,date)
FROM months
WHERE DATEDIFF(MONTH,GETDATE(),date) < 12)
我可以使用临时表与我真正关心的信息表进行 CROSS JOIN。然后使用 WHERE date BETWEEN start AND end 来过滤掉不属于该月份的条目。类似这样:
SELECT other.Title, MONTH(months.date) CROSS JOIN other
WHERE months.date BETWEEN other.start AND other.end
PIVOT (MAX(PID) FOR date IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
WITH months (date) AS (
SELECT GETDATE() AS date
UNION ALL
SELECT
DATEADD(MONTH,1,date)
FROM months
WHERE DATEDIFF(MONTH,GETDATE(),date) < 12)
SELECT Title,
[1] AS January,
[2] AS February,
[3] AS March,
[4] AS April,
[5] AS May,
[6] AS June,
[7] AS July,
[8] AS August,
[9] AS September,
[10] AS October,
[11] AS November,
[12] AS December
FROM
(
SELECT other.Title,MONTH(months.date)
CROSS JOIN other
WHERE months.date BETWEEN other.startDate AND other.endDate
) AS subquery
PIVOT (MAX(PID) FOR date IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p
我已经剥离了所有其他复杂性,以便加入我需要的其他信息,所以这实际上不是我编写的查询,但它应该概括了我用来获得所需结果的基本查询结构。
我会选择12个case语句。
实际上,在我回去看到你在问题中提到它之前,我就已经勾画出了这个解决方案。