VW_CTM_PerDiemOrders

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

Order ID

int

null

CTM_Orders.Order ID

Implied ConstraintR

Status

varchar

√

null

ShiftStartDatePST

date

√

null

ShiftMinutesStartToEnd

int

√

null

First Name

varchar

100

√

null

Last Name

varchar

100

√

null

Temp Type

varchar

100

√

null

Client Id

int

√

null

Facility Number

varchar

√

null

Client Name

varchar

200

√

null

Region Name

varchar

√

null

Order Specialty

varchar

√

null

Order Certification

varchar

100

√

null

Floor

varchar

√

null

Note

varchar

4000

√

null

Date/Time Created

varchar

√

null

Order Type

varchar

√

null

Void Date/Time Created

varchar

√

null

Void Note Type Code

varchar

200

√

null

VoidNoteReason

varchar

4000

√

null

Void Note

varchar

8000

√

null

Void Reason

varchar

8000

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_CTM_PerDiemOrders AS

/*********************************************************************
Database:		DataCStore
View:			dbo.VW_CTM_PerDiemOrders
Created:		6/7/2021
Description:    Straight view of underlying orders table.
                The view is named PerDiemOrders because that is
                the scope of the underlying data source. A different
                API call is needed to retrieve "other" orders.
*********************************************************************/

    WITH base AS (
        SELECT [Order ID],
               Status,
               [Shift Start Time],
               CAST(CAST([Shift Start Time] AS DATETIME) AT TIME ZONE 'UTC' AT TIME ZONE
                    'Pacific Standard Time' AS DATETIME) AS ShiftStartTimePST,
               CAST(CAST([Shift Start Time] AS DATETIME) AT TIME ZONE 'UTC' AT TIME ZONE
                    'Pacific Standard Time' AS DATE)     AS ShiftStartDatePST,
               [Shift End Time],
               CAST(CAST([Shift End Time] AS DATETIME) AT TIME ZONE 'UTC' AT TIME ZONE
                    'Pacific Standard Time' AS DATETIME) AS ShiftEndTimePST,
               [Temp Id],
               [First Name],
               [Last Name],
               [Client Id],
               [Client Name],
               [Region Name],
               [Order Specialty],
               [Order Certification],
               Floor,
               [Shift Number],
               Note,
               [Payroll Number],
               [Less Lunch Min],
               [Date/Time Created],
               [Taken By],
               [Booked By User ID],
               [Order Type ID],
               [Order Type],
               City,
               State,
               [Zip Code],
               [Order Source ID],
               [Order Source Name],
               [LT OrderID],
               [Date/Time Modified],
               [Subject ID],
               Subject,
               VMS
        FROM dbo.CTM_Orders)

    SELECT base.[Order ID],
           base.Status,
           ShiftStartDatePST,
           DATEDIFF(mi, ShiftStartTimePST, ShiftEndTimePST) AS ShiftMinutesStartToEnd,
           base.[First Name],
           base.[Last Name],
           temps.[Temp Type],
           base.[Client Id],
           clients.[Facility Number],
           base.[Client Name],
           base.[Region Name],
           base.[Order Specialty],
           base.[Order Certification],
           base.Floor,
           base.Note,
           base.[Date/Time Created],
           base.[Order Type],
           notes.[Date/Time Created] AS [Void Date/Time Created],
           notes.[Note Type Code] AS [Void Note Type Code],
           notes.[Note] AS [VoidNoteReason],
           CASE WHEN notes.[Note] LIKE 'Note:%Reason:%' THEN REPLACE(LEFT(notes.[Note], PATINDEX('%Reason:%', notes.[Note]) - 1), 'Note: ', '')
                WHEN notes.[Note] LIKE 'Note:%' THEN REPLACE(notes.[Note], 'Notes: ', '')
                ELSE NULL END AS [Void Note],
           CASE WHEN notes.[Note] LIKE 'Note:%Reason:%' THEN REPLACE(SUBSTRING(notes.[Note], PATINDEX('%Reason:%', notes.[Note]), 500), 'Reason: ', '')
                WHEN notes.[Note] LIKE 'Reason:%' THEN REPLACE(notes.[Note], 'Reason: ', '')
                ELSE NULL END AS [Void Reason]
    FROM base
    LEFT JOIN dbo.CTM_Clients AS clients
    ON base.[Client Id] = clients.[Client ID]
    LEFT JOIN dbo.CTM_Temps AS temps
    ON base.[Temp Id] = temps.[Temp ID]
    LEFT JOIN dbo.CTM_Notes AS notes
    ON base.[Order ID] = notes.[Order ID]

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views