TRANSFORM Last(QryDataDump.StatusDate) AS LastOfStatusDate
SELECT QryDataDump.[Study Code], QryDataDump.study_title, QryDataDump.study_description, QryDataDump.[Study Type], QryDataDump.LastOfOverall AS RAG, QryDataDump.LastOfDateRAGamended AS [RAG Updated], QryDataDump.[Current Status], QryDataDump.[Current Status Date], QryDataDump.[Business Unit], QryDataDump.[HMRC Program Area], QryDataDump.IMSPortfolioAreaName, QryDataDump.OGD, QryDataDump.LeadSupplier, QryDataDump.[SDD Involvement], QryDDSlowContacts.[Aspire Viability Study Manager], QryDDSlowContacts.[Lead Architect], QryDDSlowContacts.[Opportunity Owner], QryDDContact.AspireBusinessManagerName, QryDDContact.AspireDeliveryManager, QryDDContact.SDLT, QryDDContact.[SD Porfolio], QryDDSlowContacts.[Fujitsu Architect], QryDDSlowContacts.[Accenture Architect], QryDDContact.[Aspire Requirements Manager], QryDDSlowContacts.[HMRC Viability Study Manager], QryDDContact.[SDD Bid Manager], QryDDContact.[IMS Business Partner], QryDDContact.SRO, QryDDContact.[Business Project Manager], QryDDContact.[Strategic Architect], QryDDContact.[SDD Architect], QryDDContact.[IMS Asset Manager], QryDDContact.[Commercial Advisor], QryDDContact.[Portfolio Delivery Manager], QryDataDump.JDBCreationDate, QryDataDump.[Baseline VRG], QryDataDump.[Baseline Viability Start], QryDataDump.[Baseline Viability End], QryDataDump.[Baseline IT Supplier Proposal Due], QryDataDump.[Baseline Proposal Expiry], QryDataDump.[Baseline SRO Approval], QryDataDump.[Baseline Commercial Sign Off], QryDataDump.[Baseline Projected Implementation], QryDataDump.[Current VRG], QryDataDump.[Current Viability Start], QryDataDump.[Current Viability End], QryDataDump.[Current IT Supplier Proposal Due], QryDataDump.[Current Proposal Expiry], QryDataDump.[Current SRO Approval], QryDataDump.[Current Commercial Sign Off], QryDataDump.[Current Projected Implementation], QryDataDump.PLine AS [P-Line], QryDataDump.SLine AS [S-Line], QryDataDump.Other, QryDataDump.P01Value AS [P01 Value], QryDataDump.ApprovedOBCCost AS [Approved OBC £], QryDataDump.Rework, QryDataDump.QryDDStudyData.Working AS Active, QryDataDump.[First Proposal Issued Date], Last(QryDataDump.[IT Supplier Proposal Slippage Comment]) AS [IT Supplier Proposal Slippage Comment]
FROM (QryDataDump LEFT JOIN QryDDContact ON QryDataDump.[Study Code] = QryDDContact.Feasibility_code) LEFT JOIN QryDDSlowContacts ON QryDataDump.StudyID = QryDDSlowContacts.study_id
WHERE (((QryDataDump.[Study Type])="Viability" Or (QryDataDump.[Study Type])="Viability Planning"))
GROUP BY QryDataDump.[Study Code], QryDataDump.study_title, QryDataDump.study_description, QryDataDump.[Study Type], QryDataDump.LastOfOverall, QryDataDump.LastOfDateRAGamended, QryDataDump.[Current Status], QryDataDump.[Current Status Date], QryDataDump.[Business Unit], QryDataDump.[HMRC Program Area], QryDataDump.IMSPortfolioAreaName, QryDataDump.OGD, QryDataDump.LeadSupplier, QryDataDump.[SDD Involvement], QryDDSlowContacts.[Aspire Viability Study Manager], QryDDSlowContacts.[Lead Architect], QryDDSlowContacts.[Opportunity Owner], QryDDContact.AspireBusinessManagerName, QryDDContact.AspireDeliveryManager, QryDDContact.SDLT, QryDDContact.[SD Porfolio], QryDDSlowContacts.[Fujitsu Architect], QryDDSlowContacts.[Accenture Architect], QryDDContact.[Aspire Requirements Manager], QryDDSlowContacts.[HMRC Viability Study Manager], QryDDContact.[SDD Bid Manager], QryDDContact.[IMS Business Partner], QryDDContact.SRO, QryDDContact.[Business Project Manager], QryDDContact.[Strategic Architect], QryDDContact.[SDD Architect], QryDDContact.[IMS Asset Manager], QryDDContact.[Commercial Advisor], QryDDContact.[Portfolio Delivery Manager], QryDataDump.JDBCreationDate, QryDataDump.[Baseline VRG], QryDataDump.[Baseline Viability Start], QryDataDump.[Baseline Viability End], QryDataDump.[Baseline IT Supplier Proposal Due], QryDataDump.[Baseline Proposal Expiry], QryDataDump.[Baseline SRO Approval], QryDataDump.[Baseline Commercial Sign Off], QryDataDump.[Baseline Projected Implementation], QryDataDump.[Current VRG], QryDataDump.[Current Viability Start], QryDataDump.[Current Viability End], QryDataDump.[Current IT Supplier Proposal Due], QryDataDump.[Current Proposal Expiry], QryDataDump.[Current SRO Approval], QryDataDump.[Current Commercial Sign Off], QryDataDump.[Current Projected Implementation], QryDataDump.PLine, QryDataDump.SLine, QryDataDump.Other, QryDataDump.P01Value, QryDataDump.ApprovedOBCCost, QryDataDump.Rework, QryDataDump.QryDDStudyData.Working, QryDataDump.[First Proposal Issued Date]
ORDER BY QryDataDump.[Study Code]
PIVOT QryDataDump.Status;