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)

CREATE PROCEDURE dbo.InsertCountry
	(
		@Country	NVARCHAR(200)
	)
AS

BEGIN
	DECLARE @LastID = BIGINT
	SET @LastID = (SELECT MAX(Country_ID) FROM Countries
	IF @LastID IS NULL SET @LastID = '1'
	ELSE SET @LastID = (@LastID) + 1
	RETURN @LastID
END
BEGIN
	INSERT INTO Countries
		(
			Country_ID,
			Country
		)
	VALUES
		(
			@LastID,
			@Country
		)
END