SET NOCOUNT ON
DECLARE @Loc_STN_ID int,
		@Loc_CTYPE_CODE char(1),
		@Loc_PARAM_XML varchar(1000),
		@Loc_START_DATE datetime,
		@Loc_END_DATE datetime

SET @Loc_STN_ID = @STN_ID
SET @Loc_CTYPE_CODE = @CTYPE_CODE
SET @Loc_PARAM_XML = @PARAM_XML
SET @Loc_START_DATE = @START_DATE
SET @Loc_END_DATE = @END_DATE

DECLARE @Parameters TABLE
(
	ParameterID char(5)
)

--Initialize XML handle
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT, @Loc_PARAM_XML

--load up temporary table
INSERT @Parameters
(
	ParameterID
)

SELECT ID
FROM OPENXML(@hdoc, '/Parameters/Parameter', 1)
WITH (
	ID char(5)
) AS x

DECLARE @dates table (sdate datetime)

INSERT @dates (sdate)
SELECT dateadd(day, number, @Loc_START_DATE)
FROM   master..spt_values
WHERE  type = 'P'
AND    number < datediff(day, @Loc_START_DATE, @Loc_END_DATE + 1)

SELECT PIN.PARAM_NAME,CONVERT(VARCHAR(25), d.sdate, 106) AS DATE,
'' + 
CASE WHEN @Loc_CTYPE_CODE <> '1' THEN 'n/a' 
	ELSE CASE SAMPLE_START_TIME 
		WHEN 0 THEN '1'
		WHEN 100 THEN '2'
		WHEN 200 THEN '3'
		WHEN 300 THEN '4'
		WHEN 400 THEN '5'
		WHEN 500 THEN '6'
		WHEN 600 THEN '7'
		WHEN 700 THEN '8'
		WHEN 800 THEN '9'
		WHEN 900 THEN '10'
		WHEN 1000 THEN '11'
		WHEN 1100 THEN '12'
		WHEN 1200 THEN '13'
		WHEN 1300 THEN '14'
		WHEN 1400 THEN '15'
		WHEN 1500 THEN '16'
		WHEN 1600 THEN '17'
		WHEN 1700 THEN '18'
		WHEN 1800 THEN '19'
		WHEN 1900 THEN '20'
		WHEN 2000 THEN '21'
		WHEN 2100 THEN '22'
		WHEN 2200 THEN '23'
		WHEN 2300 THEN '24'
	END
END AS 'Hour', 
STI.STN_NAME, METHOD_NAME METHOD, UNIT_ABBREV UNIT, DATA_VALUE, DATA_FLAG

FROM @dates d LEFT OUTER JOIN SAMPLE_DATA SMPD ON d.sdate = SMPD.SAMPLE_START_DATE

--FROM SAMPLE_DATA SMPD LEFT OUTER JOIN @dates d ON d.sdate = SMPD.SAMPLE_START_DATE
	INNER JOIN STATION_DATA STN
	ON SMPD.SAMPLE_NUM = STN.DATA_SAMPLE_NUM

	INNER JOIN COLLECTIONTYPE_CODES CTC
	ON SMPD.SAMPLE_TYPE = CTC.CTYPE_CODE
	AND SMPD.SAMPLE_TYPE = @Loc_CTYPE_CODE

	INNER JOIN PARAMETER_INFO PIN
	ON PIN.PARAM_ID = STN.DATA_PARAMETER 
	
	INNER JOIN @Parameters p
	ON PIN.PARAM_ID = p.ParameterID
	
	INNER JOIN METHOD_CODES MCD
	ON STN.DATA_METHOD = MCD.METHOD_CODE

	INNER JOIN UNIT_CODES UNC
	ON STN.DATA_UNIT = UNC.UNIT_CODE
	
	INNER JOIN STATION_INFO STI
	ON SMPD.SAMPLE_STATION = STI.STN_ID
	AND SMPD.SAMPLE_STATION = @Loc_STN_ID

	ORDER BY d.sdate, CONVERT(int,SAMPLE_START_TIME), PARAM_NAME
	SET NOCOUNT OFF
END