SQL to grab all customers even if they don't have an order
Below is a SQL statement that grabs orders for customers. I would like to modify it, so that it will include customers that don't have an order, however I am unsure as to the best way to do this:
SELECT
CT.ACCOUNTNUM,CT.STOREGROUPID,CT.STOREID, ST.STORENAME,
CT.CITY,PT.ORIGSALESID,PT.DELIVERYDATE,PT.ITEMID,IT.BRANDID,
IT.SIZEID,IT.SPECIALTYTYPEID,SUM(PT.QTY) AS Qty,
dbo.ConvertUnitQty(PT.itemid,PT.salesunit,'DZ',PT开发者_StackOverflow.dataareaid,sum(pt.qty)) as ConsumeQtyDz
FROM
CUSTPACKINGSLIPTRANS PT
JOIN SALESTABLE ST ON ST.SALESID = PT.ORIGSALESID
AND ST.DATAAREAID = PT.DATAAREAID
JOIN CUSTTABLE CT ON ST.CUSTACCOUNT = CT.ACCOUNTNUM
AND ST.DATAAREAID = CT.DATAAREAID
JOIN INVENTTABLE IT ON IT.ITEMID = PT.ITEMID
AND IT.DATAAREAID = PT.DATAAREAID
WHERE
(ST.DATAAREAID = @COMPANY) AND
(PT.DELIVERYDATE BETWEEN @STARTDATE AND @ENDDATE) AND
IT.BRANDID IN (@BRANDID) AND
IT.SIZEID IN (@SIZEID) AND
IT.SPECIALTYTYPEID = (@SPECIALTYTYPE) AND
CT.ASISTOREGROUPID IN (@STOREGROUPID)
GROUP BY
CT.ACCOUNTNUM,CT.STOREGROUPID,CT.STOREID,ST.STORENAME,
CT.CITY,PT.ORIGSALESID,PT.DELIVERYDATE,PT.ITEMID,IT.BRANDID,
IT.SIZEID,IT.SPECIALTYTYPEID,PT.SALESUNIT,PT.DATAAREAID
May be like this
SELECT
CT.ACCOUNTNUM, CT.STOREGROUPID, CT.STOREID, ST.STORENAME, CT.CITY, PT.ORIGSALESID, PT.DELIVERYDATE, PT.ITEMID, IT.BRANDID, IT.SIZEID, IT.SPECIALTYTYPEID, SUM(PT.QTY) AS Qty, dbo.ConvertUnitQty(PT.itemid,PT.salesunit,'DZ',PT.dataareaid,sum(pt.qty))as ConsumeQtyDz
FROM
CUSTPACKINGSLIPTRANS PT
LEFT JOIN
SALESTABLE ST ON ST.SALESID = PT.ORIGSALESID AND ST.DATAAREAID = PT.DATAAREAID
LEFT JOIN
CUSTTABLE CT ON ST.CUSTACCOUNT = CT.ACCOUNTNUM AND ST.DATAAREAID = CT.DATAAREAID
LEFT JOIN
INVENTTABLE IT ON IT.ITEMID = PT.ITEMID AND IT.DATAAREAID = PT.DATAAREAID
WHERE
(ST.DATAAREAID = @COMPANY OR ST.DATAAREAID = IS NULL)
AND (PT.DELIVERYDATE BETWEEN @STARTDATE AND @ENDDATE)
AND (IT.BRANDID IN (@BRANDID) OR IT.BRANDID IS NULL)
AND (IT.SIZEID IN (@SIZEID) OR IT.SIZEID IS NULL)
AND (IT.SPECIALTYTYPEID = (@SPECIALTYTYPE) OR IT.SPECIALTYTYPEID IS NULL)
AND (CT.ASISTOREGROUPID IN (@STOREGROUPID) OR CT.ASISTOREGROUPID IS NULL)
GROUP BY
CT.ACCOUNTNUM, CT.STOREGROUPID, CT.STOREID, ST.STORENAME, CT.CITY, PT.ORIGSALESID, PT.DELIVERYDATE, PT.ITEMID, IT.BRANDID, IT.SIZEID, IT.SPECIALTYTYPEID, PT.SALESUNIT, PT.DATAAREAID
I'm not sure if I'm missing something, but...
SELECT * FROM [customerTable]
The minimum SQL to show every thing about every customer is:
select * from custtable;
精彩评论