Database

SELECT Table1.AutoID,'RA No' = CASE WHEN Table1.RANo <= 9 THEN Table2.Assessment_Name + '/' + cast(Table1.Year_Year as varchar) + '/00' + cast(Table1.RANo as varchar) WHEN Table1.RANo >= 10 and Table1.RANo<=99 THEN Table2.Assessment_Name + '/'  + cast(Table1.Year_Year as varchar) + '/0' + cast(Table1.RANo as varchar) WHEN Table1.RANo >= 100 THEN Table2.Assessment_Name + '/'  + cast(Table1.Year_Year as varchar) + '/' + cast( + Table1.RANo as varchar) END,

'BU (consolidated Lev 1 - 4)' = 
if len(Table1.BULevel1) <> 0 BEGIN SELECT Table1.BULevel1 + '/' from Table1 where len(Table1.BULevel1) <>0 END
if len(Table1.BULevel2) <> 0 BEGIN SELECT Table1.BULevel2 + '/' from Table1 where len(Table1.BULevel2) <>0 END
if len(Table1.BULevel3) <> 0 BEGIN SELECT Table1.BULevel3 + '/' from Table1 where len(Table1.BULevel3) <>0 END
if len(Table1.BULevel4) <> 0 BEGIN SELECT Table1.BULevel4 + '/' from Table1 where len(Table1.BULevel4) <>0 END

,Table1.RATitle AS [RA Name],Table1.RADesc AS [RA Desc],Table1.SME AS [SME],Tab_RiskRate.RiskRate_Name AS [Risk Rating (Pre Miti)],Tab_Location.Location_Name AS Location,Table1.PlannedMonth AS [Planned month],Table1.RescheduleMonth AS [Rescheduled Month],Table1.DateStarted AS [Date started],Table1.RAInitiated AS [No. of days since RA initiated],Table1.Signoff AS [Date Signed Off],Table1.ActualDate AS [Project Live Date],Tab_Status.Status_Name AS Status FROM Table1 INNER JOIN Table2 ON Table1.Assessment = Table2.AutoID INNER JOIN Tab_RiskRate ON Table1.PreMitigating = Tab_RiskRate.AutoID INNER JOIN Tab_Location ON Table1.Location = Tab_Location.AutoID INNER JOIN Tab_Status ON Table1.Status = Tab_Status.AutoID WHERE (Table1.Mode = 1)