Skip to content

Query: Qry Report RAG

Type: Select
Has Parameters: Yes
Has Joins: No
Has Subquery: No

Tables Referenced

  • vwPurpleReportRAG
  • vwPurpleSumTotalReplacedBilledtoDatebyPrinProjectPillar
  • vwPurpleSumTotalReplacedSelectionFeebyPrinProjectPillar

Columns Selected

  • vwPurpleReportRAG.[Principal Project Status ID]
  • vwPurpleReportRAG.[Lump Sum Allocation]
  • vwPurpleReportRAG.[Principal Project ID]
  • [Principal] & \
  • vwPurpleReportRAG.[BBBEE Pillar]
  • vwPurpleReportRAG.[Brief Date]
  • vwPurpleReportRAG.[Number of Companies Required]
  • vwPurpleReportRAG.[Entrepreneur Delivery Date]
  • vwPurpleReportRAG.[Amount Sponsored]
  • DateDiff(\
  • IIf([Entrepreneur Delivery Date]<Date(),DateDiff(\
  • DateDiff(\
  • IIf(IsNull([Total Sponsorship Amt]),0,[Total Sponsorship Amt])+IIf(IsNull([Total
  • [Amount Sponsored]-[Total Sponsorship]
  • IIf([Total Days]=0,0,[Current Days]/[Total Days])
  • IIf([Amount Sponsored]=0,0,[Total Sponsorship]/[Amount Sponsored])
  • IIf(IsNull([Number Active Allocated Companies]),0,[Number Active Allocated Compa
  • IIf(IsNull([Number Exited Companies]),0,[Number Exited Companies])
  • IIf(IsNull([Unallocated Companies]),0,[Unallocated Companies])
  • [Number of Companies Required]-[Number Active]-[Number Exited]-[Number Unallocat
  • IIf(IsNull([Number of Replacements Needed]),0,[Number of Replacements Needed])

Order By

[Principal] & \

SQL

SELECT vwPurpleReportRAG.[Principal Project Status ID], vwPurpleReportRAG.[Lump Sum Allocation], vwPurpleReportRAG.[Principal Project ID], [Principal] & \, vwPurpleReportRAG.[BBBEE Pillar], vwPurpleReportRAG.[Brief Date], vwPurpleReportRAG.[Number of Companies Required], vwPurpleReportRAG.[Entrepreneur Delivery Date], vwPurpleReportRAG.[Amount Sponsored], DateDiff(\, IIf([Entrepreneur Delivery Date]<Date(),DateDiff(\, DateDiff(\, IIf(IsNull([Total Sponsorship Amt]),0,[Total Sponsorship Amt])+IIf(IsNull([Total, [Amount Sponsored]-[Total Sponsorship], IIf([Total Days]=0,0,[Current Days]/[Total Days]), IIf([Amount Sponsored]=0,0,[Total Sponsorship]/[Amount Sponsored]), IIf(IsNull([Number Active Allocated Companies]),0,[Number Active Allocated Compa, IIf(IsNull([Number Exited Companies]),0,[Number Exited Companies]), IIf(IsNull([Unallocated Companies]),0,[Unallocated Companies]), [Number of Companies Required]-[Number Active]-[Number Exited]-[Number Unallocat, IIf(IsNull([Number of Replacements Needed]),0,[Number of Replacements Needed]) FROM [vwPurpleReportRAG], [vwPurpleSumTotalReplacedBilledtoDatebyPrinProjectPillar], [vwPurpleSumTotalReplacedSelectionFeebyPrinProjectPillar]