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),
'Saturday',
'6 Saturday'
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())), 6),
'Sunday',
'7 Sunday'
/* Days of the week for the Next 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) + ')',
'Week 2',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+7), 0),
'Monday',
'1 Monday'
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) + ')',
'Week 2',
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) + ')',
'Week 2',
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) + ')',
'Week 2',
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) + ')',
'Week 2',
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) + ')',
'Week 2',
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) + ')',
'Week 2',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+7), 6),
'Sunday',
'7 Sunday'
/* Days of the week for 3rd Week */
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+14),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+14) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +14),113) + ')',
'Week 3',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+14), 0),
'Monday',
'1 Monday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+14),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+14) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +14),113) + ')',
'Week 3',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+14), 1),
'Tuesday',
'2 Tuesday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+14),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+14) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +14),113) + ')',
'Week 3',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+14), 2),
'Wednesday',
'3 Wednesday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+14),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+14) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +14),113) + ')',
'Week 3',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+14), 3),
'Thursday',
'4 Thursday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+14),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+14) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +14),113) + ')',
'Week 3',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+14), 4),
'Friday',
'5 Friday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+14),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+14) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +14),113) + ')',
'Week 3',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+14), 5),
'Saturday',
'6 Saturday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+14),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+14) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +14),113) + ')',
'Week 3',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+14), 6),
'Sunday',
'7 Sunday'
/* Days of the week for 4th Week */
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+21),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+21) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +21),113) + ')',
'Week 4',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+21), 0),
'Monday',
'1 Monday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+21),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+21) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +21),113) + ')',
'Week 4',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+21), 1),
'Tuesday',
'2 Tuesday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+21),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+21) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +21),113) + ')',
'Week 4',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+21), 2),
'Wednesday',
'3 Wednesday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+21),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+21) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +21),113) + ')',
'Week 4',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+21), 3),
'Thursday',
'4 Thursday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+21),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+21) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +21),113) + ')',
'Week 4',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+21), 4),
'Friday',
'5 Friday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+21),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+21) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +21),113) + ')',
'Week 4',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+21), 5),
'Saturday',
'6 Saturday'
UNION ALL
SELECT
DATEPART(week, dbo.TZDate(GETDATE())+21),
'WK ' + CAST(DATEPART(week, dbo.TZDate(GETDATE())+21) AS VARCHAR(2)) + ' (' + CONVERT(VARCHAR(6),DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())), +21),113) + ')',
'Week 4',
DATEADD(wk, DATEDIFF(wk,0,dbo.TZDate(GETDATE())+21), 6),
'Sunday',
'7 Sunday'
ALL SQL Func TZDate
/*
FUNCTION: TZDate
AMMENDED: 29-07-2022
AUTHOR: ALEMBA PTY LTD
CLIENT: This script is for demonstration purposes and is not supported as product.
PURPOSE: This standard function performs the following:
1. Interogates the Server Time Zone, set in the System Administration, for it's
selected time zone, and sets the variable @nServerTZadj to the minutes
adjustment for that time zone.
2. Interogates the "DASHBOARD" analyst account for it's selected time zone, and
sets the variable @nUserTZadj to the minutes adjustment for that time zone.
3. Calulates the @nTimeAdj by subtracting the the Server TZ adjustment from
the User TZ adjustment.
4. Adds the @nTimeAdj to the supplied @dDateTime value.
5. Returns the updated @dDateTime value.
The Time Zone that dates are emant to be adjusted to needs to be defined against
the "DASHBOARD" Analyst account.
SYNTAX: SELECT dbo.TZDate(CL_CALL_LOGGING.TIMEDATE_CALL_LOG) FROM CL_CALL_LOGGING
VERSION: Last tested against v10.5.6
DATABASE: SQL Server
CHANGES: 29-07-2022 Modifed to calculate the difference in adjustments between Server Time
Zone (set in System Administration) and User Time Zone.
Added GRANT EXECUTE statement to allow access for read-only DB accounts.
28-07-2022 Created the function to adjust datetime fields to the relavent time zone.
*/
-- DROP FUNCTION TZDate
CREATE FUNCTION TZDate
(
@dDateTime DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@nServerTZadj INT,
@nUserTZadj INT,
@nTimeAdj INT
SELECT @nServerTZadj = -BIAS FROM SA_TIMEZONE WHERE REF = (SELECT NUMBER_STATUS FROM SU_SYS_PARAMETERS WHERE PARAMETER = 'ServerTimeZone')
SELECT @nUserTZadj = -BIAS FROM SA_TIMEZONE WHERE REF = (SELECT TIME_ZONE_REF FROM AR_PERSON WHERE USER_ID = 'DASHBOARD')
SET @nTimeAdj = @nUserTZadj - @nServerTZadj
SELECT @dDateTime = DATEADD(MINUTE, @nTimeAdj, @dDateTime)
RETURN @dDateTime
END
GO
/*
For a dashboard SQL account that only has db_datareader access to the database, the account will
need to be granted EXECUTE privileges to the FUNCTION to allow the function to be called.
If you don't, you will get the following error when you attempt to execute the query:
Unable to retrieve schema. Reason: The EXECUTE permission was denied on the object 'TZDate'...
SYNTAX: GRANT EXECUTE [dbo].[functionname] TO [dbreaderaccountname]
*/
-- REVOKE EXECUTE ON [dbo].[TZDate] TO [alembadbreader]
GRANT EXECUTE ON [dbo].[TZDate] TO [alembadbreader]