Sample SQL Queries
Below are samples of SQL queries used to create views for your dashboards
ALL SQL View DV_WEEK_DAYS
/*
VIEW: DV_WEEK_DAYS
AMMENDED: 03-08-2022
AUTHOR: ALEMBA PTY LTD
CLIENT: This script is for demonstration purposes and is not supported as product.
PURPOSE: This standard view performs the following:
1. Creates a week day structure for the previous week, current week, and
following 3 weeks.
2. Uses the FUNCTION TZDate to base the structure on the current datetime,
adjusted by the time zone setting of the "DASHBOARD" analyst in ASM.
The Time Zone that dates are emant to be adjusted to needs to be defined against
the "DASHBOARD" Analyst account.
SYNTAX: SELECT * FROM DV_WEEK_DAYS
VERSION: Last tested against v10.5.6
DATABASE: SQL Server
CHANGES: 03-08-2022 Added the WeekType column to present the week as " Previous", "Current",
"Week 2", etc
02-08-2022 Modified the Week column to present the format "WK ## (dd Mon)" where
the date is the Monday of that week.
28-07-2022 Created the view.
*/
-- DROP VIEW DV_WEEK_DAYS
CREATE VIEW DV_WEEK_DAYS AS
/* Days of the week for the Previous Week */
SELECT
DATEPART(week, dbo.TZDate(GETDATE())-7) Week_Num,
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())-7) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), -7),113) + ')' Week,
'Previous' WeekType,
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())-7), 0) Date_Of_Week,
'Monday' Day_Of_Week,
'1 Monday' NumDay_Of_Week
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())-7),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())-7) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), -7),113) + ')',
'Previous',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())-7), 1),
'Tuesday',
'2 Tuesday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())-7),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())-7) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), -7),113) + ')',
'Previous',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())-7), 2),
'Wednesday',
'3 Wednesday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())-7),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())-7) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), -7),113) + ')',
'Previous',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())-7), 3),
'Thursday',
'4 Thursday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())-7),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())-7) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), -7),113) + ')',
'Previous',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())-7), 4),
'Friday',
'5 Friday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())-7),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())-7) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), -7),113) + ')',
'Previous',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())-7), 5),
'Saturday',
'6 Saturday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())-7),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())-7) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), -7),113) + ')',
'Previous',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())-7), 6),
'Sunday',
'7 Sunday'
/* Days of the week for the Current Week */
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 0),113) + ')',
'Current',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 0),
'Monday',
'1 Monday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 0),113) + ')',
'Current',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 1),
'Tuesday',
'2 Tuesday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 0),113) + ')',
'Current',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 2),
'Wednesday',
'3 Wednesday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 0),113) + ')',
'Current',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 3),
'Thursday',
'4 Thursday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 0),113) + ')',
'Current',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 4),
'Friday',
'5 Friday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 0),113) + ')',
'Current',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), 5