/**
* Returns information on services with service dates between the current date and a week out.
*/

CREATE view [dbo].[LocalServicesWithinNextWeek]
  (
        AgentID,
        Commodity,
        Service,
        CustomerNumber,
        ServiceDate,
        OrderNumber,
        ShipperName,
        PurchaseOrderNo,
        ProjectNumber,
        CreatedOn,
        Classification,
        BranchLocation
  )
as
select  Agent.AgentID,
        CommType.Commodity,
        ServType.Service,
        Orders.CustomerNumber,
        convert(varchar(10), LocServ.ServiceDate, 101) as ServiceDate,
        Orders.OrderNo as OrderNumber,
        dbo.FormatFirstNameLastName(Orders.FirstName, Orders.LastName) as ShipperName,
        isnull(Orders.PurchaseOrderNo, '') as PurchaseOrderNo,
        '' as ProjectNumber,
        convert(varchar(10), Orders.CreatedOn, 101) as CreatedOn,
        ServTypeClass.Description as Classification,
        BranchLocation.Description as BranchLocation
FROM    LocServ
        inner join Orders on Orders.PriKey = LocServ.OrdPriKey
        left outer join Branch on Branch.BranchPriKey = LocServ.BranchPriKey
        left outer join BranchLocationMap on BranchLocationMap.BranchFID = Branch.BranchPriKey
        left outer join BranchLocation on BranchLocation.BranchLocationID = BranchLocationMap.BranchLocationFID
        left outer join Agent on Agent.AgentPriKey = Branch.AgentPriKey
        left outer join ServType on ServType.ServiceID = LocServ.ServiceID
        left outer join LSStatus on LSStatus.PriKey = LocServ.ServStatus
        left outer join CommType on CommType.PriKey = Orders.Commodity
        left outer join ServTypeClass on ServType.ServTypeClassFID  = ServTypeClass.ServTypeClassID
WHERE   
        --grab services with service dates between the start of today and the very end of the day (23:59:59) 7 days from now.
        LocServ.ServiceDate between dateadd(dd, datediff(dd, 0, getdate()), 0) and dateadd( s, -1, dateadd(dd, datediff(dd, -8, getdate()), 0) )
        and LSStatus.Status not in ( 'Idle', 'Not Cleared', 'Pending', 'Void', 'Cancelled' )
        and Orders.OrderNo is not null
GO
GRANT SELECT ON  [dbo].[LocalServicesWithinNextWeek] TO [MssExec]
GRANT INSERT ON  [dbo].[LocalServicesWithinNextWeek] TO [MssExec]
GRANT DELETE ON  [dbo].[LocalServicesWithinNextWeek] TO [MssExec]
GRANT UPDATE ON  [dbo].[LocalServicesWithinNextWeek] TO [MssExec]
GO