Changing Nested IIF to Case

Transact-SQL
1

Hi

I need to change an access query to work with SQL 2008 and wondered what is the best way of handling this set of nested IIFs ?

IIF(Reds>0, 'R',iif(Ambers>0,'A',Iif(Greens>0,'G',Iif(Completes>0,'C','?')))) as RAG

Many thanks

Phil

Edited - actually it may help if I include the full query (see below). Not sure this is the best way of doing this, so if anyone can help with getting a better query that would be great.

SELECT PR.[Project Reference],
		[Project Name],
		Min(Plans.Start) as Start, 
		Max(Plans.Finish) as Finish, 
        Min(Plans.[Baseline Start]) as [Baseline Start], 
		Max(Plans.[Baseline Finish]) as [Baseline Finish], 
                 
		(SELECT Sum(BenefitValue) 
			FROM (Workstreams INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Benefits Risk] ON Plans.ID = [Benefits Risk].Milestone 
			Where [Project Reference] = PR.[Project Reference]) as [Risk Score], 

        (SELECT Sum(BenefitValue) 
			FROM (Workstreams INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Benefits Value] ON Plans.ID = [Benefits Value].Milestone 
			Where [Project Reference] = PR.[Project Reference]) as [Benefit], 

        (SELECT Count(*)  
			FROM Workstreams W INNER JOIN Plans P On W.ID = P.WorkstreamID 
			Where W.[Project Reference] = PR.[Project Reference] And RAG='R') as Reds,

        (SELECT Count(*)  
			FROM Workstreams W INNER JOIN Plans P ON W.ID = P.WorkstreamID 
			Where W.[Project Reference] = PR.[Project Reference] And RAG='A') as Ambers, 

        (SELECT Count(*)  
			FROM Workstreams W INNER JOIN Plans P ON W.ID = P.WorkstreamID 
			Where W.[Project Reference] = PR.[Project Reference] And RAG='G') as Greens, 

        (SELECT Count(*)  
			FROM Workstreams W INNER JOIN Plans P ON W.ID = P.WorkstreamID 
			Where W.[Project Reference] = PR.[Project Reference] And RAG='C') as Completes, 

        IIF(Reds>0, 'R',iif(Ambers>0,'A',Iif(Greens>0,'G',Iif(Completes>0,'C','?')))) as RAG 

FROM 
	([Portfolio Register] PR INNER JOIN Workstreams ON PR.[Project Reference] = Workstreams.[Project Reference]) 
                INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID 
Group By 
	PR.[Project Reference], [Project Name] Order By PR.[Project Reference]
2 
case
   when reds>0      then 'R'
   when ambers>0    then 'A'
   when greens>0    then 'G'
   when completes>0 then 'C'
   else                  '?'
end as rag
3

Thanks; make sense.

Problem is, the query (which worked in Access) now fails in that it can't find the new columns for Reds, Ambers, Greens and Completes.

Can anyone see what I have done wrong?

Thanks

SELECT PR.[Project Reference],
	[Project Name],
	Min(Plans.Start) as Start, 
	Max(Plans.Finish) as Finish, 
    Min(Plans.[Baseline Start]) as [Baseline Start], 
	Max(Plans.[Baseline Finish]) as [Baseline Finish], 
             
	(SELECT Sum(BenefitValue) 
		FROM (Workstreams INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Benefits Risk] ON Plans.ID = [Benefits Risk].Milestone 
		Where [Project Reference] = PR.[Project Reference]) as [Risk Score], 

    (SELECT Sum(BenefitValue) 
		FROM (Workstreams INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Benefits Value] ON Plans.ID = [Benefits Value].Milestone 
		Where [Project Reference] = PR.[Project Reference]) as [Benefit], 

    (SELECT Count(*)  
		FROM Workstreams W INNER JOIN Plans P On W.ID = P.WorkstreamID 
		Where W.[Project Reference] = PR.[Project Reference] And RAG='R') as Reds,

    (SELECT Count(*)  
		FROM Workstreams W INNER JOIN Plans P ON W.ID = P.WorkstreamID 
		Where W.[Project Reference] = PR.[Project Reference] And RAG='A') as Ambers, 

    (SELECT Count(*)  
		FROM Workstreams W INNER JOIN Plans P ON W.ID = P.WorkstreamID 
		Where W.[Project Reference] = PR.[Project Reference] And RAG='G') as Greens, 

    (SELECT Count(*)  
		FROM Workstreams W INNER JOIN Plans P ON W.ID = P.WorkstreamID 
		Where W.[Project Reference] = PR.[Project Reference] And RAG='C') as Completes, 

		case
		   when Reds>0      then 'R'
		   when Ambers>0    then 'A'
		   when Greens>0    then 'G'
		   when Completes>0 then 'C'
		   else                  '?'
		end as RAG

