Views [dbo].[LocalServices]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnNo
Created6:18:18 PM Tuesday, February 27, 2007
Last Modified7:37:48 PM Tuesday, September 24, 2024
Columns
Name
LocalServicesLocServFID
LocalServicesOrdersFID
Service
ServiceStatus
ServiceDate
WorkTicket
JobStartTime
JobEndTime
TotalCrewHours
HoursPerMan
HoursDrive1Way
Quantity
EstimatedCrew
TotalCrew
TotalEquip
OriginDestination
LocServBranchName
LocServBranchID
LocServBranchAddress
LocServBranchCity
LocServBranchState
LocServBranchZip
LocServBranchPhone
LocServWorkTicketTitle
CreatedOn
CreatedByFirstName
CreatedByLastName
LastEditedOn
LastEditedByFirstName
LastEditedByLastName
SubmittedOn
SubmittedByFirstName
SubmittedByLastName
ClearedOn
ClearedByFirstName
ClearedByLastName
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
SET QUOTED_IDENTIFIER OFF
GO
/**
*    
*    Description: Gathers all of the basic information about all services
*/


CREATE view [dbo].[LocalServices]
(
    LocalServicesLocServFID,

    --General Service Information
    LocalServicesOrdersFID,
    Service,
    ServiceStatus,
    ServiceDate,
    WorkTicket,
    JobStartTime,
    JobEndTime,
    TotalCrewHours,
    HoursPerMan,
    HoursDrive1Way,
    Quantity,
    EstimatedCrew,
    TotalCrew,
    TotalEquip,
    OriginDestination,

    --Branch Information
    LocServBranchName,
    LocServBranchID,
    LocServBranchAddress,
    LocServBranchCity,
    LocServBranchState,
    LocServBranchZip,
    LocServBranchPhone,
    LocServWorkTicketTitle,

    --Dates and Names
    CreatedOn,
    CreatedByFirstName,
    CreatedByLastName,
    LastEditedOn,
    LastEditedByFirstName,
    LastEditedByLastName,
    SubmittedOn,
    SubmittedByFirstName,
    SubmittedByLastName,
    ClearedOn,
    ClearedByFirstName,
    ClearedByLastName
)
as

select distinct
    LocalServicesLocServFID    = mainLocServ.PriKey,

    --General Service Information
    LocalServicesOrdersFID = mainLocServ.OrdPriKey,
    Service = ServType.Service,
    ServiceStatus = LSStatus.Status,
    ServiceDate = mainLocServ.ServiceDate,
    WorkTicket = mainLocServ.WkTicketNo,
    JobStartTime = mainLocServ.JobStart,
    JobEndTime = mainLocServ.JobEnd,
    TotalCrewHours = mainLocServ.TotCrewHrs,
    HoursPerMan = mainLocServ.HrsPerMan,
    HoursDrive1Way = mainLocServ.HrsDrv1Way,
    Quantity = mainLocServ.Quantity,
    EstimatedCrew = mainLocServ.Crew,
    TotalCrew =
    (
        select count( LSCrew.PriKey )
        from dbo.LocServ subLocServ1
        inner join dbo.LSCrew on LSCrew.LSPriKey = subLocServ1.PriKey
        where subLocServ1.PriKey = mainLocServ.PriKey
    ),
    TotalEquip =
    (
        select count( Serveq.PriKey )
        from LocServ as subLocServ2
        inner join dbo.Serveq on Serveq.LSPriKey = subLocServ2.PriKey
        where subLocServ2.PriKey = mainLocServ.PriKey
    ),
    OriginDestination = mainLocServ.OriginDestination,

    --Branch Information
    LocServBranchName = Branch.Name,
    LocServBranchID = Branch.BranchID,
    LocServBranchAddress = BranchAddress.Address,
    LocServBranchCity = BranchAddress.City,
    LocServBranchState = BranchAddress.State,
    LocServBranchZip = BranchAddress.Zip,
    LocServBranchPhone = BranchAddress.Phone,
    LocServWorkTicketTitle = BranchAddress.Title,

    --Dates and Names
    CreatedOn = mainLocServ.CreatedOn,
    CreatedByFirstName = CreatedBy.FirstName,
    CreatedByLastName = CreatedBy.LastName,
    LastEditedOn = mainLocServ.LastEditedOn,
    LastEditedByFirstName = LastEditedBy.FirstName,
    LastEditedByLastName = LastEditedBy.LastName,
    SubmittedOn = mainLocServ.SubmittedOn,
    SubmittedByFirstName = SubmittedBy.FirstName,
    SubmittedByLastName = SubmittedBy.LastName,
    ClearedOn = mainLocServ.ClearedOn,
    ClearedByFirstName = ClearedBy.FirstName,
    ClearedByLastName = ClearedBy.LastName
  
from LocServ as mainLocServ
left outer join Branch on Branch.BranchPriKey = mainLocServ.BranchPriKey
left outer join BranchAddress on
(
    BranchAddress.BranchFID = Branch.BranchPriKey and
    BranchAddress.BranchAddressTypeFID = ( select BranchAddressTypeID from dbo.BranchAddressType where BranchAddressType = 'Default' )
)
left outer join ServType on ServType.ServiceID = mainLocServ.ServiceID
left outer join LSStatus on LSStatus.PriKey = mainLocServ.ServStatus
left outer join SysUser as CreatedBy on CreatedBy.SysUserID = mainLocServ.CreatedBy
left outer join SysUser as LastEditedBy on LastEditedBy.SysUserID = mainLocServ.LastEditedBy
left outer join SysUser as SubmittedBy on SubmittedBy.SysUserID = mainLocServ.SubmittedBy
left outer join SysUser as ClearedBy on ClearedBy.SysUserID = mainLocServ.ClearedBy
GO
GRANT SELECT ON  [dbo].[LocalServices] TO [MssExec]
GRANT INSERT ON  [dbo].[LocalServices] TO [MssExec]
GRANT DELETE ON  [dbo].[LocalServices] TO [MssExec]
GRANT UPDATE ON  [dbo].[LocalServices] TO [MssExec]
GO
Uses