History

Returns historical weather data from Weather Underground using SQL Server

Prerequisites:

See SQLHTTP easy setup for Weather Underground
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE usp_WeatherUnderground_History(	
		@Date 	date,
		@ZipCode varchar(20),
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

DECLARE @HistorySegment char(16)
SET @HistorySegment = 'history_' + CONVERT(char(8), DATEADD(day, -1, @Date), 112)

DECLARE @URL nvarchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'http://api.wunderground.com/api'
SET @ZipCode = @ZipCode + '.json' COLLATE SQL_Latin1_General_CP1_CI_AS

EXEC SQLHTTP.net.UrlBuilder 	@URL OUTPUT,
				'wunderground',
				'#APIKey',
				@HistorySegment, 'q', @ZipCode

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest   @HttpSessionID,
                               @URL = @URL,
                               @Method = 'GET',
                               @StatusCode = @StatusCode OUTPUT,
                               @StatusDescription = @StatusDescription OUTPUT,
                               @Response = @Response OUTPUT
GO

CREATE PROCEDURE usp_WeatherUnderground_History(

@Date date,

@ZipCode varchar(20),

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @HistorySegment char(16)

SET @HistorySegment = 'history_' + CONVERT(char(8), DATEADD(day, -1, @Date), 112)

DECLARE @URL nvarchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'http://api.wunderground.com/api'

SET @ZipCode = @ZipCode + '.json' COLLATE SQL_Latin1_General_CP1_CI_AS

EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT,

'wunderground',

'#APIKey',

@HistorySegment, 'q', @ZipCode

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'GET',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_WeatherUnderground_History	
		@Date = '2/2/2016', --enter date
		@ZipCode = '90210', --enter zip code
		@Response = @Response OUTPUT, 
		@StatusCode = @StatusCode OUTPUT, 
		@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	BEGIN
		
		DECLARE @X xml
		SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

		--The XPath syntax below was easily generated by executing the following commands:
		--EXEC SQLHTTP.net.XQueryHelper @X
		--and then executing: 
		--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/history[1]/dailysummary[1]/JsonObject[1]' 

		SELECT T.C.value(N'date[1]/@hour', N'nvarchar(MAX)') AS [hour]
		      ,T.C.value(N'@rain', N'nvarchar(MAX)') AS [rain]
		      ,T.C.value(N'@snow', N'nvarchar(MAX)') AS [snow]
		      ,T.C.value(N'@maxtempi', N'nvarchar(MAX)') AS [maxtempi]
		      ,T.C.value(N'@maxtempm', N'nvarchar(MAX)') AS [maxtempm]
		FROM @X.nodes(N'/JsonObject/history[1]/dailysummary[1]/JsonObject[1]') T(C)
		
	END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_WeatherUnderground_History

@Date = '2/2/2016', --enter date

@ZipCode = '90210', --enter zip code

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @X xml

SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/history[1]/dailysummary[1]/JsonObject[1]'

SELECT T.C.value(N'date[1]/@hour', N'nvarchar(MAX)') AS [hour]

,T.C.value(N'@rain', N'nvarchar(MAX)') AS [rain]

,T.C.value(N'@snow', N'nvarchar(MAX)') AS [snow]

,T.C.value(N'@maxtempi', N'nvarchar(MAX)') AS [maxtempi]

,T.C.value(N'@maxtempm', N'nvarchar(MAX)') AS [maxtempm]

FROM @X.nodes(N'/JsonObject/history[1]/dailysummary[1]/JsonObject[1]') T(C)

END

hour rain snow maxtempi maxtempm

------------- ----------- ----------- ----------- ------------

12 0 0 57 14



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.