May 24, 2017 at 10:13 am
Hi,
Can anyone help ?
I have a view, an individual sales view.
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[IndividualSales]
AS
SELECT
GRO.GROUPDESCRIPTION AS "Group",
ACC.ACCOUNTNUMBER AS "Account_No",
ACC.ACCOUNTNAME AS "Account_Name",
ORD.InvoiceNo,
ORD.DELADDRESS1 AS "DelAddress1",
ORD.DELADDRESS2 AS "DelAddress2",
ORD.DELADDRESS3 AS "DelAddress3",
ORD.DELADDRESS4 AS "DelAddress4",
ORD.DELPOSTCODE AS "Del_Postcode",
CASE
WHEN ORD.TYPE = 0 THEN 'SP'
WHEN TYPE = 1 THEN 'WO'
WHEN TYPE = 3 THEN 'NP8'
WHEN TYPE = 4 THEN 'SO'
WHEN TYPE = 6 THEN 'PI'
WHEN TYPE = 7 THEN 'GEN'
WHEN TYPE = 8 THEN 'OTC'
ELSE 'XX' END AS Type,
PRO.SPECIALCODE AS "Special_Code",
PRO.SPECIALDESCRIPTION AS "Special_Description",
CASE
WHEN ISNULL(ORD.DTMarker, 0)
= 0 THEN 'Non Drug Tariff'
ELSE 'Drug Tariff'
END AS DT,
ISNULL(ORD.SPECIALCHARGE, 0) AS Charge,
ISNULL(ORD.SHIPPINGCHARGE, 0) AS "Out_Of_Pocket_Expenses",
CONVERT(DECIMAL(12, 2),
ISNULL(ORD.SPECIALCHARGE * (ORD.VATRATE / 100), 0) + ISNULL(ORD.SHIPPINGCHARGE * (ORD.SHIPPINGVATRATE / 100), 0)) AS VAT,
CONVERT(Varchar(10), ORD.ORDERDATEANDTIME, 103) AS "Order_Date",
ORD.QTY AS "Qty_Supplied", ISNULL(ORD.SPECIALCOST, 0) + ISNULL(ORD.SHIPPINGCOST, 0) AS Cost,
SAL.SalesDescription AS "Sales_Person", ORD.REBATEAMOUNT AS "Rebate_Amount",
ORD.ORDERDATEANDTIME
FROM
dbo.Orders AS ORD INNER JOIN
dbo.Account AS ACC ON ORD.ACCOUNTID = ACC.ACCOUNTID INNER JOIN
dbo.Groups AS GRO ON ACC.GROUPID = GRO.GROUPID INNER JOIN
dbo.Products AS PRO ON PRO.PRODUCTID = ORD.PRODUCTID LEFT OUTER JOIN
dbo.Sales AS SAL ON ACC.SALESID = SAL.SALESID
WHERE
ORD.StatusID <> 9
AND ORD.StatusID <> 14
AND ORD.CreditDateAndTime IS NULL
AND NOT ACC.AccountNumber = 'LAB'
AND NOT ORD.TYPE IN (6, 7, 8)
UNION
SELECT
GRO.GroupDescription,
ACC.AccountNumber,
ACC.AccountName,
ORD.InvoiceNo, ORD.DelAddress1,
ORD.DelAddress2,
ORD.DelAddress3,
ORD.DelAddress4,
ORD.DelPostcode,
CASE
WHEN ORD.TYPE = 0 THEN 'SP'
WHEN TYPE = 1 THEN 'WO'
WHEN TYPE = 3 THEN 'NP8'
WHEN TYPE = 4 THEN 'SO'
WHEN TYPE = 6 THEN 'PI'
WHEN TYPE = 7 THEN 'GEN'
WHEN TYPE = 8 THEN 'OTC'
ELSE 'XX' END AS Type,
PRO.SpecialCode,
PRO.SpecialDescription,
CASE
WHEN ISNULL(ORD.DTMarker, 0)
= 0 THEN 'Non Drug Tariff'
ELSE 'Drug Tariff' END AS DT,
ISNULL(ORDD.QTY * ORDD.UNITCHARGE, 0) AS Charge,
ISNULL(ORD.ShippingCharge, 0) AS Out_Of_Pocket_Expenses,
CONVERT(DECIMAL(12, 2), (ISNULL((ORDD.QTY * ORDD.UNITCHARGE) * (ORD.VATRate / 100), 0) + ISNULL(ORD.SHIPPINGCHARGE * (ORD.SHIPPINGVATRate / 100), 0))) AS VAT,
CONVERT(Varchar(10), ORD.OrderDateAndTime, 103) AS Order_Date,
ORDD.Qty AS Qty_Supplied, (ISNULL((ORDD.QTY * ORDD.UNITCOST), 0) + ISNULL(ORD.ShippingCost, 0)) AS Cost,
SAL.SalesDescription,
ORD.REBATEAMOUNT,
ORD.ORDERDATEANDTIME
FROM
OrderDetail ORDD INNER JOIN
Orders ORD ON ORD.OrderID = ORDD.OrderID INNER JOIN
Account ACC ON ORD.AccountID = ACC.AccountID INNER JOIN
Groups GRO ON ACC.GroupID = GRO.GroupID INNER JOIN
Products PRO ON PRO.ProductID = ORDD.ProductID LEFT OUTER JOIN
dbo.Sales AS SAL ON ACC.SALESID = SAL.SALESID
WHERE
ORD.StatusID <> 9
AND ORD.StatusID <> 14
AND ORD.CreditDateAndTime IS NULL
AND NOT ACC.AccountNumber = 'LAB'
GO
What I need to do is include Accounts which have no orders so that the Sales guys can see customers that haven't placed any orders as well as the ones that have.
Thanks in advance.
Paul.
May 24, 2017 at 10:27 am
Your INNER JOINS on Orders Orders ORD ON ORD.OrderID = ORDD.OrderID INNER JOIN rule out rows that don't match.
Consider a LEFT OUTER join to return unmatched orders too.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 24, 2017 at 11:14 am
you'd also have to OUTER JOIN any tables linked off of Orders as well, so maybe:
--first half of union
FROM dbo.Account AS ACC
INNER JOIN dbo.Groups AS GRO ON ACC.GROUPID = GRO.GROUPID
LEFT OUTER JOIN dbo.Orders AS ORD ACC.ACCOUNTID = ON ORD.ACCOUNTID
LEFT OUTER JOIN dbo.Products AS PRO ON PRO.PRODUCTID = ORD.PRODUCTID
LEFT OUTER JOIN dbo.Sales AS SAL ON ACC.SALESID = SAL.SALESID
--second half of union
FROM Account ACC
INNER JOIN Groups GRO ON ACC.GroupID = GRO.GroupID
LEFT OUTER JOIN Orders ORD ON ACC.AccountID = ORD.AccountID
LEFT OUTER JOIN OrderDetail ORDD ON ORD.OrderID = ORDD.OrderID
LEFT OUTER JOIN Products PRO ON PRO.ProductID = ORDD.ProductID
LEFT OUTER JOIN dbo.Sales AS SAL ON ACC.SALESID = SAL.SALESID
May 25, 2017 at 3:12 am
Thank you for your help
May 26, 2017 at 3:09 pm
The first and second result sets are likely to always be different. Changing your UNION to a UNION ALL will save you some server resources. If you are trying to eliminate duplicates from the top or bottom, you may be able to save some resource by using SELECT DISTINCT instead.
SELECT... concatenates both sets and then does a distinct sort.
UNION
SELECT...
SELECT DISTINCT... performs a distinct sort on the smaller sets and then concatenates them.
UNION ALL
SELECT DISTINCT...
SELECT DISTINCT... only checks the top set for duplicates.
UNION ALL
SELECT...
Sorting/duplicate removal has a lot of overhead. Since you have static values in your top set, I assume you'll never have duplicates between the top & bottom sets. If an individual set won't return duplicates, you should eliminate the overhead of checking for them.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply