Returning specific records

Question

Hi,

I have the following SQL statement (within an SSRS report):

SELECT  
pl.title,
(SELECT  TOP(1) RAG 
FROM ELPS_ProjectStatusUpdate 
WHERE [Project] = [PL].[Title] ORDER BY ReportDate DESC) AS [StatusSummaryRAG],
  
(select top 1 RAG 
FROM (select top 2 * from ELPS_ProjectStatusUpdate 
WHERE [Project] = [PL].[Title] 
order by itemid desc) as a order by itemid asc) as StatusSummaryRAG2

FROM [CWSM_ProjectsList] AS [PL] 
INNER JOIN [ELPS_ProjectDetails] AS [PD] ON [PL].[Title] = [PD].[Project] 

StatusSummaryRAG returns the most recent record which is correct - however StatusSummaryRAG2 needs to return the record before the latest record. i.e. if there are 100 records, StatusSummaryRAG should return 100 and StatusSummaryRAG2 99.

My query appears to do this, however - sometimes there is only one record and StatusSummaryRAG2 will still return it; if there is more than one record it shouldn't (only one unique date should be returned against each statement). What the simplest way to amend the query so that unique values are returned?

Answer 1

Try

;with cteRags as (select Rag, Project, dense_rank() over (partition by Project order by ItemD Desc) as Rag2Rnk,

row_number() over (partition by Project order by ReportDate DESC) as Rag1Rnk from ELPS_ProjectStatusUpdate)

select pl.Title, F1.Rag as StatusSummaryRAG, F2.Rag as StatusSummaryRag2

from [CWSM_ProjectList] as PL

-- INNER JOIN [ELPS_ProjectDetails PD ON Pl.Title = PD.Project -- not sure this join is needed as we don't select from this table

CROSS APPLY (select Rag from cteRags where Rag1Rnk = 1 and cteRags.Project = Pl.Title) F1

OUTER APPLY (select Rag from cteRags where Rag2Rnk = 2 and cteRags.Project = Pl.Title) F2

Also, please note, that for StatusSummaryRag we're ordering by ReportDate DESC, but for StatusSummaryRag2 we're ordering my ItemID. If we wanted to order by ReportDate in both cases, we could have simplified the above query by only using one rank and 1 CROSS APPLY.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

This might help

(SELECT RAG 
FROM (SELECT TOP 2 *, ROW_NUMBER() OVER(ORDER BY itemid DESC) rn 
      FROM ELPS_ProjectStatusUpdate
      WHERE [Project] = [PL].[Title] AND rn = 2 ) AS a) AS StatusSummaryRAG2

there could be an alternative way to write the whole query though

Answer 3

Hi, I'm struggling to get get either solution working based on my original SQL statement - can you please provide a full example. I keep getting errors around the semi colon and the alias statements. Thanks

Answer 4

I also get an invalid column error on 'rn':

  WHERE [Project] = [PL].[Title] AND rn = 2 )

Answer 5

Number the rows, and then pivot:

; WITH numbered AS (
   SELECT Project, RAG,
          rowno = row_number() OVER(PARTITION BY Project
                                    ORDER BY ReportDate DESC)
   FROM   ELPS_ProjectStatusUpdate
), pivoted AS (
   SELECT Project,
          MAX(CASE rowno WHEN 1 THEN RAG END) AS StatusSummaryRAG,
          MAX(CASE rowno WHEN 2 THEN RAG END) AS StatusSummaryRAG2
   FROM   numbered
)
SELECT  pl.title, p.StatusSummaryRAG, p.StatusSummaryRAG
FROM    CWSM_ProjectsList AS pl
JOIN    pivoted p ON pl.title = pl.Project

You query includes an inner join to the table ELPS_ProjectDetails, but I could not see where it fits in, so I dropped it.

Disclaimer: the code above is not check for correctness or syntax. Since you did not supply scripts for table creation and sample data, I assume that you are willing to sort out trivial errors on your own.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Based on my statement below how do I add an IF statement around the StatusSummaryRAG2 block to check if there is more than 1 record - if so it should run as is but if there is only one record then StatusSummaryRAG2 can be ignored (as it is covered in StatusSummaryRAG)?

SELECT  
pl.title,
(SELECT  TOP(1) RAG FROM ELPS_ProjectStatusUpdate 
WHERE [Project] = [PL].[Title] ORDER BY ReportDate DESC) AS [StatusSummaryRAG],
  
(select top 1 RAG FROM (select top 2 * from ELPS_ProjectStatusUpdate WHERE [Project] = [PL].[Title] 
order by itemid desc) as a order by itemid asc) as StatusSummaryRAG2
FROM [CWSM_ProjectsList] AS [PL] 
INNER JOIN [ELPS_ProjectDetails] AS [PD] ON [PL].[Title] = [PD].[Project] 