FROM 
	([Portfolio Register] PR INNER JOIN Workstreams ON PR.[Project Reference] = Workstreams.[Project Reference]) 
				INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID 
Group By 
	PR.[Project Reference], [Project Name] Order By PR.[Project Reference]
4

Problem with the case statement is, that you're refering to fields (reds, ambers, greens and completes) that is not available (at least at point in time you're are refering to them). This could be solved by wrapping the query without the case statement, in a subselect, and then you can apply the case statement.

Looking at your query, there's much room for optimization. If you're interested in this, please provide descriptions of all tables in this query (as create statement), some sample data (as insert statement) and expected output (from the sample data you provide).

5

Well I am still learning :smile:

If you could have a look that would be great and help me improve.

I have included below what I think you need.....

Thanks

Phil

========= Result from Access ======

Project Reference Project Name Start Finish Baseline Start Baseline Finish Risk Score Benefit Reds Ambers Greens Completes RAG
PR00203 XXXXX Remediation 03/02/2014 04/01/2016 01/01/2001 18/12/2015 0 0 124 535 G

========= CREATES ========

-- Portfolio Register

CREATE TABLE [dbo].[Portfolio Register](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Project Reference] nvarchar NOT NULL,
[Status] nvarchar NULL,
[Project Phase] nvarchar NULL,
[Project Name] nvarchar NULL,
[Description] nvarchar NULL,
[Project Lead] nvarchar NULL,
[Project Manager] nvarchar NULL,
[Sponsor] nvarchar NULL,
[Capital Budget] [money] NULL DEFAULT ((0)),
[Revenue Budget] [money] NULL DEFAULT ((0)),
[Funding] nvarchar NULL,
[Priority] nvarchar NULL,
[Priority Rationale] nvarchar NULL,
[Expenditure Reference] nvarchar NULL,
[Cost Centre] nvarchar NULL,
[System] [bit] NULL DEFAULT ((0)),
[Notes] nvarchar NULL,
[Updated] datetime2 NULL,
[Updated By] nvarchar NULL,
[Inherit Access] [bit] NULL DEFAULT ((1)),
[RiskScore] nvarchar NULL,
[Benefit] nvarchar NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [Portfolio Register$PrimaryKey] PRIMARY KEY CLUSTERED
(
[Project Reference] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

-- Workstream

CREATE TABLE [dbo].[Workstreams](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Workstream] nvarchar NULL,
[Project Reference] nvarchar NULL,
[Project Manager] nvarchar NULL,
[Status] nvarchar NULL,
[Plan Loaded] datetime2 NULL,
[Plan Baselined] datetime2 NULL,
[Planning Confidence] [smallint] NULL DEFAULT ((0)),
[Notes] nvarchar NULL,
[Updated] datetime2 NULL,
[Updated By] nvarchar NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [Workstreams$PrimaryKey] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

-- Plans

CREATE TABLE [dbo].[Plans](
[ID] [int] IDENTITY(1,1) NOT NULL,
[WorkstreamID] [int] NULL DEFAULT ((0)),
[Title] nvarchar NULL,
[Start] datetime2 NULL,
[Finish] datetime2 NULL,
[Baseline Start] datetime2 NULL,
[Baseline Finish] datetime2 NULL,
[PercentComplete] [int] NULL DEFAULT ((0)),
[Milestone] [bit] NULL DEFAULT ((0)),
[Level] [int] NULL DEFAULT ((0)),
[RAG] nvarchar NULL,
[Comments] nvarchar NULL,
[Resources] nvarchar NULL,
[Duration] [int] NULL DEFAULT ((0)),
[PlanID] [int] NULL DEFAULT ((0)),
[PlanLine] [int] NULL DEFAULT ((0)),
[Deleted] [bit] NULL DEFAULT ((0)),
[System] [bit] NULL DEFAULT ((0)),
[ResourcesConfirmed] [bit] NULL DEFAULT ((0)),
[RiskScore] [int] NULL DEFAULT ((0)),
[Benefit] [money] NULL DEFAULT ((0)),
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [Plans$PrimaryKey] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Benefits Risk

CREATE TABLE [dbo].[Benefits Risk](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Milestone] [int] NULL,
[Description] nvarchar NULL,
[BenefitValue] [int] NULL,
CONSTRAINT [Benefits Risk$PrimaryKey] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Benefits Value

CREATE TABLE [dbo].[Benefits Value](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Milestone] [int] NULL,
[Description] nvarchar NULL,
[BenefitValue] [money] NULL,
CONSTRAINT [Benefits Value$PrimaryKey] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

========= INSERT ==========

-- Portfolio Register

INSERT INTO [Dbo].[Portfolio Register]([ID],[Project Reference],[Status],[Project Phase],[Project Name],[Description],[Project Lead],[Project Manager],[Sponsor],[Capital Budget],[Revenue Budget],[Funding],[Priority],[Priority Rationale],[Expenditure Reference],[Cost Centre],[System],[Notes],[Updated],[Updated By],[Inherit Access],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '1','PR00203','Open','Delivery','XXXXX Remediation','Migratiion','Phil','Jon','Jason','123.00','0.00','Capital','2. Strategic','Keeping the lights on','1477773901','777739','0','','2015-10-13 12:41:23','LIVE\CORLPH','1','0 / 0 (0%)','£0 / £0 (0%)','

-- Workstream

INSERT INTO [Dbo].[Workstreams]([ID],[Workstream],[Project Reference],[Project Manager],[Status],[Plan Loaded],[Plan Baselined],[Planning Confidence],[Notes],[Updated],[Updated By],[SSMA_TimeStamp]) Values( '7','Windows 2003 Study','PR00277 Study','SteveMMM','Closed','2015-10-16 13:52:36','2015-10-16 11:56:00','100',Null,Null,Null,'
INSERT INTO [Dbo].[Workstreams]([ID],[Workstream],[Project Reference],[Project Manager],[Status],[Plan Loaded],[Plan Baselined],[Planning Confidence],[Notes],[Updated],[Updated By],[SSMA_TimeStamp]) Values( '8','Phase 1','PR00277','SteveMMM','Open','2015-10-16 12:14:24','2015-10-16 12:05:00','100',Null,Null,Null,'
INSERT INTO [Dbo].[Workstreams]([ID],[Workstream],[Project Reference],[Project Manager],[Status],[Plan Loaded],[Plan Baselined],[Planning Confidence],[Notes],[Updated],[Updated By],[SSMA_TimeStamp]) Values( '13','Programme','PR00203','PPPP','Open','2015-08-12 13:30:02','2015-01-22 11:59:00','90','','2015-07-26 20:36:08','XXX\PHIL','
INSERT INTO [Dbo].[Workstreams]([ID],[Workstream],[Project Reference],[Project Manager],[Status],[Plan Loaded],[Plan Baselined],[Planning Confidence],[Notes],[Updated],[Updated By],[SSMA_TimeStamp]) Values( '14','Physical Windows Migrations','PR00203','YTTTT','Open','2015-09-11 10:53:15','2015-04-20 11:17:00','70','','2015-07-26 20:38:00','XXX\PHIL','
INSERT INTO [Dbo].[Workstreams]([ID],[Workstream],[Project Reference],[Project Manager],[Status],[Plan Loaded],[Plan Baselined],[Planning Confidence],[Notes],[Updated],[Updated By],[SSMA_TimeStamp]) Values( '15','Tiered Services','PR00203','WWWWW','Closed','2015-08-12 13:38:10','2015-06-10 13:29:00','100','','2015-07-26 20:38:17','XXX\PHIL','

-- Plans

INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2060','13','[Unallocated]',Null,Null,Null,Null,'0','0','0',Null,Null,Null,'0','0','0','0','1','0',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2061','13','Programme Start','2014-06-02 00:00:00','2014-06-02 00:00:00','2014-06-02 00:00:00','2014-06-02 00:00:00','100','1','2','C','',Null,'0','298','2','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2062','13','Programme Definition Approved','2014-10-03 00:00:00','2014-10-03 00:00:00','2014-10-03 00:00:00','2014-10-03 00:00:00','100','1','1','C','',Null,'0','321','3','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2063','13','Draft Programme Plan Confirmed','2014-09-05 00:00:00','2014-09-05 00:00:00','2014-09-05 00:00:00','2014-09-05 00:00:00','100','1','1','C','',Null,'0','322','4','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2064','13','First Steering Group','2014-09-03 00:00:00','2014-09-03 00:00:00','2014-09-03 00:00:00','2014-09-03 00:00:00','100','1','1','C','',Null,'0','323','5','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2069','13','Programme Management','2014-06-02 00:00:00','2014-08-08 00:00:00','2014-06-02 00:00:00','2014-08-08 00:00:00','100','0','100','C','','Iain','50','302','8','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2071','13','Programme Management','2014-07-28 00:00:00','2015-12-18 00:00:00','2014-07-28 00:00:00','2014-10-24 00:00:00','0','0','100','G','','Phil','359','301','9','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2074','13','Project Manager (Tiered Services)','2014-07-21 00:00:00','2015-01-23 00:00:00','2014-07-21 00:00:00','2014-10-17 00:00:00','100','0','100','C','','Ian','135','306','10','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2077','13','Project Manager (SAN/LAN Preperation)','2014-06-13 00:00:00','2014-10-09 00:00:00','2014-06-13 00:00:00','2014-10-09 00:00:00','100','0','100','C','','David','85','307','11','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2079','13','Project Manager (Storage Migrations)','2014-06-02 00:00:00','2014-11-28 00:00:00','2014-06-02 00:00:00','2014-11-28 00:00:00','100','0','100','C','','Steve','130','308','12','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2081','13','Project Manager (Storage Migrations)','2014-12-08 00:00:00','2015-12-18 00:00:00','2015-01-05 00:00:00','2015-12-18 00:00:00','24','0','100','G','','Tony','270','336','13','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2085','13','PMO Overhead','2014-07-01 00:00:00','2015-05-01 00:00:00','2014-07-01 00:00:00','2014-10-13 00:00:00','100','0','100','C','','Annmarie','219','303','14','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2088','13','TDL','2014-05-01 00:00:00','2015-01-30 00:00:00','2014-05-01 00:00:00','2015-01-30 00:00:00','100','0','100','C','','Technical Specialist[5%]','197','348','17','0','0','0',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2089','13','Others (pre August)','2014-06-02 00:00:00','2014-08-01 00:00:00','2014-06-02 00:00:00','2014-08-01 00:00:00','100','0','100','C','','Shared Services - Project Manager','45','349','18','0','0','0',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2091','13','D0.1 DEP HP - 3PAR Arrays ready for Consumption - SLAM','2014-10-17 00:00:00','2014-10-17 00:00:00','2014-10-17 00:00:00','2014-10-17 00:00:00','100','0','3','C','',Null,'0','312','20','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '2092','13','D0.1 DEP HP - 3PAR Arrays ready for Consumption - SCCM','2014-10-17 00:00:00','2014-10-17 00:00:00','2014-10-17 00:00:00','2014-10-17 00:00:00','100','0','3','C','',Null,'0','320','21','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '3312','13','Programme Close','2015-12-18 00:00:00','2015-12-18 00:00:00','2015-12-18 00:00:00','2015-12-18 00:00:00','0','1','1','G','',Null,'0','304','6','0','0','1',Null,Null,'
INSERT INTO [Dbo].[Plans]([ID],[WorkstreamID],[Title],[Start],[Finish],[Baseline Start],[Baseline Finish],[PercentComplete],[Milestone],[Level],[RAG],[Comments],[Resources],[Duration],[PlanID],[PlanLine],[Deleted],[System],[ResourcesConfirmed],[RiskScore],[Benefit],[SSMA_TimeStamp]) Values( '3774','13','PMO Overhead','2015-08-03 00:00:00','2015-12-18 00:00:00','2001-01-01 00:00:00','2001-01-01 00:00:00','0','0','100','G','','Helen','100','350','15','0','0','1',Null,Null,'

-- Benefits Risk

No Records

-- Benefits Value

No Records

6

Your insert statements are incomplete.
Try this:

select pr.[project reference]
      ,pr.[project name]
      ,min(p.start) as start
      ,max(p.finish) as finish
      ,min(p.[baseline start]) as [baseline start]
      ,max(p.[baseline finish]) as [baseline finish]
      ,sum(isnull(br.benefitvalue,0)) as [risk score]
      ,sum(isnull(bv.benefitvalue,0)) as benefit
      ,sum(case when p.rag='R' then 1 else 0 end) as reds
      ,sum(case when p.rag='A' then 1 else 0 end) as ambers
      ,sum(case when p.rag='G' then 1 else 0 end) as greens
      ,sum(case when p.rag='C' then 1 else 0 end) as completes
      ,case
          when sum(case when p.rag='R' then 1 else 0 end)>0 then 'R'
          when sum(case when p.rag='A' then 1 else 0 end)>0 then 'A'
          when sum(case when p.rag='G' then 1 else 0 end)>0 then 'G'
          when sum(case when p.rag='C' then 1 else 0 end)>0 then 'C'
          else                                                   '?'
       end as rag
  from [portfolio register] as pr
       inner join workstreams as w
               on w.[project reference]=pr.[project reference]
       inner join plans as p
               on p.workstreamid=w.id
       left outer join [benefits risk] as br
                    on br.milestone=p.id
       left outer join [benefits value] as bv
                    on bv.milestone=p.id
 group by pr.[project reference]
         ,pr.[project name]
 order by pr.[project reference]
;

I can't really find out, why you would make the rag field. The logic to me, makes very little sence. It makes more logic to me, to show all color letters used, instead of "just the first". In other words, this makes more sence to me:

      ,case when sum(case when p.rag='R' then 1 else 0 end)>0 then 'R' else '' end
      +case when sum(case when p.rag='A' then 1 else 0 end)>0 then 'A' else '' end
      +case when sum(case when p.rag='G' then 1 else 0 end)>0 then 'G' else '' end
      +case when sum(case when p.rag='C' then 1 else 0 end)>0 then 'C' else '' end
      +case when sum(case when p.rag in ('R','A','G','C') then 1 else 0 end)=0 then '?' else '' end
       as rag
1 Like
7

Many thanks I will have a go with this.

The single RAG is providing the worst state of each group. It isn't need to know how many of each RAG there are, just the worst one. So zero reds, 3 Ambers, 4 greens, 6 completes, would return Amber as the overall state. So it is doing a max(rag) really but based on colors not numbers

Hope that makes sense

Phil

8

From your sample, completes has the highest number, not amber. To get amber from your sample, it would be lowest number over zero.

Using highest number method (and there can be more than one color having the highest), you can do this:

with cte
  as (select pr.[project reference]
            ,pr.[project name]
            ,min(p.start) as start
            ,max(p.finish) as finish
            ,min(p.[baseline start]) as [baseline start]
            ,max(p.[baseline finish]) as [baseline finish]
            ,sum(isnull(br.benefitvalue,0)) as [risk score]
            ,sum(isnull(bv.benefitvalue,0)) as benefit
            ,sum(case when p.rag='R' then 1 else 0 end) as reds
            ,sum(case when p.rag='A' then 1 else 0 end) as ambers
            ,sum(case when p.rag='G' then 1 else 0 end) as greens
            ,sum(case when p.rag='C' then 1 else 0 end) as completes
        from [portfolio register] as pr
             inner join workstreams as w
                     on w.[project reference]=pr.[project reference]
             inner join plans as p
                     on p.workstreamid=w.id
             left outer join [benefits risk] as br
                          on br.milestone=p.id
             left outer join [benefits value] as bv
                          on bv.milestone=p.id
       group by pr.[project reference]
               ,pr.[project name]
     )
select *
      ,case
          when reds>0
           and reds>=ambers
           and reds>=greens
           and reds>=completes
          then 'R'
          else ''
       end
      +case
          when ambers>0
           and ambers>=reds
           and ambers>=greens
           and ambers>=completes
          then 'A'
          else ''
       end
      +case
          when greens>0
           and greens>=reds
           and greens>=ambers
           and greens>=completes
          then 'G'
          else ''
       end
      +case
          when completes>0
           and completes>=reds
           and completes>=ambers
           and completes>=greens
          then 'C'
          else ''
       end
      +case
          when reds=0
           and ambers=0
           and greens=0
           and completes=0
          then '?'
          else ''
       end
       as rag
  from cte
 order by [project reference]
;

Using lowest method, replace the case statements with this:

      ,case
          when reds>0
           and (reds<=ambers or ambers=0)
           and (reds<=greens or greens=0)
           and (reds<=completes or completes=0)
          then 'R'
          else ''
       end
      +case
          when ambers>0
           and (ambers<=reds or reds=0)
           and (ambers<=greens or greens=0)
           and (ambers<=completes or completes=0)
          then 'A'
          else ''
       end
      +case
          when greens>0
           and (greens<=reds or reds=0)
           and (greens<=ambers or ambers=0)
           and (greens<=completes or completes=0)
          then 'G'
          else ''
       end
      +case
          when completes>0
           and (completes<=reds or reds=0)
           and (completes<=ambers or ambers=0)
           and (completes<=greens or greens=0)
          then 'C'
          else ''
       end
      +case
          when reds=0
           and ambers=0
           and greens=0
           and completes=0
          then '?'
          else ''
       end
       as rag
9

It isn't the highest number of a specific RAG but the "maximum" state. So if there is 1 RED and 20 GREENS the RAG that should be returned if RED as that is the worst state. The next "worst" state would be amber, then green and then complete.

Hope that makes sense?

Appreciate the help

Thanks

Phil

10

The first query in the fifth post in this topic (my third reply) should do what you described.

1 Like
11

Many thanks - all starting to become clearer (sort of :smile: )

Phil