Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
Hi Everyone one.
 
I am creating a workflow database for the office and hence we need to see what work is planned for each day of the week.
 
The stored proc I will shortly give below will only show the date for which we have data. However, what changes do I need to make if I also want to see dates even if there is no data for such a date.
 
I am using dynamic SQL so the columns and hence dates automatically shift up as you progress to the next day in the week.
 
I hope that makes sense.
 
many thanks.
 
ALTER PROCEDURE [dbo].[spTotalRunsPerWeek]
AS
 
CREATE TABLE #ResultsDateSet
(
  DateRun VARCHAR(MAX)
)
 
INSERT INTO #ResultsDateSet
Select convert(varchar(10), EstimatedCompletionDate, 111)
from tbRun r
left outer join tbProject p on p.ProjectID = r.ProjectID
left outer join tbFK_UserProject fk on fk.ProjectID = p.ProjectID
left outer join tbUser u on u.UserID = fk.UserID
left outer join tbRole ro on ro.RoleID = fk.RoleID
left outer join tbExecution e on e.ExecutionID = p.ExecutionID
WHERE UserName <> 'NA' and ro.RoleID = 2
and EstimatedCompletionDate >= DATEADD(day,-1,GETDATE())
and EstimatedCompletionDate <= DATEADD(day,14,GETDATE())
group by convert(varchar(10), EstimatedCompletionDate, 111)
order by convert(varchar(10), EstimatedCompletionDate, 111)
 
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = 
  COALESCE(
     @PivotColumnHeaders + ',[' + cast(DateRun as varchar) + ']',
     '[' + cast(DateRun as varchar)+ ']'
   )
 FROM #ResultsDateSet
 ORDER BY DateRun
 
DECLARE @PivotTableSQL NVARCHAR(MAX)
 SET @PivotTableSQL = N'
 
CREATE TABLE #Results
(
  UserName VARCHAR(MAX),
  ProjectName VARCHAR(MAX),
  ExecutionName VARCHAR(MAX),
  DateRun VARCHAR(MAX),
  TotalRuns INT
)
 
INSERT INTO #Results
Select u.FirstName + '' '' + u.Surname as UserName, ProjectName, ExecutionName,
       convert(varchar(10), EstimatedCompletionDate, 111),
Count(r.RunNumber)
from tbRun r
left outer join tbProject p on p.ProjectID = r.ProjectID
left outer join tbFK_UserProject fk on fk.ProjectID = p.ProjectID
left outer join tbUser u on u.UserID = fk.UserID
left outer join tbRole ro on ro.RoleID = fk.RoleID
left outer join tbExecution e on e.ExecutionID = p.ExecutionID
WHERE UserName <> ''NA'' and ro.RoleID = 2
group by u.FirstName, u.Surname, ProjectName, ExecutionName,convert(varchar(10), EstimatedCompletionDate, 111)
order by u.FirstName, u.Surname, ProjectName, ExecutionName,convert(varchar(10), EstimatedCompletionDate, 111)
 
SELECT *
FROM #Results
PIVOT (sum(TotalRuns)for DateRun in (' + @PivotColumnHeaders + ')) As Result
 
DROP TABLE #Results
'
 
EXECUTE(@PivotTableSQL)
 
DROP TABLE #ResultsDateSet
Posted 18-Feb-13 3:44am
Edited 18-Feb-13 3:53am
Maciej Los150.6K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

might not be what your after but you could use a RCT(recursive common table expresion) to create a multiple rows for everydate you need and then right outer join to it
 
see example below
 
declare @tables table (dates datetime);
with dates as (
    SELECT CAST('2012-01-01' as datetime) as addtome
    UNION ALL
    SELECT DATEADD(day,1,addtome) FROM dates where DATEPART(year,addtome) = 2012
    )
 

INSERT INTO @tables SELECT addtome FROM dates OPTION (MAXRECURSION 366)
 

then in your select statement add a join as below
RIGHT OUTER JOIN @tables t on t.date =
  Permalink  
Comments
Maciej Los at 18-Feb-13 11:28am
   
Good idea and implementation. +5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

As Dec90 wrote, it looks like you need all dates in date range.
 
Other solution to enum all dates in date range is to use loop:
DECLARE @begDate DATETIME
DECLARE @endDate DATETIME
DECLARE @curDate DATETIME
 
DECLARE @dTable TABLE(aDate DATETIME)
 
SET @begDate = '2013-01-01'
SET @endDate = GETDATE()
 
SET @curDate = DATEADD(day,-1,@begDate)
WHILE (@curDate < @endDate)
BEGIN
	SET @curDate = DATEADD(day,1,@curDate)
	INSERT INTO @dTable VALUES(@curDate)
END
 
SELECT *
FROM @dTable
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 BillWoodruff 330
1 Mathew Soji 309
2 Maciej Los 295
3 DamithSL 225
4 Afzaal Ahmad Zeeshan 217
0 OriginalGriff 6,369
1 Sergey Alexandrovich Kryukov 5,973
2 DamithSL 5,183
3 Manas Bhardwaj 4,673
4 Maciej Los 4,025


Advertise | Privacy | Mobile
Web01 | 2.8.1411019.1 | Last Updated 18 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100