Pivot table between two dates using SQL Server 2008 R2

Question

I have the following table:

Example:

create table test
(
  col_dt1 date,
  col_dt2 date
)

Inserting some records:

insert into test values('2014-11-07','2014-12-01');

select * from test;

col_dt1     col_dt2
----------------------
2014-11-07  2014-12-01

Expected Result:

col_dt1     col_dt2    07 November 2014  08 November 2014 .................... 01 December 2014
-----------------------------------------------------------------------------------------------
2014-11-07  2014-12-01       1                 0          ....................        1

I got stuck to get all dates between two dates to have the stuff column in pivot table.

@Pradeep, There are two dates understand that is first and last dates given as 2014-11-07 and 2014-12-01. I want to show the pivot table for these dates starting from 2014-11-07 to 2014-12-01. It has to show all the dates between these two dates in columns. — Sarfaraz Makandar
– Sarfaraz Makandar, Commented Nov 7, 2014 at 7:08
so u need value of 1 under those two dates, and for other dates value should be 0 — Pரதீப்
– Pரதீப், Commented Nov 7, 2014 at 7:11
@Meem What if your table contains one more row, e.g. (2014-01-01, 2014-01-31). What is the desired output in this case? — Giorgos Betsos
– Giorgos Betsos, Commented Nov 7, 2014 at 7:18
@Meem In this case you might look into using a table valued function. Pivot is used to rotate table-valued expressions. What you have as an input is not a table-valued expressions. It simply is two scalar dates: a start date and an end date. — Giorgos Betsos
– Giorgos Betsos, Commented Nov 7, 2014 at 7:25

Praloy Das · Accepted Answer · 2014-11-07 11:03:06Z

DECLARE @Start_Date DATE, @End_Date DATE, @QUERY NVARCHAR(MAX), @SUBQUERY NVARCHAR(MAX), @ROWCOUNT int

CREATE TABLE #TempTable(
ID int IDENTITY(1,1) NOT NULL,
Start_Date date,
End_Date date, 
Dates NVARCHAR(50),
HasDate int
)
CREATE TABLE #TempTable2(
Start_Date date,
End_Date date, 
Dates NVARCHAR(50), 
HasDate int
)

SET @Start_Date = (SELECT TOP 1 col_dt1 from test)
SET @End_Date = (SELECT TOP 1 col_dt2 from test)

INSERT INTO #TempTable
SELECT 
@Start_Date as Start_Date,
@End_Date as End_Date,
RIGHT(REPLICATE('0', DAY(DATEADD(DAY,number,@Start_Date))) + CAST(DAY(DATEADD(DAY,number,@Start_Date)) AS NVARCHAR(2)), 2)
+' '+DATENAME(MONTH,DATEADD(DAY,number,@Start_Date))+' '+CONVERT(NVARCHAR(50),YEAR(DATEADD(DAY,number,@Start_Date))) as Start_Date,
HasDate =
      CASE 
         WHEN DATEADD(DAY,number,@Start_Date)=@Start_Date THEN 1
         WHEN DATEADD(DAY,number,@Start_Date)=@End_Date THEN 1
         ELSE 0
      END
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number,@Start_Date) <= @End_Date
INSERT INTO #TempTable2 SELECT [Start_Date],[End_Date],[Dates],HasDate FROM #TempTable

SELECT * FROM #TempTable

SET @QUERY=''
SET @QUERY+='SELECT * FROM #TempTable2
                PIVOT
                (
                    Max(HasDate)
                    FOR Dates IN ('

SET @SUBQUERY=''
SET @ROWCOUNT=1                 
WHILE @ROWCOUNT <= (SELECT COUNT(*) FROM #TempTable)
BEGIN
    SET @SUBQUERY=@SUBQUERY+'['+(SELECT CONVERT(NVARCHAR(50),Dates)as Dates FROM #TempTable WHERE ID=@ROWCOUNT)+']'
    IF (@ROWCOUNT<>(SELECT COUNT(*) FROM #TempTable))
    BEGIN
        SET @SUBQUERY=@SUBQUERY+','
    END
    SET @ROWCOUNT=@ROWCOUNT+1
END             
SET @QUERY=@QUERY+@SUBQUERY+')
                )AS tblPivot'


PRINT(@QUERY)
EXECUTE(@QUERY)

DROP TABLE #TempTable
DROP TABLE #TempTable2

You can try this.

Collectives™ on Stack Overflow

Pivot table between two dates using SQL Server 2008 R2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related