December 7, 2016 at 9:40 am
Hi I have 2 tables. A jobs Table and a Drops Table. In the Jobs table we have a collection post code and delivery post code, in the drops table we have the post codes of any additional drops.
I am creating a view which will have a “destination” column and in this column we have all the post codes from the Jobs table and the Drops table separated by a comma.
The issue I have is that if the drops table has more than one extra drop instead of putting all the post codes onto one line it is creating a line with the collection post code, delivery post code from the jobs table and the 1st post code out of the drops table on one line then creating a new line and showing the collection post code, delivery postcode and 2nd post code from the drops table.
I need a way of looping through the drops table to get each post code and put it on the same line as the collection post code and delivery post code from the jobs table.
The foreign key in the drops table is the jobid field and this will appear for for each drop and in the drops table we have a dropid field and drop number field.
I assume I would have to count the number of jobid’s in the drop table but I have no idea how to loop through the drops table to retrieve the post codes for each drop. Can anyone help please?
This is my current code. The section I am querying I have put in bold. Thanks in advance.
CREATE view [dbo].[CarouselDailyMileages] AS
SELECT DATENAME(DW,dbo.Jobs.DELDATEANDTIME) as WEEKDAY,
dbo.Jobs.DELDATEANDTIME AS "DATE",
(dbo.jobs.colpostcode + ' , ' + dbo.Jobs.DELLPOSTCODE + ', '
+ dbo.Drops.POSTCODE) AS "Destination",
DBO.Jobs.ACTUALMILEAGE AS "Mileage",
dbo.Jobs.PRICE AS "Sale Price",
CASE WHEN
(select count (*) from jobs j2 inner join driver
on driver.DRIVERPRIMARYID = j2.DRIVERPRIMARYID where j2.DRIVERCOST = 0 AND J2.JOBID = JOBS.JobID
AND driver.SUBCONTRACTORINDICATOR = 0) > 0
THEN
jobs.PRICE - (jobs.PRICE * .30)
ELSE
JOBS.DRIVERCOST
END
-- DBO.JOBS.DRIVERCOST
AS
"COST PRICE",
dbo.Vehicle.VEHICLE AS "Vehicle Type",
dbo.Jobs.JOBREFERENCE AS "Reference",
dbo.Jobs.requestedby,
dbo.customer.customerid,
dbo.driver.employeenumber,DBO.JOBS.JOBNUMBER,
case
when dbo.Jobs.requestedby like '%John Deere%' then 1 -- Green
when dbo.Jobs.requestedby like '%FSL%' then 2 -- Blue
when dbo.Jobs.requestedby = 'Manroland' then 3 -- Orange
-- when dbo.Jobs.requestedby = 'John Deere Harvest 2016' then 4 --Pink
when dbo.Jobs.requestedby = 'Clothing' then 5 -- Grey
when dbo.Jobs.requestedby = 'Community Playthings' then 6 -- Red
when dbo.Jobs.requestedby = 'Siemens' then 7 -- Black
when dbo.Jobs.requestedby = 'Carousel Siemens' then 7 --Black
when dbo.Jobs.requestedby = 'Siemens - adhoc' then 7 -- Black
when dbo.Jobs.requestedby = 'OTRS' then 8 --purple
when dbo.Jobs.requestedby = 'AGCO' then 9 -- Navy
when dbo.Jobs.requestedby like '%Draeger%' then 10 --Dark Red
else 20
end as referenceflag
FROM
dbo.Jobs INNER JOIN
dbo.Drops ON dbo.Jobs.JobID = dbo.Drops.JOBID INNER JOIN
dbo.Vehicle ON dbo.Jobs.VEHICLEID = dbo.Vehicle.VEHICLEID inner join
dbo.customer on dbo.jobs.CUSTOMERID = dbo.customer.customerid inner join
dbo.Driver on dbo.jobs.DRIVERPRIMARYID = dbo.driver.DRIVERPRIMARYID
December 7, 2016 at 9:57 am
Firstly, please put your code in IFCODE Tags, putting it straight into the box makes it quite difficult to read, and has no formatting.
Secondly, you've provided what you've attempted which is great, but I am having difficulty really visualising your data. Can you problem some samples, with DDL?
To save some others some time:
CREATE VIEW [dbo].[CarouselDailyMileages] AS
SELECT DATENAME(DW,J.DELDATEANDTIME) AS [WEEKDAY],
J.DELDATEANDTIME AS [DATE],
(J.colpostcode + ' , ' + J.DELLPOSTCODE + ', ' + D.POSTCODE) AS [Destination],
J.ACTUALMILEAGE AS Mileage,
J.PRICE AS [Sale Price],
CASE WHEN (SELECT COUNT (*)
FROM dbo.jobs j2
INNER JOIN driver dv2 ON dv2.DRIVERPRIMARYID = j2.DRIVERPRIMARYID
WHERE j2.DRIVERCOST = 0
AND j2.JOBID = J.JobID
AND dv2.SUBCONTRACTORINDICATOR = 0) > 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
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
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2016 at 10:02 am
Hi Thom,
Thanks, I have edited the post.
I am not sure how to post samples with DDL?
December 7, 2016 at 10:02 am
IF my understanding is correct, STUFF and FOR XML PATH should do what you want. I haven't tested this mind, as I don't have any sample data or DDL.
CREATE VIEW [dbo].[CarouselDailyMileages] AS
SELECT DATENAME(DW,J.DELDATEANDTIME) AS [WEEKDAY],
J.DELDATEANDTIME AS [DATE],
(J.colpostcode + ' , ' + J.DELLPOSTCODE + ', ' +
STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
WHERE d.JOBID = J.JOBID
FOR XML PATH('')),1,1,'')
) AS [Destination],
J.ACTUALMILEAGE AS Mileage,
J.PRICE AS [Sale Price],
CASE WHEN (SELECT COUNT (*)
FROM dbo.jobs j2
INNER JOIN driver dv2 ON dv2.DRIVERPRIMARYID = j2.DRIVERPRIMARYID
WHERE j2.DRIVERCOST = 0
AND j2.JOBID = J.JobID
AND dv2.SUBCONTRACTORINDICATOR = 0) > 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
--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
EDIT: In response, my signature has a useful link for you 🙂
EDIT2: Removed the top 10 I put in there for some reason.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2016 at 10:05 am
So ... a single row in the Jobs table can have multiple related rows in the Drops table, is that correct?
If so, you could make use of the FOR XML Path technique, which can create single comma-separated lists from multiple rows. Have a read here for an explanation.
If you want a working example, you'll need to provide DDL, INSERT statements and desired results, as described in the link in my signature.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 7, 2016 at 10:08 am
IF my understanding is correct, STUFF should do what you want.
You had the same idea as me, though STUFF is not doing much here other than removing an unwanted character; the meat of the solution is FOR XML PATH.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 7, 2016 at 10:12 am
Phil Parkin (12/7/2016)
IF my understanding is correct, STUFF should do what you want.
You had the same idea as me, though STUFF is not doing much here other than removing an unwanted character; the meat of the solution is FOR XML PATH.
True, I've been working with FOR XML PATH all day today for an ETL project,that I kinda of actually forgot I was using it there. :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2016 at 10:12 am
This is a sample of what the the drops table looks like
DROPIDJOBIDDROPNOTYPE POSTCODE
567 4443421 Delivery CV345AN
568 4443422 Delivery CF40 2NS
569 4443423 CollectionWS8 7HP
577 444601 Delivery RM1 1ZZ
578 444611 Delivery CF43 3DW
579 444621 Delivery IP1 1ZZ
580 444631 Delivery EH1 1ER
581 444641 Delivery RM8 9zz
December 7, 2016 at 10:25 am
Thank you Thom,
That appears to be nearly there the only problem now is if a job doesn't have any additional drops it doesnt show the collection and delivery post codes from the jobs table at all.
Thank you
December 7, 2016 at 10:48 am
paul 69259 (12/7/2016)
Thank you Thom,That appears to be nearly there the only problem now is if a job doesn't have any additional drops it doesnt show the collection and delivery post codes from the jobs table at all.
Thank you
You could try changing this
STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
WHERE d.JOBID = J.JOBID
FOR XML PATH('')),1,1,'')
To this
ISNULL(STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
WHERE d.JOBID = J.JOBID
FOR XML PATH('')),1,1,''),'')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 8, 2016 at 2:10 am
Thank you very much for your help Thom and Phil.
It seems to be working now for the most part but there seem to be random jobs appearing in the view with no post codes at all even though the data is in the database
December 8, 2016 at 2:19 am
YOur previous example had a join to DROPS, which has now been omitted. you could amend this by adding the following WHERE clause:
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 8, 2016 at 3:05 am
Thank you, that seems to have solved the issue
I assume I have put it in the correct place...
ISNULL(STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
FOR XML PATH('')),1,1,''),'')
) AS [Destination],
The query works fine in SQL but when I call it from my repoort SSRS it seems to get stuck
December 8, 2016 at 3:13 am
What do you mean by "Gets stuck"?
The query should run just as fast in SSRS as it does in SSMS. The only reason SSRS may be taking longer is due to rendering (maybe you have a lot of expressions onscreen, high volume of graphs to draw, etc).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply