Generate a range of date and compare with another range of date and pivot it

Question

I have a situation where I have to generate a range of date (say start date and end date). Now I want to fill the records of specific dates on those generated dates.

;WITH DateRange AS
(
    SELECT @start_date DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM   DateRange
    WHERE  DateValue + 1 <= @end_date
)

If @start_date = '2013-01-01' and @end_date= '2013-01-05'

The dates generated will be '01/01/2013,01/02/2013,01/03/2013,01/04/2013,01/05/2013'

Now I want to pivot it to fill the specific data for specific date. How can I achieve this?

Edit: I have 3 columns namely Duration, StartDate and EndDate. Now I want to generate these dates dynamically and fill the values of Duration accourdingly.

For eg: if Duration = 6 and @start_date = '2013-01-01' and @end_date= '2013-01-05' I want to generate a list of dates with duration equal to 6.

Update 2:

If you haven't understand my question here's a full detail for it.

I have to pass @startDate and @EndDate as parameters
I have fields Id, StartDate and EndDate, duration and other fields in my table
Now when I pass the parameters the query should generate the range of dates from @startDate and @EndDate
As the dates are generated it must check the dates StartDate and EndDate in the table and set the value of Duration to those fields from StartDate to EndDate.

enter image description here

Required output: If @startDate='2013-01-01' and @endDate='2013-01-07' then the output must be like this:

enter image description here

**

Note: the Id for both the rows in first image is same.

**

Update 3:

enter image description here

Pivoting data into a dynamic number of columns requires dynamic SQL. There are many such questions and answers here on StackOverflow, here's the first one I found... Possible duplicate of [Dynamic pivot in SQL Server](http://stackoverflow.com/questions/16759531/dynamic-pivot-in-sql-server) — MatBailie, Dec 10 '13 at 11:29
@sna2stha - is that correct, that's what you're looking for? You want the dates to be _columns_? Note that if you only ever need a specific amount (like, say, the last 30 days), this doesn't have to be dynamic: just get the current date - 1, etc (now, the _labels_ won't match, but if you have a different language actually doing the front-end reporting, that can usually handle it). — Clockwork-Muse, Dec 10 '13 at 11:47
@Clockwork-Muse: I want to generate dates from specific dates it may be weeks, months or even may be years. — Suraj Shrestha, Dec 10 '13 at 11:53
... not what I was asking. "Pivoting" is a specific term in SQL/Data terminology - simply, it's about swapping rows for columns (this is often used for charts/reporting purposes). It's trivial to generate a range of dates as rows; so, do you want those dates to appear as columns? Note that all RDBMSs have some upper limit on the number of columns allowed. Although usually there's a finite limit to what _humans_ can process... — Clockwork-Muse, Dec 10 '13 at 12:01
Personally, anything over a month is pushing it; it'd be best to stay with a week or less. What are you doing that you need **years** worth of columns? If you attempt something like that, you _will_ be hitting system limitations at some point (the human ones having already been reached several times over) — Clockwork-Muse, Dec 10 '13 at 12:13
@Clockwork-Muse: I am working on reporting so I need up to a year. — Suraj Shrestha, Dec 10 '13 at 12:16
If you want to do reporting, you're using the wrong tool. It'll be way better to use SQL Server reporting services. — flup, Dec 18 '13 at 08:27
@flup: Although I'm working for reporting but how do I solve the above problem. Will you provide me a solution for that. — Suraj Shrestha, Dec 18 '13 at 09:00
In such a case, I'd not do it in sql, but handle it in an application code layer. — Allan S. Hansen, Dec 18 '13 at 11:54
@sna2stha if you try it first, and run into problems, sure. But right now you're hammering a nail with a rock. Try the hammer first. — flup, Dec 18 '13 at 12:00

Brett Schneider · Accepted Answer · 2013-12-19T11:12:33.280

[updated 2013-12-18 11:22 UTC]
[updated 2013-12-18 14:19 UTC]
[updated 2013-12-19 11:11 UTC]

as i did not know the name of the table you are using, i created one named yeahyeah. you should replace that with the name of your own table obviously. i have inserted the values you mentioned (fromdate todate duration as shown in your question).

the procedure should look like this. i called it pivotit.

create procedure pivotit (@start_date date, @end_date date)
as

/*
step 1:
identify all the data you need to fill the pivoted table.
this is achieved by using your daterange generator and joining
it with your table. the result is kept in a dynamic table
*/

declare @acols table (i int, d date, l int);
;WITH DateRange AS
(
    SELECT @start_date DateValue
    UNION ALL
    SELECT dateadd(dd,1,DateValue)
    FROM   DateRange
    WHERE  dateadd(dd,1,DateValue) <= @end_date
)
insert into @acols (i, d, l)
select id, DateValue, Duration from DateRange
join yeahyeah on (    DateRange.DateValue >= yeahyeah.FromDate 
                  and DateRange.DateValue <= yeahyeah.ToDate);

/* 
step 2:
for pivot you need all the columns that will be adressed. so
we create a string with all the distinct dates from the dynamic 
table. these will then be put into a format like [1], [2], [3], ...
to create a dynamic select.
*/


declare @p varchar(max) = '';
declare @s varchar(max);

select @p = @p + ', [' + CONVERT(varchar,d) + ']' from (select distinct d from @acols) a;
set @p = SUBSTRING(@p,3,len(@p)-2);

/*
step 3:
create the dynamic select. 
alas neither the dynamic table nor the parameters are available from 
inside the dynamic sql. i might try to use bind variables, but was 
not 100% sure if that would work here. so put in the declares for start_ 
and end_date from the procedure parameters and build up the dynamic table 
once more.

then i use @p for the pivoted select. this is done by selecting the column
for the rows (id) and all the values from the pivot as columns (@p).

details on the whole pivot thing are here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
basically you tell sql-server (explicitly) what you want as the columns (@p),
what rows you want (id) and how to aggregate the values in the intersections
(sum(l))

[update 2013-12-19]
i added a routine that makes a cartesian product that has all the combination
of dates and ids in @acols, finds the ones that are missing in @acols and inserts
them with duration 0. then the pivoted cells are complete and display the zero
instead of NULL. you cannot use isnull or coalesce here since not the value
of the cell is NULL, the intersection simply did not exist.

*/

set @s = '
declare @start_date date = convert(date,'''+CONVERT(varchar,@start_date,112)+''',112);
declare @end_date date = convert(date,'''+CONVERT(varchar,@end_date,112)+''',112);
declare @acols table (i int, d date, l int);
;WITH DateRange AS
(
    SELECT @start_date DateValue
    UNION ALL
    SELECT dateadd(dd,1,DateValue)
    FROM   DateRange
    WHERE  dateadd(dd,1,DateValue) <= @end_date
)
insert into @acols (i, d, l)
select id, DateValue, Duration from DateRange
join yeahyeah on (    DateRange.DateValue >= yeahyeah.FromDate 
                  and DateRange.DateValue <= yeahyeah.ToDate);

with cart as 
(
     select distinct 
            a.i
          , b.d 
       from @acols a 
       join @acols b 
         on 1=1
)
insert into @acols (i, d, l)
select cart.i
     , cart.d
     , 0
  from cart 
  left outer join
       @acols a
    on cart.i = a.i
   and cart.d = a.d
 where a.i is null;

select id, '+@p+'
       from
( select convert(varchar,d) as d
       , l
       , i as id
    from @acols ) as sourcetable
  pivot ( 
    sum(l)
    for d in ('+@p+')
    )  as pivottable';

execute(@s);

after you created the procedure you can do this:

exec pivotit @start_date = '2013-01-01', @end_date = '2013-01-31'

which will then yield:

result with 0 and no zeroes

It shows The column '2013-08-01' was specified multiple times for pivottable @ line 30. — Suraj Shrestha, Dec 18 '13 at 10:41
that means in your table there are two durations for `'2013-08-01'`. what do you want to do when there are two durations for the same day? — Brett Schneider, Dec 18 '13 at 10:51
the two dates means there are two different ids for same date. and this date with different id has different duration so i want to fill this different duration on the same date next row. — Suraj Shrestha, Dec 18 '13 at 11:02
Thank you for your answer. I have been searching for it for three days. You have saved my rest time searching for it. Thank you again. Will you make me understand in simple steps of working this code. — Suraj Shrestha, Dec 18 '13 at 11:36
i put in a few comments and made a few corrections to my code. — Brett Schneider, Dec 18 '13 at 14:20
i need to ask a last question replacing those null values with zero. i tried isnull(l, 0 ) as l. but not converted. — Suraj Shrestha, Dec 19 '13 at 09:50
hi there, i added a little bit of code that will add the missing intersections in the pivot so they will display 0s instead of NULLs. — Brett Schneider, Dec 19 '13 at 11:13
hi @brett, I have problem same with this but different approach. can you check on this http://stackoverflow.com/questions/21569473/pivotting-date-range-using-sql — user1647667, Feb 05 '14 at 06:41

score 0 · Answer 2 · answered Dec 10 '13 at 11:51

You can write a query as below:

declare @start_date datetime,@end_date datetime ;
set @start_date ='2013-01-01' ;
set @end_date = '2013-01-05' ;
DECLARE @columns NVARCHAR(MAX),@sql NVARCHAR(MAX);
SET @columns = N'';

WITH DateRange AS
(
    SELECT @start_date DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM   DateRange
    WHERE  DateValue + 1 <= @end_date
)
--Get column names for entire pivoting
SELECT @columns += N', ' + QUOTENAME(SpreadCol)
FROM (select distinct convert(varchar(10),DateValue,101) as SpreadCol 
from DateRange
) AS T;  

PRINT @columns;

and then use @columns as spreading columns in dynamic Pivot query.

Thank you for the answer but the answer I required is not as you have provided. Please review my updates. — Suraj Shrestha, Dec 18 '13 at 05:08

score 0 · Answer 3 · edited May 23 '17 at 10:32

Use the SQL Server Reporting Services wizard.

In the wizard, set the query to the Left Join of the date range table with the data table.
As report type choose a matrix.
Assign the dates to the columns area, the row ids of your data table to the row area, the value column of your data table to the value area.
Choose a style
Generate the report.

If the report needs to be generated regularly, you can publish it on the reporting server. Users can export to a preferred format. It'll look nice too ;)

You can graph the results too if you like.

See http://technet.microsoft.com/en-us/library/ms160326(v=sql.105).aspx for more info on the report wizard.

See SQL query to select dates between two dates for alternative ways to generate the date range table.

Generate a range of date and compare with another range of date and pivot it

Note: the Id for both the rows in first image is same.

3 Answers3

Linked