Workspace
Archie Oppong/

Course Notes: Time Series Analysis in SQL Server

0
Beta
Spinner

Course Notes

Use this workspace to take notes, store sample queries, and build your own interactive cheat sheet!

Note that you need to connect your SQL cells to an integration to run a query.

  • You can use a sample integration from the dropdown menu. This includes the Course Databases integration, which contains tables you used in our SQL courses.
  • You can connect your own integration by following the instructions provided here.

Take Notes

Add notes here about the concepts you've learned and SQL cells with code you want to keep.

Add your notes here

WORKING WITH DATES & TIMES: 2/19/23

Building Dates GETDATE() OR GETUTCDATE() returns a date in local time or UTC time SYSDATETIME() OR SYSUTCDATETIME returns current time as date type 2

Parsing dates with date parts Functions: DATEPART(): returns numeric value of part wanted DATENAME(): gives string value

Parts: Year/Month/ Day Day of year day of week week of year ISO week of year Minute/ Seconds

ADDING AND SUBTRACTING DATES: DATEADD(DAY, 1, @VARIABLENAME) DATEADD(DAY, -1, @VARIABLENAME)

COMPARING DATES DATEDIFF() Returns INT and rounds up can take args HOUR and MINUTE

fORMATTING DATES FOR REPORTING: Cast(): useful for converting one data type to another. eg, INT to DEC cast functions can be nested cast(cast(@CubsWinWorldSeries AS date)AS NVARCHAR(30)) eg, CAST(@SomeDate AS NVARVAHR(30)) AS DateToString

Convert(): For converting Data types, has some control over foramtting. Takes 3 parameters(data type, input, optional style) eg, CONVERT(NVARCHAR(30), @SomeDate, 0) AS DefaultForm OPTIONAL STYLE: 0: prints out just like CAST 1/101: print in US date format 120: print ODBC format to the sec 3/103: Prints British/French date format 4/104: Prints German date format 11/111: Prints Japanese date format 20/120: ODBC standard(121 for ms) 126: iso8601 YYYY-MM-DD HH:MI:SS.MMM

Format(): useful for formatting date or number in a particular way for reporting. Much more flexible formatting dates to strings than CAST() or CONVERT(). Uses .NET framework for conversion can be slower as you process more rows Format takes 3 parameters; input value, foramt code, optional culture eg, FORMAT(@SomeDate, 'd', 'en-US') AS US_d 'D' build long dates 'd' builds short dates

WORKING WITH CALENDAR TABLES: Calendar table: stores date information for easy retrieval. Contents of a calendar table include: General columns, Calendar Year, Fiscal Year, Specialized Columns APPLY(): executes a function for each row in a result set

Unknown integration
DataFrameavailable as
df
variable
--Functions
--DATEPART()
SELECT
    DATEPART(YEAR, @dt) AS TheYear;
    
--DATENAME()
SELECT
    DATENAME(MONTH, @dt) AS TheMonth;
    
--DETERMINING THE YEAR MONTH AND DAY
DECLARE
	@SomeTime DATETIME2(7) = SYSUTCDATETIME();

-- Retrieve the year, month, and day
SELECT
	YEAR(@SomeTime) AS TheYear,
	MONTH(@SomeTime) AS TheMonth,
	DAY(@SomeTime) AS TheDay;
    
--Filling in appropriate dateparts
DECLARE
	@BerlinWallFalls DATETIME2(7) = '1989-11-09 23:49:36.2294852';

-- Fill in each date part
SELECT
	DATEPART(YEAR, @BerlinWallFalls) AS TheYear,
	DATEPART(MONTH, @BerlinWallFalls) AS TheMonth,
	DATEPART(DAY, @BerlinWallFalls) AS TheDay,
	DATEPART(DAYOFYEAR, @BerlinWallFalls) AS TheDayOfYear,
    -- Day of week is WEEKDAY
	DATEPART(WEEKDAY, @BerlinWallFalls) AS TheDayOfWeek,
	DATEPART(WEEK, @BerlinWallFalls) AS TheWeek,
	DATEPART(SECOND, @BerlinWallFalls) AS TheSecond,
	DATEPART(NANOSECOND, @BerlinWallFalls) AS TheNanosecond;
    
-- handling leap years
DECLARE
	@PostLeapDay DATETIME2(7) = '2012-03-01 18:00:00',
    @TwoDaysAgo DATETIME2(7);

SELECT
	@TwoDaysAgo = DATEADD(DAY, -2, @PostLeapDay);

SELECT
	@TwoDaysAgo AS TwoDaysAgo,
	@PostLeapDay AS SomeTime,
    -- Fill in the appropriate function and date types
	DATEDIFF(DAY, @TwoDaysAgo, @PostLeapDay) AS DaysDifference,
	DATEDIFF(HOUR, @TwoDaysAgo, @PostLeapDay) AS HoursDifference,
	DATEDIFF(MINUTE, @TwoDaysAgo, @PostLeapDay) AS MinutesDifference;

