SQL Pivot based on value between two columns

Question

I have a table in which I have among other things, two columns, one for start date and another for end date. I need to write a query which will return a column for each month of the year and the value of that column is 1 if the month is between 0 otherwise. The PIVOT statement seems to be what I am looking for here, but from the best I can tell the PIVOT clause is looking to match values, not check if value is between two others. Is the PIVOT clause the right construct here, or do I need to break down and write 12 case statements and then aggregate those?

Answer 1

I'd go with the 12 case statements.

That's actually the solution I sketched out before I went back and saw you mentioned in it your question.

Answer 2

I think I've got the solution here. There are 3 basic steps:

1. Get the a date in each of the 12 months
2. Check if this date is between the start and end date
3. PIVOT the result

To get the 12 dates, one for each month, I used a little recursive like WITH statement to create a temporary table with 1 column of 12 dates:

WITH months (date) AS (
SELECT GETDATE() AS date
UNION ALL
SELECT 
 DATEADD(MONTH,1,date)
FROM months
WHERE DATEDIFF(MONTH,GETDATE(),date) < 12)

From here I can CROSS JOIN this temporary table with the table with the information I really care about. Then I use WHERE date BETWEEN start AND end to filter off any entries which don't belong to that month. So something like this:

SELECT other.Title, MONTH(months.date) CROSS JOIN other
WHERE months.date BETWEEN other.start AND other.end

In this step we must be careful to only SELECT the columns we explicity desire in the result, or those which will be aggregated using the PIVOT statement.

Lastly, we must pivot the result:

PIVOT (MAX(PID) FOR date IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))

So the resultant query might look something like:

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

I've stripped out all of the other complexity I went through to join in other information I needed, so this isn't actually the query I wrote, but it should encapsulate the basic query structure that I used to get the result as I needed it.

Answer 3

Why not compute a column for the month and then use it to pivot?

datepart(month,[date])

or did I misunderstand the problem?