Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT *
- FROM (
- SELECT [TM_UserID],
- [FullName],
- [Worked_dte],
- [Worked_Hours]
- FROM @Reporting_User_Timesheet
- WHERE [worked_dte] BETWEEN '2014-04-04'
- AND '2014-04-06'
- ) AS sourceTable
- Pivot(sum([Worked_Hours]) FOR [Worked_dte] IN ([2014-04-04], [2014-04-05], [2014-04-06])) AS PivotTable
- declare @range_start date, @range_end date;
- select @range_start = '20140404', @range_end = '20140406';
- declare @collist nvarchar(max);
- SET @collist = stuff((select distinct ',' + QUOTENAME(convert(varchar,date,112))
- FROM calendar
- WHERE Datue BETWEEN @range_start AND @range_end
- FOR XML PATH(''), TYPE
- ).value('.', 'NVARCHAR(MAX)')
- ,1,1,'');
- declare @q nvarchar(max);
- set @q = '
- SELECT *
- FROM (
- SELECT [TM_UserID],
- [FullName],
- [Worked_dte],
- [Worked_Hours]
- FROM @Reporting_User_Timesheet
- WHERE [worked_dte] BETWEEN ''' + CONVERT(varchar, @range_start, 112) + '''
- AND ''' + CONVERT(varchar, @range_end, 112) + '''
- ) AS sourceTable
- Pivot (
- sum([Worked_Hours]) FOR [Worked_dte] IN (' + @collist + ')
- ) AS PivotTable
- ';
- exec (@q);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement