January 23, 2007 at 8:08 am
This may work. It uses derived tables, only one column in the GROUP BY
and an EXISTS subquery to avoid the overhead of a DISTINCT.
SELECT ISNULL(D.PCCount, 0) AS PCCount
,P.Postcode, P.Town, P.County, P.Area, P.Country, P.Master_Account
FROM dbo.UkPostcodes P
-- Could do a full join here to show districts not in dbo.UkPostcodes
LEFT JOIN (
-- This counts the districts
SELECT D1.Postcode
,COUNT(*) AS PCCount
FROM (
-- The SELECT gets the district from the postcode
-- Assumes all postcodes have a space and the district is the bit before the space.
SELECT LEFT(C.Postcode, CHARINDEX(' ', C.Postcode) - 1) AS Postcode
FROM dbo.Customers_Trade C
WHERE C.is_activated <> 'No'
AND EXISTS (
SELECT *
FROM dbo.Orders_Trade O
WHERE DATEDIFF(d, O.order_date, GETDATE()) < 60
AND (O.TradeCustomerID = C.TradeCustomerID OR
O.TradeCustomerID = C.BillingAccountID)) D1
GROUP BY D1.Postcode ) D
ON P.Postcode = D.Postcode
January 24, 2007 at 10:26 am
Hi everyone - got this working for my needs with the following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.spTradePostcodeWorker
@Master_Account nvarchar(50)
AS SELECT ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2)
WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0)
AS PCOUNT, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
dbo.UkPostcodes.Master_Account, dbo.UkPostcodes.Status, dbo.UkPostcodes.StatusUpdate, dbo.UkPostcodes.StatusUpdatedBy, dbo.UkPostcodes.StatusUpdatedByName
FROM dbo.UkPostcodes LEFT OUTER JOIN
dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END
WHERE (dbo.UkPostcodes.Master_Account = @Master_Account) AND (dbo.UkPostcodes.Status <> 'Grey' OR dbo.UkPostcodes.Status IS NULL) AND ((dbo.Customers_Trade.is_activated = 'Yes') OR
(dbo.Customers_Trade.is_activated IS NULL))
AND
((dbo.Customers_Trade.TradeCustomerID IN
(SELECT DISTINCT TradeCustomerID
FROM dbo.Orders_Trade
WHERE (DATEDIFF(d, order_date, GETDATE()) < 60)) OR
dbo.Customers_Trade.TradeCustomerID IS NULL)
OR
(dbo.Customers_Trade.BillingAccountID IN
(SELECT DISTINCT TradeCustomerID
FROM dbo.Orders_Trade
WHERE (DATEDIFF(d, order_date, GETDATE()) < 60)) OR
dbo.Customers_Trade.BillingAccountID IS NULL))
GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
dbo.UkPostcodes.Master_Account, dbo.UkPostcodes.Status, dbo.UkPostcodes.StatusUpdate, dbo.UkPostcodes.StatusUpdatedBy, dbo.UkPostcodes.StatusUpdatedByName
ORDER BY PCOUNT ASc, dbo.UkPostcodes.Status DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
January 24, 2007 at 12:38 pm
Darth,
Back to your basic question....the reason the NULLs aren't showing with the WHERE is the way SQL Server 'reads' the script. It does NOT start with the SELECT. It starts with the FROM and then moves downward. The SELECT is the LAST thing it does. So, if your WHERE eliminates NULLs, your SELECT will never find them to change them to 0. This is one reason to include the ISNULL(columnname,0) to the WHERE clause.
-SQLBill
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply