Pivot In Values with dates?
At the end of my sql, I am using the following code. Is there any way of replacing the fixed strings [2011/07/14], [2011/07/16], etc, to GetDate() value?
PIVOT ( count([AppointmentsBooked]) FOR [date] IN ([2011/07/14], [2011/07/16], [2011/07/17],[2011/07/18],[2011/07/21]) ) as pivottable
Can you try to use BETWEEN and DATEADD in following manner:
DECLARE @dates TABLE(value DateTime) INSERT INTO @dates VALUES (GETDATE()), ('2011/07/9'), ('2011/07/17'), ('2011/07/18'), ('2011/07/21') SELECT value FROM @dates WHERE value BETWEEN GETDATE() AND DATEADD(day, 5, GETDATE())
You can create a string of all dates which is comma seperated with '[' and ']' before and after each date. assign this string to a string variable (@dates) and use the string spit method to split all dates inside the pivot query.
this question was posted about a year ago. i don't care. i have some code that might be exactly what the OP wanted.... i'm sure he'll never come back and chose an answer but still.... all i want to do is count the records by month with a pivot for a few tables and ultimately compare the number of records for each month for each table. however... in this code there is only one table (rt_taco_15m) but that doesn't matter. i just haven't written the rest to completely fit my needs. but i think it fits the needs of the OP or at least gets him on a good start.... if he truly has been waiting a year on this problem. lol.
if object_id('tempdb..#temp') is not null drop table #temp if object_id('tempdb..#temp2') is not null drop table #temp2 if object_id('tempdb..#temp3') is not null drop table #temp3 declare @start_date as datetime set @start_date = cast('1-1-2012' as datetime) declare @end_date as datetime set @end_date = cast('9-1-2012' as datetime) ;with cte as ( select @start_date as [start], dateadd(month, 1, @start_date) as [end] union all select dateadd(month, 1, [start]) as [start], dateadd(month, 1, dateadd(month, 1, [start])) as [end] from cte where dateadd(month, 1, [start]) <= @end_date ) (select 'rt_taco_15m' as table_name, convert(varchar(10), [start], 101) as [start], convert(varchar(10), [end], 101) as [end], datename(month, [start]) as month_name, cast([start] as integer) as orderby, count(taco.taco_record_id) as [range_count] into #temp from cte left outer join rt_taco_15m as taco on taco.period >= cte.[start] and taco.period < cte.[end] group by cte.[start], cte.[end]) select table_name as table_name, convert(varchar(10), getdate(), 101) as [start], convert(varchar(10), getdate(), 101) as [end], 'Total' as month_name, cast(dateadd(month,2,@end_date) as integer) as orderby, range_sum as [range_count] into #temp2 from (select table_name, sum([range_count]) as range_sum from #temp group by table_name) as summed_up select * into #temp3 from (select * from #temp union all select * from #temp2) as x order by orderby select * from #temp3 declare @cols nvarchar(2000) select @cols = stuff( (select '],[' + month_name from #temp3 order by orderby for xml path('') ) , 1, 2, '') + ']' print @cols if object_id('tempdb..#temp2') is not null drop table #temp2 declare @query varchar(max) set @query = N' select table_name, ' + @cols + N' from (select table_name, month_name, range_count from #temp3) p pivot ( sum(range_count) for month_name in ( '+ @cols +' ) ) as pvt' execute(@query