SELECT 
	(select top 	'1'1 PIn from PW001P01) as [PIN],

	'1'	(select top 1 NUMORGID from PWORG) as [NUMORGID],

	'1' as [EMPLOYMENTSTARTDATE],

	'1' as [EMPLOYMENTENDDATE ],
	C0.REGULATIVECODE as 'Table'[Table Code],
	C0.REGULATIVENAME as[Table Table_Name],
	C1.PAYSCALECODE as[Payscale 'Payscale_Code'],
	C1.PAYSCALENAME as[Payscale 'Payscale_Name'],
	convert CONVERT(varcharVARCHAR, LN.Line) + ' ' + LN.NAME as[Rate 'Rate_Line'],
	--Cl.Col as '[Seniority_Column' Column],
	Cl.Seniority [Seniority Value],
	C0.CURRENCYCODE as 'Currency',
	Isnull(TRY_CONVERT(Decimal [Currency],
	CASE WHEN C1R.Rate iS NULL or C1R.Rate='' then  ISNULL(TRY_CAST(REPLACE(C0R.Formula, ',', '.') AS DECIMAL(10,2),C1R.Rate), '0.00')
as [Rate],
	CASE when (C1R		 WHEN (C1R.Rate iS NULL or C1R.Rate='') and (C0R.Formula is NULL or C1RC0R.Formula = '') then convert (Decimal(10,2),THEN '0.00')
		ELSE  TRY_CONVERT(DecimalCAST(REPLACE(C1R.Rate, ',', '.') AS DECIMAL(10, 2),C1R.Rate) 
	END Formula	
from [Rate]                    
FROM   PWPSC000 C0								--payscale 
left jointable properties table
       LEFT JOIN PWPSC001 C1 on					--payscale code properties table
            ON  C1.SEQNO = C0.SEQNO
left join
       LEFT JOIN PWPSC_RATES_LINES LN on			--payscale rate lines list
            ON  LN.SEQNO = C0.SEQNO
left join       LEFT JOIN PWPSC_RATES_COLUMNS CL on CL.SEQNO=C0.SEQNO
left join			--payscale columns list
            ON  CL.SEQNO = C0.SEQNO
       LEFT JOIN PWPSC000_RATES C0R				--payscale rate value table
            ON  C0R.SeqNo = C0.SEQNO
            AND LN.LineId = C0R.LineId
            AND CL.ColId = C0R.ColId
       LEFT JOIN PWPSC001_RATES C1R on				--payscale rate value table
            ON  C1R.SeqNo =C0 C1.SEQNO and 
            AND LN.LineId = C1R.LineId
and            AND CL.ColId = C1R.ColId and
            AND C1.ID = C1R.ID
WHERE   C0.options not like '%O%' 
--and cl.Seniority=0
--and C0.REGULATIVECODE='FC-1OFF'  
--and C1.PAYSCALECODE='FIROFF'