December 8, 2016 at 7:47 am
paul 69259 (12/8/2016)
Awesome thank you Chris, that seems to have done the trick for me.
Thanks for the feedback Paul, but it's really for Phil and Thom, who did all the groundwork. Test this thoroughly, like you would anything from an unknown source. If you are absolutely sure that the query generates correct results, then if you like, post up the actual execution plan to give folks an opportunity for tuning it for you.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2016 at 8:05 am
There is a slight problem in that if J.DELLPOSTCODE is blank in the database it seems to not put any post codes at all in the destination field it doesn't show the J.colpostcode value.
December 8, 2016 at 8:11 am
paul 69259 (12/8/2016)
There is a slight problem in that if J.DELLPOSTCODE is blank in the database it seems to not put any post codes at all in the destination field it doesn't show the J.colpostcode value.
Phil introduced you to the ISNULL function earlier. The solution is the same here. I think you should really try to solve this one yourself 🙂
You can read about ISNULL (Transact-SQL) on msdn, which tells you how to use it if Phil's Post doesn't clue you in.
Alternatively, if you want to, you could use COALESCE (Transact-SQL).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 8, 2016 at 8:22 am
Great, thanks for the tip Thom. I managed to get it done.
alter VIEW [dbo].[CarouselDailyMileages] AS
SELECT
DATENAME(DW,J.DELDATEANDTIME) AS [WEEKDAY],
J.DELDATEANDTIME AS [DATE],
isnull(J.colpostcode,'EMPTY') + ' , ' + isnull(J.DELLPOSTCODE,'EMPTY') + ', ' + ISNULL(d.DropList, 'EMPTY') AS [Destination],
J.ACTUALMILEAGE AS Mileage,
J.PRICE AS [Sale Price],
CASE WHEN x.cnt > 0
THEN J.PRICE - (J.PRICE * .30)
ELSE J.DRIVERCOST END AS [COST PRICE],
V.VEHICLE AS [Vehicle Type],
J.JOBREFERENCE AS [Reference],
J.requestedby,
C.customerid,
Dv.employeenumber,
J.JOBNUMBER,
CASE WHEN J.requestedby like '%John Deere%' THEN 1 -- Green
WHEN J.requestedby like '%FSL%' THEN 2 -- Blue
WHEN J.requestedby = 'Manroland' THEN 3 -- Orange
-- WHEN J.requestedby = 'John Deere Harvest 2016' THEN 4 --Pink
WHEN J.requestedby = 'Clothing' THEN 5 -- Grey
WHEN J.requestedby = 'Community Playthings' THEN 6 -- Red
WHEN J.requestedby = 'Siemens' THEN 7 -- Black
WHEN J.requestedby = 'Carousel Siemens' THEN 7 --Black
WHEN J.requestedby = 'Siemens - adhoc' THEN 7 -- Black
WHEN J.requestedby = 'OTRS' THEN 8 --purple
WHEN J.requestedby = 'AGCO' THEN 9 -- Navy
WHEN J.requestedby like '%Draeger%' THEN 10 --Dark Red
ELSE 20
end as referenceflag
FROM dbo.Jobs J
OUTER APPLY (
SELECT DropList = (
SELECT STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
WHERE d.JOBID = J.JOBID -- correlation to outer query
FOR XML PATH('')),1,1,'')
)
) d
CROSS APPLY ( -- if JobID is unique in Jobs table, then remove Jobs table from this subquery and correlate to dbo.Jobs J
SELECT cnt = COUNT (*)
FROM dbo.jobs j2
INNER JOIN driver dv2
ON dv2.DRIVERPRIMARYID = j2.DRIVERPRIMARYID
WHERE j2.DRIVERCOST = 0
AND j2.JOBID = J.JobID -- correlation to outer query
AND dv2.SUBCONTRACTORINDICATOR = 0
) x
-- INNER JOIN dbo.Drops D ON J.JobID = D.JOBID no longer required
INNER JOIN dbo.Vehicle V ON J.VEHICLEID = V.VEHICLEID
INNER JOIN dbo.customer C on J.CUSTOMERID = C.customerid
INNER JOIN dbo.Driver Dv on J.DRIVERPRIMARYID = Dv.DRIVERPRIMARYID
December 8, 2016 at 8:23 am
Thank you to everyone for your help and time in this post.
December 9, 2016 at 2:07 am
Thom A (12/8/2016)
...On the side note, I do get a LEFT SEMI JOIN when doing to following:
USE DevTestDB;GO
CREATE TABLE Jobs (JOBID INT IDENTITY(1,1),
VehicleID INT,
CustomerID INT,
DRIVERPRIMARYID INT);
GO
CREATE TABLE Drops (DropID INT IDENTITY(1,1),
JobID INT);
GO
CREATE TABLE Vehicle (VehicleID INT IDENTITY(1,1),
Registration VARCHAR(10));
GO
CREATE TABLE Customer (CustomerID INT IDENTITY(1,1),
CustomerName VARCHAR(100));
GO
CREATE TABLE Driver (DriverPrimaryID INT IDENTITY(1,1),
DriverName VARCHAR(100));
GO
INSERT INTO Jobs (VehicleID, CustomerID, DRIVERPRIMARYID)
VALUES (1, 1, 1),
(1, 2, 1),
(2, 3, 2),
(2, 4, 2),
(3, 5, 3);
INSERT INTO Drops (JobID)
VALUES (1),
(1),
(1),
(1),
(2),
(2),
(2);
INSERT INTO Vehicle (Registration)
VALUES ('AA11 AGB'),
('YE12 AGB'),
('AB16 AGB');
INSERT INTO Customer (CustomerName)
VALUES ('Mr Smith'),
('Mrs Jones'),
('Ms Green'),
('Mr Butter'),
('Dr Cook')
INSERT INTO Driver (DriverName)
VALUES ('John Free'),
('Hillary Clinton'),
('Oliver Dover');
SELECT *
FROM dbo.Jobs J
--INNER JOIN dbo.Drops D ON J.JobID = D.JOBID
INNER JOIN dbo.Vehicle V ON J.VEHICLEID = V.VEHICLEID
INNER JOIN dbo.customer C on J.CUSTOMERID = C.customerid
INNER JOIN dbo.Driver Dv on J.DRIVERPRIMARYID = Dv.DRIVERPRIMARYID
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
DROP TABLE Jobs;
DROP TABLE Drops;
DROP TABLE Vehicle;
DROP TABLE Customer;
DROP TABLE Driver;
Query plan attached.
With a simpler test harness and a little tweaking, you can encourage a merge join, nested loops inner join and left semi join just by changing the rowcount of the two tables involved:
DROP TABLE #t1;SELECT TOP(1000) n = ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),0)%10 INTO #t1 FROM SYS.columns
CREATE CLUSTERED INDEX ucx_Stuff ON #t1 (n)
DROP TABLE #t2;SELECT TOP(1000) n = ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),0) INTO #t2 FROM SYS.columns
CREATE CLUSTERED INDEX ucx_Stuff ON #t2 (n)
SELECT o.* FROM #t2 o WHERE o.n IN (SELECT n FROM #t1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply