sql server - Get Total on SQL Dynamic (Calendar) Pivot Tables -
i trying sql server dynamic pivot table work allows me count , sum number of columns. purpose of pivot table create report of days individuals staying in city , total number of days(in month). so, example, person staying everyday in june - total 30.person b started staying on 3rd of june - total 27 etc. data table consists of name, arrivedate, departdate...the days of month created through sql query.
+------+------------+------------+-------+-------+-------+-----+-------+-------+-------+ | name | arrivedate | departdate | 06-01 | 06-02 | 06-03 | ... | 06-29 | 06-30 | total | +------+------------+------------+-------+-------+-------+-----+-------+-------+-------+ | | 2014-06-01 | 2014-06-23 | 1 | 1 | 1 | ... | 1 | 1 | 30 | | b | 2014-06-02 | 2014-06-23 | 0 | 1 | 1 | ... | 1 | 1 | 27 | | c | 2014-06-02 | 2014-06-23 | 0 | 0 | 0 | ... | 1 | 1 | 16 | +------+------------+------------+-------+-------+-------+-----+-------+-------+-------+here query have far:
drop table #tempdates declare @cols nvarchar(max), @query nvarchar(max) ;with cte (datelist, maxdate) ( select min(arrivedate) datelist, eomonth(getdate()) maxdate reservation union select dateadd(dd, 1, datelist), maxdate cte datelist < maxdate ) select c.datelist #tempdates cte c select @cols = stuff(( select distinct ',' + quotename(convert(char(10), datelist, 120)) #tempdates xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select id, arrivedate, departdate, ' + @cols + ' ( select r.id, r.arrivedate, r.departdate, d.datelist, convert(char(10), datelist, 120) pivotdate reservation r left join #tempdates d on d.datelist between rg.arrivedate , getdate() ) x pivot ( count(datelist) pivotdate in (' + @cols + ') ) p ' execute (@query)
here attempt using dynamic crosstab:
sample data:
name arrivedate departdate ---- ---------- ---------- 2015-07-01 2015-07-23 b 2015-07-02 2015-07-04 c 2015-07-03 2015-07-31 dynamic crosstab solution:
declare @mindate date, @maxdate date select @mindate = dateadd(day, 1, eomonth(getdate(), -1)), @maxdate = eomonth(getdate()) create table #dates(dt date) declare @sql1 varchar(max) = '', @sql2 varchar(max) = '', @sql3 varchar(max) = ''; e1(n) as( select 1 from(values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(n) ), e2(n) as(select 1 e1 cross join e1 b), e4(n) as(select 1 e2 cross join e2 b), e8(n) as(select 1 e4 cross join e4 b), tally(n) as( select top(datediff(day, @mindate, @maxdate) + 1) row_number() over(order (select null)) e8 ) insert #dates select dateadd(day, n - 1, @mindate) tally select @sql1 = 'select r.name , r.arrivedate , r.departdate' + char(10) select @sql2 = @sql2 + ' , sum(case when d.dt = cast(''' + convert(varchar(8), dt, 112) + ''' date) 1 else 0 end) ' + quotename(convert(varchar(10), dt, 120)) + char(10) #dates order dt select @sql2 = @sql2 + ' , count(d.dt) [total]' + char(10) select @sql3 = 'from reservation r left join #dates d on d.dt between r.arrivedate , r.departdate group r.name, r.arrivedate, r.departdate' print (@sql1 + @sql2 + @sql3) exec (@sql1 + @sql2 + @sql3) drop table #dates result:
name arrivedate departdate 2015-07-01 2015-07-02 2015-07-03 ..... 2015-07-29 2015-07-30 2015-07-31 total ---- ---------- ---------- ----------- ----------- ----------- ..... ----------- ----------- ----------- ----------- 2015-07-01 2015-07-23 1 1 1 ..... 0 0 0 23 b 2015-07-02 2015-07-04 0 1 1 ..... 0 0 0 3 c 2015-07-03 2015-07-31 0 0 1 ..... 1 1 1 29
Comments
Post a Comment