Thanks

Answer 7

Did you try my solution?

Also, you didn't answer 2 of my comments:

1. For your StatusSummaryRag you're ordering by ReportDate DESC, but for the StatusSummaryRag2 you're ordering by ItemID desc, so, the orders are different and you will get different rows

2. Why do you use inner join with ELPS_ProjectDetails table if you're not selecting any columns from that table?

----------

If you want a better answer, please post DDL of your tables, some insert statements and the desired output based on the input. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

Based on my statement below how do I add an IF statement around the StatusSummaryRAG2 block to check if there is more than 1 record - if so it should run as is but if there is only one record then StatusSummaryRAG2 can be ignored (as it is covered in StatusSummaryRAG)?

What's wrong with the solutions that I and Naomi posted? It's difficult to help you if you don't give us feedback of what's wrong.

You cannot of course not have an IF statement in the middle of a SELECT, but you can use a CASE expression:

   CASE WHEN (SELECT COUNT(*) ....) > 0
        THEN (SELECT TOP 1 RAG...)
   END AS StatusSummaryRAG2

But it would be a less efficient solution than what I and Naomi posted.

And do you really want ORDER BY item id in StatusSummaryRAG2?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 9

Thanks

Ok - I need the join as I do bring across values from the second table - here is my amended SQL which is working apart from one thing:

;with cteRags as (select Rag, Project, dense_rank() over (partition by Project order by ItemiD Desc) as Rag2Rnk,
row_number() over (partition by Project order by ReportDate DESC) as Rag1Rnk from ELPS_ProjectStatusUpdate)
select
[PL].[PrimaryWorkstream] AS [PLPrimaryWorkstream] 
,[PL].[Title] AS [PLTitle] 
,[PL].[PlanOwner] AS [PLPlanOwner] 
,[PL].[Lead] AS [PLLead] 
,[PL].[SharePointProjectSite] AS [PLSharePointProjectSite] 
,[PL].[ITProject] AS [PLITProject] 
,[PL].[ProgrammeManager] AS [PLProgrammeManager] 
,[PL].[Stage] AS [PLStage] 
,[PD].[SiteUrl] AS [PDSiteUrl] 
,[PD].[ItemId] AS [PDItemId] 
,[PD].[Title] AS [PDTitle] 
,[PD].[Project] AS [PDProject],


(SELECT COUNT(ItemID) FROM [DeliveryHub].[dbo].[ELPS_Risks] WHERE [Status] = 'Open' AND [Project] IN(select [Project] from [ELPS_ProjectDetails] where [Project] = [PL].[Title]   )) AS [RisksCount], 
(SELECT COUNT(ItemID) FROM [DeliveryHub].[dbo].[ELPS_Issues] WHERE [Status] = 'Open' AND [Project] IN(select [Project] from [ELPS_ProjectDetails] where [Project] = [PL].[TItle]  )) AS [IssuesCount],
(SELECT  TOP (1) CurrentStatusSummary FROM ELPS_ProjectStatusUpdate WHERE [Project] = [PL].[Title] ORDER BY ReportDate DESC) AS [StatusSummary],


F1.Rag as StatusSummaryRAG, F2.Rag as StatusSummaryRAG2
from [CWSM_ProjectsList] as PL
CROSS APPLY (select Rag from cteRags where Rag1Rnk = 1 and cteRags.Project = Pl.Title) F1
OUTER APPLY (select Rag from cteRags where Rag2Rnk = 2 and cteRags.Project = Pl.Title) F2

INNER JOIN [DeliveryHub].[dbo].[ELPS_ProjectDetails] AS [PD] ON [PL].[Title] = [PD].[Project] 
WHERE [PL].[Title] IN(@Project)  
AND [PL].[PrimaryWorkstream] IN(@Workstream)  
ORDER BY [PL].[PrimaryWorkstream], [PL].[Title]

The problem is that I only see results where there is a StatusSummaryRAG or StatusSummaryRAG2 result. I want to see all rows because the Report data (ELPS_ProjectStatusUpdate) won't always be available yet I still want to see the details from ELPS_ProjectDetails.

Is there an issue with the join?

Thanks again - much appreciated.

Answer 10

In fact I should always see results from CWSM_ProjectsList and ELPS_ProjectDetails - sometimes the  value from ELPS_ProjectStatusIUpdate will contain a date and other times a NULL.

Would a LEFT JOIN help here?

Answer 11

Ok - so all the records do appear to be showing.

