Click here to Skip to main content
12,952,725 members (45,625 online)
Rate this:
 
Please Sign up or sign in to vote.
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 2:44am
Updated 18-Feb-13 2:53am
Maciej Los253.1K
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 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
Top Experts
Last 24hrsThis month
OriginalGriff 6,289
CHill60 3,490
Maciej Los 3,103
Jochen Arndt 1,975
ppolymorphe 1,900


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 18 Feb 2013
Copyright © CodeProject, 1999-2017
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