OpenOrders.sql

USE [tagnet] 

go 

SELECT DISTINCT headers.[partnerprimaryorderid]              AS 'YRC PRO' ,
		/*
		PLACE HOLDER FOR OVERSTOCK PARTNER
		We need more info
		*/ 
       ( CASE 
           WHEN addRef.reference IS NULL THEN '-' 
           ELSE addRef.reference 
         END )                                      AS 'ORDER NUMBER', 
       orders.servicelevel                          AS 'SERVICE LEVEL', 
       orders.orderstatus                           AS 'STATE', 
       CONVERT(VARCHAR(10), headers.dateadded, 110) AS 'REGISTRATION DATE', 
       orders.jobtype                               AS 'JOBTYPE', 
       ( CASE 
           WHEN orders.jobtype = 'DELIVERY' THEN pointsLD.NAME 
           ELSE pointsUl.NAME 
         END )                                      AS 'VENDOR NAME AND ADDRESS' 
       , 
       ( CASE 
           WHEN orders.jobtype = 'PICKUP' THEN pointsLD.NAME 
           ELSE pointsUl.NAME 
         END )                                      AS 
       'CONSIGNEE NAME AND ADDRESS' 
FROM   [dbo].[tagnet_appointmentspn_partnerorderheaders] AS headers 
       INNER JOIN [dbo].[tagnet_appointmentspn_orders] AS orders 
               ON orders.[partnerprimaryorderid] = 
                  headers.[partnerprimaryorderid] 
       INNER JOIN [dbo].[tagnet_appointmentspn_partneraddresspoints] AS pointsUl 
               ON pointsUl.[partnerprimaryorderid] = 
                  headers.[partnerprimaryorderid] 
       INNER JOIN [dbo].[tagnet_appointmentspn_partneraddresspoints] AS pointsLD 
               ON pointsLD.[partnerprimaryorderid] = 
                  headers.[partnerprimaryorderid] 
       LEFT JOIN 
(SELECT [reference], 
        [partnerprimaryorderid] 
 FROM   [dbo].[tagnet_appointmentspn_partnerorderadditionalreferences] 
 WHERE  [referencetype] = 'SI') AS addRef 
              ON addRef.[partnerprimaryorderid] = 
                 headers.[partnerprimaryorderid] 
WHERE  headers.partnerid = 2 
       AND orders.jobtype IN ( 'PICKUP', 'DELIVERY' ) 
       AND orders.orderstate IN ( 15, 3, 22, 12 ) 
       AND pointsUl.[addresstype] = 'UL' 
       AND pointsLD.[addresstype] = 'LD' 
ORDER BY 
'REGISTRATION DATE', 'YRC PRO'

go