/**
* 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
  )
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
FROM    LocServ
        inner join Orders on Orders.PriKey = LocServ.OrdPriKey
        left outer join Branch on Branch.BranchPriKey = LocServ.BranchPriKey
        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
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', 'Voided' )
        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