Lastly, the above query returns the latest record and the one previous. Is it possible to get the thord record (3rd latest)?

i.e. I would see RAG values from the last three report dates.

Thanks again.

Answer 12

In this case you just need to use OUTER APPLY for both cases instead of the first CROSS APPLY. Just change first CROSS APPLY to OUTER APPLY and I hope it will give you results you're looking for.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 13

Sure, just add one extra OUTER APPLY same as above but using Rag1Rank = 3 (in case you want it by date) or Rag2Rank = 3 in case you want it by ItemId.

BTW, you still didn't answer why you're using different order.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 14

The query below should be more efficient, since if you use the pivot model, the CTE only has to be computed once. I also simplified the COUNT(*) queries. I cannot see any need for those extra instances of the ProjectDetails table.

I removed all those square brackets. I don't know about you, but they sure hurt my eyes.

Finally, in the WHERE clause you have a somewhat mysterious use of IN:

WHERE [PL].[Title] IN(@Project) 
AND [PL].[PrimaryWorkstream] IN(@Workstream) 

I don't know what you have mind, but I've changed these to =, which is exactly the same.

Here is the modified query.

; WITH numbered AS (
   SELECT Project, RAG,
          rowno = row_number() OVER(PARTITION BY Project
                                    ORDER BY ReportDate DESC)
   FROM   ELPS_ProjectStatusUpdate
), pivoted AS (
   SELECT Project,
          MAX(CASE rowno WHEN 1 THEN RAG END) AS StatusSummaryRAG,
          MAX(CASE rowno WHEN 2 THEN RAG END) AS StatusSummaryRAG2
   FROM   numbered
)
SELECT PL.PrimaryWorkstream AS PLPrimaryWorkstream,
       PL.Title AS PLTitle,
       PL.PlanOwner AS PLPlanOwner,
       PL.Lead AS PLLead,
       PL.SharePointProjectSite AS PLSharePointProjectSite,
       PL.ITProject AS PLITProject,
       PL.ProgrammeManager AS PLProgrammeManager,
       PL.Stage AS PLStage,
       PD.SiteUrl AS PDSiteUrl,
       PD.ItemId AS PDItemId,
       PD.Title AS PDTitle,
       PD.Project AS PDProject,
       (SELECT COUNT(R.ItemID)
        FROM   DeliveryHub.dbo.ELPS_Risks R
        WHERE  R.Status = 'Open'
          AND  R.Project = PL.Title) AS RisksCount,
       (SELECT COUNT(I.ItemID)
        FROM   DeliveryHub.dbo.ELPS_Issues I
        WHERE  I.Status = 'Open'
          AND  I.Project = PL.TItle) AS IssuesCount,
       (SELECT  TOP (1) CurrentStatusSummary
        FROM    ELPS_ProjectStatusUpdate
        WHERE   Project = PL.Title
        ORDER   BY ReportDate DESC) AS StatusSummary,
       n.StatusSummaryRAG, nStatusSummaryRAG2
FROM   CWSM_ProjectsList as PL
LEFT   JOIN numbered n ON PL.Title = n.Project
JOIN   DeliveryHub.dbo.ELPS_ProjectDetails AS PD ON PL.Title = PD.Project
WHERE  PL.Title = @Project
  AND  PL.PrimaryWorkstream = @Workstream
ORDER  BY PL.PrimaryWorkstream, PL.Title

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 15

Hi Erdland - when I put the above into Query Analyser 'n.StatusSummaryRAG' and 'nStatusSummaryRAG2' are underlined red and status 'invalid column name..':

  ORDER   BY ReportDate DESC) AS StatusSummary,
        n.StatusSummaryRAG, nStatusSummaryRAG2
 FROM   CWSM_ProjectsList as PL

Naomi - I am using a different order based on an exmaple I found. If the above can be changed to included the THIRD value and order correctly i.e. by Report date newset first then that would be fantastic...

:)

Answer 16

Hi Erdland - when I put the above into Query Analyser 'n.StatusSummaryRAG' and 'nStatusSummaryRAG2' are underlined red and status 'invalid column name..':

Maybe if you look closely, you might be able to spot the error?

In a previous post I said:

Disclaimer: the code above is not check for correctness or syntax. Since you did not supply scripts for table creation and sample data, I assume that you are willing to sort out trivial errors on your own.

That still stands. If you want to be spoonfed working queries, you need to make some effort to help us to help you.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 17

I did spot the error "n." - but both columns do not exist. I'll take a closer look.

My error is:

Msg 8120, Level 16, State 1, Line 7

Column 'numbered.Project' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 18

If you want all 3 values to be ordered by Report Date, (1st latest, 2nd latest, 3rd latest), then it's simple:

;with cteRags as (select Rag, 
CurrentStatusSummary,
Project, dense_rank() over (partition by Project order by ReportDate Desc) as Rnk
from ELPS_ProjectStatusUpdate
WHERE [Project] IN (@Project))
SELECT
[PL].[PrimaryWorkstream] AS [PLPrimaryWorkstream] 
,[PL].[Title] AS [PLTitle] 
,[PL].[PlanOwner] AS [PLPlanOwner] 
,[PL].[Lead] AS [PLLead] 
,[PL].[SharePointProjectSite] AS [PLSharePointProjectSite] 
,[PL].[ITProject] AS [PLITProject] 
,[PL].[ProgrammeManager] AS [PLProgrammeManager] 
,[PL].[Stage] AS [PLStage] 
,[PD].[SiteUrl] AS [PDSiteUrl] 
,[PD].[ItemId] AS [PDItemId] 
,[PD].[Title] AS [PDTitle] 
,[PD].[Project] AS [PDProject],
(SELECT COUNT(ItemID) FROM [DeliveryHub].[dbo].[ELPS_Risks] 
WHERE [Status] = 'Open' AND [Project] = [PL].[Title]  ) AS [RisksCount], 
(SELECT COUNT(ItemID) FROM [DeliveryHub].[dbo].[ELPS_Issues] 
WHERE [Status] = 'Open' AND [Project] = [PL].[TItle]) AS [IssuesCount],
F1.[StatusSummary],
F1.StatusSummaryRAG, F1.StatusSummaryRAG2,
F1.StatusSummaryRag3
FROM [CWSM_ProjectsList] as PL
OUTER APPLY 
(SELECT MAX(case when Rnk=1 then CurrentStatusSummary END) as StatusSummary,
MAX(case when Rnk=1 then Rag END) as StatusSummaryRag,
MAX(case when Rnk=2 then Rag END) as StatusSummaryRag2,
MAX(case when Rnk=3 then Rag END) as StatusSummaryRag3
from cteRags where cteRags.Project = Pl.Title) F1

INNER JOIN [DeliveryHub].[dbo].[ELPS_ProjectDetails] AS [PD] ON [PL].[Title] = [PD].[Project] 
WHERE [PL].[Title] IN (@Project)  
AND [PL].[PrimaryWorkstream] IN (@Workstream)  
ORDER BY [PL].[PrimaryWorkstream], [PL].[Title]

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 19

Hi Naomi - hmm, I get this:

Operand data type ntext is invalid for max operator.

Answer 20

Is the CurrentStatusSummary a ntext type? If you're using SQL 2005 and up, there is no need to use text/ntext and better to use nvarchar(max)/varchar(max). Anyway, in the query above first cast the value to nvarchar(max) then, e.g.

;with cteRags as (select Rag, 
CurrentStatusSummary,
Project, dense_rank() over (partition by Project order by ReportDate Desc) as Rnk
from ELPS_ProjectStatusUpdate
WHERE [Project] IN (@Project))
SELECT
[PL].[PrimaryWorkstream] AS [PLPrimaryWorkstream] 
,[PL].[Title] AS [PLTitle] 
,[PL].[PlanOwner] AS [PLPlanOwner] 
,[PL].[Lead] AS [PLLead] 
,[PL].[SharePointProjectSite] AS [PLSharePointProjectSite] 
,[PL].[ITProject] AS [PLITProject] 
,[PL].[ProgrammeManager] AS [PLProgrammeManager] 
,[PL].[Stage] AS [PLStage] 
,[PD].[SiteUrl] AS [PDSiteUrl] 
,[PD].[ItemId] AS [PDItemId] 
,[PD].[Title] AS [PDTitle] 
,[PD].[Project] AS [PDProject],
(SELECT COUNT(ItemID) FROM [DeliveryHub].[dbo].[ELPS_Risks] 
WHERE [Status] = 'Open' AND [Project] = [PL].[Title]  ) AS [RisksCount], 
(SELECT COUNT(ItemID) FROM [DeliveryHub].[dbo].[ELPS_Issues] 
WHERE [Status] = 'Open' AND [Project] = [PL].[TItle]) AS [IssuesCount],
F1.[StatusSummary],
F1.StatusSummaryRAG, F1.StatusSummaryRAG2,
F1.StatusSummaryRag3
FROM [CWSM_ProjectsList] as PL
OUTER APPLY 
(SELECT MAX(case when Rnk=1 
then cast(CurrentStatusSummary as nvarchar(max)) END) as StatusSummary,
MAX(case when Rnk=1 then Rag END) as StatusSummaryRag,
MAX(case when Rnk=2 then Rag END) as StatusSummaryRag2,
MAX(case when Rnk=3 then Rag END) as StatusSummaryRag3
from cteRags where cteRags.Project = Pl.Title) F1

