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]