James-McNeill

-- Time Series functions
SELECT *,
	AVG(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
		) sma3,
	LAG(t1.Value, 3, 0)
		OVER(

James-McNeill

-- Ranking functions
SELECT *,
	RANK() 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Value DESC
		) rankValue,
	ROW_NUMBER() 
		OVER(
		PARTITION BY t1.Area, t1.Property_type

James-McNeill

-- Analytical functions (Other)
SELECT *,
	LEAD(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) leadValue,
	LEAD(t1.Value, 1, 0) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type

James-McNeill

-- Analytical functions (LAG)
SELECT *,
	LAG(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) lagValue,
	LAG(t1.Value, 3) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type

James-McNeill

-- Aggregate functions
SELECT *,
	AVG(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) avgValue,
	COUNT(*)
		OVER(
		PARTITION BY t1.Area, t1.Property_type

James-McNeill

DECLARE @date1 NVARCHAR(20) = '30.03.2019';
-- Set the language
SET LANGUAGE 'Dutch';

SELECT
@date1 AS initial_date,
-- Check that the date is valid
ISDATE(@date1) AS is_valid,
-- Select the name of the month
DATENAME(MONTH, @date1) AS month_name;

James-McNeill

DECLARE @date1 NVARCHAR(20) = '10.01.2022';

-- Set the date format and check if the variable is a date
SET DATEFORMAT mdy;
--PRINT @date1;
SELECT ISDATE(@date1) AS result
	, @date1 AS review_date;
GO

-- By using the GO keyword we have segmented the code and now the interpreter does not know 

James-McNeill

-- Declare the initial value
DECLARE @counter INT;
SET @counter = 20;

-- Print initial value
SELECT @counter AS _COUNT;

-- Create a loop
BEGIN;
-- Loop code starting point

James-McNeill

-- Declare the variable (a SQL Command, the var name, the datatype)
DECLARE @counter INT;
-- Set the counter to 20
SET @counter = 20;

-- Print the initial value
SELECT @counter AS _COUNT;

-- Select and increment the counter by one
SELECT @counter = @counter + 1;

James-McNeill

-- Declare your variables
DECLARE @start date
DECLARE @stop date
-- SET the relevant values for each variable
SET @start = '2021-06-01'
SET @stop = GETDATE()

-- Run select statement
SELECT t1.[DATETIME], COUNT(*) AS vol
FROM Medium.dbo.Earthquakes t1