INNER JOIN [DeliveryHub].[dbo].[ELPS_ProjectDetails] AS [PD] ON [PL].[Title] = [PD].[Project] 
WHERE [PL].[Title] IN (@Project)  
AND [PL].[PrimaryWorkstream] IN (@Workstream)  
ORDER BY [PL].[PrimaryWorkstream], [PL].[Title]

If the RAG column is ntext, do the same for the RAG column.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 21

My error is:

Msg 8120, Level 16, State 1, Line 7

Column 'numbered.Project' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What about adding a GROUP BY clause then?

You will also need to cast the RAGs to nvarchar(MAX) to get rid of that error. (And make a note about talking with your DBA to change the schema, as the ntext is deprecated.)

I can't escape from asking what expectations you have when you come here to ask a question. That you will just have lean back in your chair while we give you a working a query? We can do that, but then we need:

1) CREATE TABLE statements for your tables.
2) INSERT statements with sample dat.a
3) The expected results given the sample.
4) Which version of SQL Sever you are using.

But if we don't have that we cannot test our queries, but only give you suggestions and you will have to sort out the details. Had I known that the RAGs are the next data type, I would bave covered in my SELECT statement. And had I had the table scripts would I have spotted the missing GROUP BY and the missing period. Now if I declare the variables and run the query, the error I get:

Msg 208, Level 16, State 1, Line 3
Invalid object name 'ELPS_ProjectStatusUpdate'.

And that's where I stop. It's not that you pay me or Naomi to help you.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 22

Is the CurrentStatusSummary a ntext type? If you're using SQL 2005 and up, there is no need to use text/ntext and better to use nvarchar(max)/varchar(max). Anyway, in the query above first cast the value to nvarchar(max) then, e.g.

;with cteRags as (select Rag, 
CurrentStatusSummary,
Project, dense_rank() over (partition by Project order by ReportDate Desc) as Rnk
from ELPS_ProjectStatusUpdate
WHERE [Project] IN (@Project))
SELECT
[PL].[PrimaryWorkstream] AS [PLPrimaryWorkstream] 
,[PL].[Title] AS [PLTitle] 
,[PL].[PlanOwner] AS [PLPlanOwner] 
,[PL].[Lead] AS [PLLead] 
,[PL].[SharePointProjectSite] AS [PLSharePointProjectSite] 
,[PL].[ITProject] AS [PLITProject] 
,[PL].[ProgrammeManager] AS [PLProgrammeManager] 
,[PL].[Stage] AS [PLStage] 
,[PD].[SiteUrl] AS [PDSiteUrl] 
,[PD].[ItemId] AS [PDItemId] 
,[PD].[Title] AS [PDTitle] 
,[PD].[Project] AS [PDProject],
(SELECT COUNT(ItemID) FROM [DeliveryHub].[dbo].[ELPS_Risks] 
WHERE [Status] = 'Open' AND [Project] = [PL].[Title]  ) AS [RisksCount], 
(SELECT COUNT(ItemID) FROM [DeliveryHub].[dbo].[ELPS_Issues] 
WHERE [Status] = 'Open' AND [Project] = [PL].[TItle]) AS [IssuesCount],
F1.[StatusSummary],
F1.StatusSummaryRAG, F1.StatusSummaryRAG2,
F1.StatusSummaryRag3
FROM [CWSM_ProjectsList] as PL
OUTER APPLY 
(SELECT MAX(case when Rnk=1 
then cast(CurrentStatusSummary as nvarchar(max)) END) as StatusSummary,
MAX(case when Rnk=1 then Rag END) as StatusSummaryRag,
MAX(case when Rnk=2 then Rag END) as StatusSummaryRag2,
MAX(case when Rnk=3 then Rag END) as StatusSummaryRag3
from cteRags where cteRags.Project = Pl.Title) F1

INNER JOIN [DeliveryHub].[dbo].[ELPS_ProjectDetails] AS [PD] ON [PL].[Title] = [PD].[Project] 
WHERE [PL].[Title] IN (@Project)  
AND [PL].[PrimaryWorkstream] IN (@Workstream)  
ORDER BY [PL].[PrimaryWorkstream], [PL].[Title]

If the RAG column is ntext, do the same for the RAG column.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Thank you Naomi (and Erland) - with your help as I now have a report which appears to be giving me the correct results.

Your help is very much appreciated.