--NESTING USING CAST()
DECLARE
	@CubsWinWorldSeries DATETIME2(3) = '2016-11-03 00:30:29.245';

SELECT
	cast(cast(@CubsWinWorldSeries AS date) AS NVARCHAR(30)) AS DateStringForm;

--USING CONVERT
 DECLARE
	@CubsWinWorldSeries DATETIME2(3) = '2016-11-03 00:30:29.245';

SELECT
	CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 0) AS DefaultForm,
	CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 3) AS UK_dmy,
	CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 1) AS US_mdy,
	CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 103) AS UK_dmyyyy,
	CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 101) AS US_mdyyyy; 
    
--FORMATTING DATES WITH FORMAT()
DECLARE
	@Python3ReleaseDate DATETIME2(3) = '2008-12-03 19:45:00.033';

SELECT
	-- Fill in the function call and format parameter
	FORMAT(@Python3ReleaseDate, 'd', 'en-US') AS US_d,
	FORMAT(@Python3ReleaseDate, 'd', 'de-DE') AS DE_d,
	-- Fill in the locale for Japan
	FORMAT(@Python3ReleaseDate, 'd', 'jp-JP') AS JP_d,
	FORMAT(@Python3ReleaseDate, 'd', 'zh-cn') AS CN_d;

DECLARE
	@Python3ReleaseDate DATETIME2(3) = '2008-12-03 19:45:00.033';
    
SELECT
	-- 20081203
	FORMAT(@Python3ReleaseDate, 'yyyyMMdd') AS F1,
	-- 2008-12-03
	FORMAT(@Python3ReleaseDate, 'yyyy-MM-dd') AS F2,
	-- Dec 03+2008 (the + is just a "+" character)
	FORMAT(@Python3ReleaseDate, 'MMM dd+yyyy') AS F3,
	-- 12 08 03 (month, two-digit year, day)
	FORMAT(@Python3ReleaseDate, 'MM yy dd') AS F4,
	-- 03 07:45 2008.00
    -- (day hour:minute year.second)
	FORMAT(@Python3ReleaseDate, 'dd hh:mm yyyy.ss') AS F5;
    
--Using calendar tables
SELECT
	c.Date
FROM dbo.calendar c
WHERE
	c.MonthName = 'December'
	AND c.DayName = 'Tuesday'
	AND c.CalendarYear BETWEEN 2008 AND 2010
ORDER BY
	c.Date;

-- Find fiscal week 29 of fiscal year 2019
SELECT
	c.Date
FROM dbo.Calendar c
WHERE
    -- Instead of month, use the fiscal week
	c.FiscalWeekOfYear = 29
    -- Instead of calendar year, use fiscal year
	AND c.FiscalYear = 2019
ORDER BY
	c.Date ASC;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Building Dates From Parts 2/20/23

SQL server has 6 functions to build dates from components: DATEFROMPARTS(YEAR, MONTH, DAY) TIMEFROMPARTS(hours, minute, second, fraction, precision) DATETIMEFROMPARTS(year, month, day, hour, minute, second, ms) DATETIME2FROMPARTS(year, month, day, hour, minute, second, fraction, precision) SMALLDATETIMEFROMPARTS(year, month, day, hour, minute) DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, second, fraction, hour_offset, muinute_offset, precision)

Translating Date Strings: use cast() as default. Cast func is language and locale-specific eg. SELECT CAST('09/14/99' AS Date)

Parsse() useful for translating non default locales. use for small datasets or if you are willing to trade speed for flexibility. eg. SELECT PARSE('25 Dezember 2014' AS DATE USING 'de-de') AS Weihnachten;

2/21/23 DATETIMEOFFSET DATETIMEOFFSET made of 3 components: Date Time Utc Offset

Swiwtchoffset used to change time zone of a DateTime or DateTime2 Swiwtchoffset takes 2 parameters: the date string as input & time zone Swiwtchoffset allows for changes to time in input string sys.time_zone_info : to find out time zones

TODATETIMEOFFSET() to turn an existing date into a date type with an offset.

Handling invalid Dates: Safe Functions: try_Cast() Try_Convert() Try_Parse()

Only use Try_Parse when performance is not critical. Try_Cast & Try_Convert are faters than Try_Parse, but Try_Parse is more efficient.

2/23/23 ABasic aggregate funcs: COUNT DETERMINES THE TOTAL AMOUNT OF ROWS Count calls include: COUNT() COUNT(BIG) COUNT(DISTINCT): counts unique elements

Non-null values: COUNT(d.TR): if you specify a column gives you values where that column is not null Count(NULLIF(d.Tr, 1990))

Statistical aggreagte funcs: AVG() calcs mean STDEV() calcs standard deviation STDEVP() calc population standard deviation VAR() variance VARP() population variance

PERCENTILE_CONT() func is used to calc median has a parameter that goes into parentheses

3/23/23

Downsampling and upsampling data:

Downsampling: Upsampling: Aggregate data Disaggregate data sums or counts results need allocation rule Acceptable for most purposes for data generation, calculated averages

  • AI Chat
  • Code