SQL 語法( 統一發票明細表 )

SELECT Y.NumBase, Y.CloseWorkDate, Y.InvNO, Y.sumInvoiceTotal, N.FlagNO
FROM (SELECT min(SUBSTRING(Invoice.No, 3, 8) / 250) AS NumBase, Orders.CloseWorkDate, MIN(Invoice.No)
+ ' ~ ' + MAX(Invoice.No) AS InvNO,
SUM((CASE WHEN Invoice.Flag = 'Y' THEN Invoice.Total ELSE (CASE WHEN Flag = 'N' THEN - Invoice.Total
ELSE 0 END) END)) AS sumInvoiceTotal
FROM Orders INNER JOIN
Invoice ON Orders.ID = Invoice.OrderID AND Orders.ShopID = Invoice.ShopID
WHERE (Orders.ShopID = 'h003') AND (SUBSTRING(Orders.CloseWorkDate, 1, 6) = LEFT('20150801', 6)) AND
(Invoice.Flag = 'Y' OR Invoice.Flag = 'O')
 GROUP BY    Orders.CloseWorkDate, SUBSTRING(Invoice.No, 3, 8) / 250) AS Y INNER JOIN
(SELECT Orders_1.CloseWorkDate, (SUBSTRING(Invoice_1.No, 3, 8) / 250) AS NumBase,

(SELECT SUBSTRING(i.No, 3, 8) + ',' + '' AS Expr1
FROM Invoice AS i INNER JOIN
Orders AS o ON i.OrderID = o.ID AND i.ShopID = o.ShopID
WHERE (Orders_1.CloseWorkDate = o.CloseWorkDate) AND (o.ShopID = 'h003') 
AND (SUBSTRING(i.No, 3, 8)/250)=(SUBSTRING(Invoice_1.No, 3, 8) / 250) AND 
(LEFT(o.CloseWorkDate, 6) = LEFT('20150801', 6)) AND (RIGHT(i.No, 1) = '#') FOR 
XML PATH('')) AS FlagNO

FROM Orders AS Orders_1 INNER JOIN
Invoice AS Invoice_1 ON Orders_1.ID = Invoice_1.OrderID AND Orders_1.ShopID = Invoice_1.ShopID
WHERE  (Orders_1.ShopID = 'h003') AND (LEFT(Orders_1.CloseWorkDate, 6) = LEFT('20150801', 6))
GROUP BY    Orders_1.CloseWorkDate,SUBSTRING(Invoice_1.No, 3, 8) / 250) AS N ON Y.CloseWorkDate = N.CloseWorkDate
AND  Y.NumBase = N.NumBase
ORDER BY   Y.CloseWorkDate