SQL pivot based on the value between two columns

Question

I have a table in which I have, among other things, two columns, one for the start date and the other for the end date. I need to write a query that will return a column for each month of the year, and the value of this column is 1 if the month is between 0 otherwise. The PIVOT statement is similar to what I am looking for here, but, as far as I can tell, the PIVOT statement seeks to match the values, rather than check if the value is between the other two. Is the PIVOT clause the right construct here, or do I need to split and write 12 case statements and then aggregate them?

Answer 1

I think I have a solution here. There are 3 main steps:

• Get a date in each of 12 months
• Make sure this date is between the start and end dates.
• PIVOT result

To get 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 a table with the information that I really need. Then I use WHERE date BETWEEN start AND end to filter out any records that do not belong to this 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 select only those columns that we want to get as a result, or those that will be aggregated using the PIVOT statement.

, :

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

, , , , , , , .

Answer 2

12 .

, , , .

Answer 3

, ?

DatePart (, [])

?