ALTER PROCEDURE [dbo].[SelectAllContractorRates]
	@ContractorID varchar(max),
	@JobSeverityID varchar(max)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @query1 nvarchar(max)
DECLARE @time nvarchar(max)
--select * from JobRateTypes

SELECT @time= STUFF(( SELECT DISTINCT
						'],[' + ltrim(JobRateType)
			
                        FROM JobRateTypes
                       
                        ORDER BY '],[' + ltrim(JobRateType)
                        FOR XML PATH('')
						), 1, 2, '') + ']'
			


SET @query1 =
'
SELECT 
	CASE WHEN FirstHour = 1 THEN ''First Hour'' ELSE ''Additional Hours'' END AS [Time],
	*
	FROM 
	(
	SELECT [JobRateType],Rate,FirstHour
	FROM
	ContractorDefaultRates cr
	INNER JOIN JobRateTypes jrt ON
	cr.JobRateTypeID = jrt.JobRateTypeID
	INNER JOIN JobSeverities js ON
	cr.JobSeverityID = js.JobSeverityID
	WHERE
	contratorID = '+@ContractorID+' AND
	js.JobSeverityID = '+@JobSeverityID+' AND
	jrt.BankHoliday = 0
	) p PIVOT ( SUM([Rate])
                    FOR [JobRateType] 
                      IN ('+@time+')
                  ) AS pvt
    ORDER BY [Time] DESC
    

'
--execute  (@query1);
exec sp_executesql @query1;
	
END