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