Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Combining rows to make one master record

Posted on 2008-06-26
43
Medium Priority
?
267 Views
Last Modified: 2012-05-05
I need to be able to merge multiple records into one master record.

I am joining multiple tables on a field "orderno". Because some tables have more than 1 record per "orderno", I get multiple records returned. The problem is that 1 record will contain some of the data I need and another record will contain the rest of the data I need. I need to combine the records and show all of the data within one main record.

I need to be able to have a value for the "coverweight" and a value for the "textweight" held within one record

I have attached a sample of my code and also a sample of the records returned for a specific "orderno". Basically, I need to combine the rows so that all records that are the same stay the same, but any records that are different are added in
select orderheader.orderno, 
companyname,
jobheader.jobdesc,
section.title, 
orderheader.coverpagination,
orderheader.textpagination,
section.depth, 
section.width, 
sourcingbrands.branddesc,
section.papertype, 
orderheader.completedate,
CASE WHEN iscover = 1 THEN papergsm else '0' END AS [cover weight],
CASE WHEN iscover = 0 THEN papergsm else '0' END AS [text weight]
from section
inner join orderheader on section.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join binding on jobheader.orderno = binding.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
where section.recordtype = 'ppe' and binding.recordtype = 'ppe' and orderheader.completedate > '2008-01-01' and binding.description not like '%guillotine%' and binding.description not like '%folder%' and binding.description not like '%sitma%'and binding.description not like '%pre stitch%' and jobheader.jobno = '7005'
order by orderheader.completedate

Open in new window

Results.xls
0
Comment
Question by:benissitt
  • 19
  • 12
  • 11
43 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21882912
If the need to combine / aggregate is based simply on combining those 2 records, it's not too hard.  If it would also involve concatenating strings or other stuff, then it could be more difficult.

In pseudo code:

SELECT
All your fields except for [cover weight] and [text weight],
Sum([Cover Weight]) as [Cover Weight], -- this simply combines your papergsm and 0 entries
Sum([text weight]) as [Text Weight]
FROM
(
Your existing query block
) As MyQuery
GROUP BY
All your fields except for [cover weight] and [text weight]
0
 

Author Comment

by:benissitt
ID: 21923652
Cover weight and text weight aren't fields in the table. I have to use a case statement to define them.

These are the error messages I get

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'coverweight'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'textweight'.

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21924895
>>Cover weight and text weight aren't fields in the table. I have to use a case statement to define them.

I understand that, but the use of the derived table would usually succeed anyway.  Perhaps I was unclear in my code:
SELECT
orderno, companyname, jobdesc, title, coverpagination, textpagination, depth,
depth, width, branddesc, papertype, completedate,
Sum([Cover Weight]) as [Cover Weight], -- this simply combines your papergsm and 0 entries
Sum([text weight]) as [Text Weight]
FROM
(
select orderheader.orderno, 
companyname,
jobheader.jobdesc,
section.title, 
orderheader.coverpagination,
orderheader.textpagination,
section.depth, 
section.width, 
sourcingbrands.branddesc,
section.papertype, 
orderheader.completedate,
CASE WHEN iscover = 1 THEN papergsm else '0' END AS [cover weight],
CASE WHEN iscover = 0 THEN papergsm else '0' END AS [text weight]
from section
inner join orderheader on section.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join binding on jobheader.orderno = binding.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
where section.recordtype = 'ppe' and binding.recordtype = 'ppe' and orderheader.completedate > '2008-01-01' and binding.description not like '%guillotine%' and binding.description not like '%folder%' and binding.description not like '%sitma%'and binding.description not like '%pre stitch%' and jobheader.jobno = '7005'
order by orderheader.completedate
) As MyQuery
GROUP BY
orderno, companyname, jobdesc, title, coverpagination, textpagination, depth,
depth, width, branddesc, papertype, completedate

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:benissitt
ID: 21924979
Hi

I have just tried your suggestion and it is giving me the same result as before. The cover weight and text weight are held in seperate records
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21925064
Hmmm, in that case, please post a few of the query results -- even 4-6 rows.

Thanks.
0
 

Author Comment

by:benissitt
ID: 21926567
Results in attached Spreadsheet "Results"
7005.xls
0
 

Author Comment

by:benissitt
ID: 21926579
Sorry, spreadsheet named "7005"
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21927388
So ... you would like to see rows 2&3 combined and rows 4& 5 combine?

BranDesc is different between the rows that are failing to combine.

Which Brandesc do you want?  We could pick either the Min or the Max.
0
 

Author Comment

by:benissitt
ID: 21931578
Ah, I see what you mean.

I would need both branddesc to be in the record. So for example, I would have Cover Branddesc and Text Branddesc. It would also need to keep the associated papertype record, so there would be Cover Papertype and Text Papertype.

There is a field called "iscover" (from the section table) that defines whether or not a record is referring to a cover section.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21951268
With the spreadsheet that you have provided - is it possible to now show how you would image it to be presented ?

There are a couple of columns that keep the text and cover weight seperate - like title and papertype (if we were to group rows 2 and 4 together).

While you say you want to consolidate by Order No, it does appear that you are really dealing at the details / line item level because whilst in the example given, all 4 rows share the same orderno, there are actually 4 distinct lines... or, it appears that there are at least two pairs, but different enough to cause the problems...

So, think it would be most beneficial if you could "show" us the results you are looking for...
0
 

Author Comment

by:benissitt
ID: 21954339
Ideally I would only have one record per orderno but I have found that impossible due the joins. There is probably a very simple way to do it I'm just not aware of it.


7005.xls
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21954499
Giving us 2 columns for the BranDesc should make it possible ...

SELECT
orderno, companyname, jobdesc, title, coverpagination, textpagination, depth,
depth, width,  completedate,
Sum([Cover Weight]) as [Cover Weight], -- this simply combines your papergsm and 0 entries
Sum([text weight]) as [Text Weight],
Max([Cover Brandesc]) as [Cover Brandesc],
Max([Text Brandesc]) as ([Text Brandesc],
Max([Cover PaperType]) as [Cover PaperType],
Max([Text PaperType]) as ([Text PaperType]
 
FROM
(
select orderheader.orderno, 
companyname,
jobheader.jobdesc,
section.title, 
orderheader.coverpagination,
orderheader.textpagination,
section.depth, 
section.width, 
CASE WHEN iscover = 1 THEN sourcingbrands.branddesc else '' END as [Cover Brandesc],
CASE WHEN iscover = 0 THEN sourcingbrands.branddesc else '' END as [Text Brandesc],
 
CASE WHEN iscover = 1 THEN section.papertype else '' END as [Cover PaperType],
CASE WHEN iscover = 0 THEN section.papertype else '' END as [Text PaperType],
 
orderheader.completedate,
CASE WHEN iscover = 1 THEN papergsm else '0' END AS [cover weight],
CASE WHEN iscover = 0 THEN papergsm else '0' END AS [text weight]
from section
inner join orderheader on section.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join binding on jobheader.orderno = binding.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
where section.recordtype = 'ppe' and binding.recordtype = 'ppe' and orderheader.completedate > '2008-01-01' and binding.description not like '%guillotine%' and binding.description not like '%folder%' and binding.description not like '%sitma%'and binding.description not like '%pre stitch%' and jobheader.jobno = '7005'
order by orderheader.completedate
) As MyQuery
GROUP BY
orderno, companyname, jobdesc, title, coverpagination, textpagination, depth,
depth, width,   completedate

Open in new window

0
 

Author Comment

by:benissitt
ID: 21954582
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
Server: Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'order'.

I think I can see why these are appearing, but when I "correct" the code, I receive the following:

Server: Msg 1033, Level 15, State 1, Line 41
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21954704
Would you move the Order By to the end?
0
 

Author Comment

by:benissitt
ID: 21955214
Done that using the following code. Results attached. As you can see, still an issue with the cover weight and the text weight
SELECT
orderno, companyname, jobdesc, title, coverpagination, textpagination, depth,
depth, width,  completedate,
Sum([Cover Weight]) as [Cover Weight], -- this simply combines your papergsm and 0 entries
Sum([text weight]) as [Text Weight],
Max([Cover Brandesc]) as [Cover Brandesc],
Max([Text Brandesc]) as [Text Brandesc],
Max([Cover PaperType]) as [Cover PaperType],
Max([Text PaperType]) as [Text PaperType]
 
FROM
(
select orderheader.orderno, 
companyname,
jobheader.jobdesc,
section.title, 
orderheader.coverpagination,
orderheader.textpagination,
section.depth, 
section.width, 
CASE WHEN iscover = 1 THEN sourcingbrands.branddesc else '' END as [Cover Brandesc],
CASE WHEN iscover = 0 THEN sourcingbrands.branddesc else '' END as [Text Brandesc],
 
CASE WHEN iscover = 1 THEN section.papertype else '' END as [Cover PaperType],
CASE WHEN iscover = 0 THEN section.papertype else '' END as [Text PaperType],
 
orderheader.completedate,
CASE WHEN iscover = 1 THEN papergsm else '0' END AS [cover weight],
CASE WHEN iscover = 0 THEN papergsm else '0' END AS [text weight]
from section
inner join orderheader on section.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join binding on jobheader.orderno = binding.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
where section.recordtype = 'ppe' and binding.recordtype = 'ppe' and orderheader.completedate > '2008-01-01' and binding.description not like '%guillotine%' and binding.description not like '%folder%' and binding.description not like '%sitma%'and binding.description not like '%pre stitch%' and jobheader.jobno = '7005'
) As MyQuery
GROUP BY
orderno, companyname, jobdesc, title, coverpagination, textpagination, depth,
depth, width,completedate
order by completedate
 

Open in new window

7005.xls
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21955292
Any time a rows have different values in the same field, those rows will not combine -- if those fields are in the
Group By clause.

In the latest example, the Title field has different values in those rows.

Do you need Cover Title and Text Title?  Or should those titles really be different and keep the records apart?
0
 

Author Comment

by:benissitt
ID: 21955453
The title field can be taken out completely. It's not really needed as it is relevant to each individual record, but I only want the one record.

I have taken it out and it's now only returning one record per job, which is brilliant.

However, it seems to be using the same value for both the cover brandesc and text brandesc and the values in the cover weight and text weight are being multiplied. I think it is mutiplying them by however many records there were originally. For example, if a job had 2 records for the text and the text weight was 90, it is now showing as 180.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21955482
Can you post some source & result examples of the duplication / multiplication?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21956177
Now I understand... Is it always a case where you can have (and only have) Cover and/or Text ? and really the additional columns are branddesc, papertype and wieght for each of Cover and Text - and is it only the populated iscover that tells us which is which ?

I take it that all these aggregated columns are really only "MAX" values and not sums...

SELECT
orderno, companyname, jobdesc, coverpagination, textpagination, depth,
depth, width,  completedate,
Max([Cover Weight]) as [Cover Weight],
Max([text weight]) as [Text Weight],
Max([Cover Brandesc]) as [Cover Brandesc],
Max([Text Brandesc]) as [Text Brandesc],
Max([Cover PaperType]) as [Cover PaperType],
Max([Text PaperType]) as [Text PaperType]
 
FROM
(
select orderheader.orderno, 
companyname,
jobheader.jobdesc,
section.title, 
orderheader.coverpagination,
orderheader.textpagination,
section.depth, 
section.width, 
orderheader.completedate,
 
CASE WHEN iscover = 1 THEN sourcingbrands.branddesc else '' END as [Cover Brandesc],
CASE WHEN iscover = 0 THEN sourcingbrands.branddesc else '' END as [Text Brandesc],
 
CASE WHEN iscover = 1 THEN section.papertype else '' END as [Cover PaperType],
CASE WHEN iscover = 0 THEN section.papertype else '' END as [Text PaperType],
 
CASE WHEN iscover = 1 THEN papergsm else '0' END AS [cover weight],
CASE WHEN iscover = 0 THEN papergsm else '0' END AS [text weight]
 
from section
inner join orderheader on section.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join binding on jobheader.orderno = binding.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
 
where section.recordtype = 'ppe' and binding.recordtype = 'ppe' and orderheader.completedate > '2008-01-01' and binding.description not like '%guillotine%' and binding.description not like '%folder%' and binding.description not like '%sitma%'and binding.description not like '%pre stitch%' and jobheader.jobno = '7005'
) As MyQuery
 
GROUP BY
orderno, companyname, jobdesc, coverpagination, textpagination, depth, width,completedate
order by completedate

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21956315
>>Is it always a case where you can have (and only have) Cover and/or Text ?
Yes, you have to either pick one or do some kind of combination.  SUM works nicely for numeric data, but for text MAX or MIN is probably best.

>>I take it that all these aggregated columns are really only "MAX" values and not sums
Sum of something and 0 evaluates to the same as the max of that something and 0 -- given that the something is positive.  So either way you want to look at it.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21956486
Hi Daniel,

I believe the joins are giving duplicated information in the rowset returned, so, SUM is going to double up weight. If I knew more about those joins, I might even suggest a couple of inline queries rather than selecting from a sub-query where the row numbers are obviously expaned upon due to the joined relationships... But it will work fine using MAX to effectively select a "discrete" value.

I think that is really what the author has been struggling with from the get go...

Cheers, Mark.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21956538
Perhaps so ... with this running over the long weekend, I haven't kept up as well as I would like.
0
 

Author Comment

by:benissitt
ID: 21961524
Hi,
It is just about perfect except the same value is being used for cover brandesc and text brandesc. It appears to be applying the cover brandesc to both fields.

I appreciate the help both of you have provided on this, it's been a thorn in my side for a long time

Ben



7005.xls
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21962867
Can you also show us the source data for this order?  Particularly from the sourcingbrands table?

Thanks.
0
 

Author Comment

by:benissitt
ID: 21963564
Using the following code, I get the attached results:
select
orderheader.orderno, companyname, jobdesc, coverpagination, textpagination,
section.depth, section.width,  completedate, branddesc, SECTION.ISCOVER, SECTION.TITLE
from section
inner join orderheader on section.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join binding on jobheader.orderno = binding.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
where orderheader.orderno = '7005' and section.recordtype = 'ppe' and binding.recordtype = 'ppe'

Open in new window

7005-Results.xls
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21963678
  Look down the IsCover and Brandesc columns.

You will see both brandesc entries tied to both IsCover values.

So ... you've narrowed it down ...

The problem appears to reside in:
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid

Is there some other criterion you can add to the join?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21963756
Well now, that is interesting... the iscover does not differentiate...

the linkage to SECTION is obviously out of sorts... we will need more info to "see" the linkages. Might need to see more detail about section

could you please run the following and give us another spreadsheet ?  Also, would like to see



select
orderheader.orderno, companyname, jobheader.jobno, jobdesc, binding.jobno as bindjobno,sourcingrequest.jobno as sourcingjobno, coverpagination, textpagination,section.depth, section.width,  completedate, sourceingrequestxref.sourcingid,sourceingrequestxref.paperid, branddesc, SECTION.ISCOVER, SECTION.TITLE
from orderheader
inner join section on section.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join binding on jobheader.orderno = binding.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
where orderheader.orderno = '7005' and section.recordtype = 'ppe' and binding.recordtype = 'ppe'
 
-- and as a second query, would also like to see:
 
select * from section where section.orderno = 7005 and section.recordtype = 'ppe' 

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21963783
what is in sourcingrequestxref ?  and why would that be a left outer join if it is ultimately used to source the pointer to paperid and ultimately to brandid ?
0
 

Author Comment

by:benissitt
ID: 21964042
I have included the results you requested and also results for sourcingrequestxref table. I was using a left outer join as an inner join seemed to be excluding some records.

Sourcingpapermaster could be linked to sourcingbrands on

sourcingpapermaster.brand = sourcingbrands.brandid
Result.xls
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21965793
Seems to be a big problem somewhere - we are getting way too many rows for the combined orderheader / section join (ie expecting only two rows)

OK, so let us assume the following in the query we have at the moment (ie order 7005)

1) 1 orderheader
2) 2 sections
3) 1 company
4) 1 job header number for that order header ?? test: select * from jobheader where jobno = 7005
5) 1 binding  ??   test: select * from binding where orderno = 7005
6) 1 sourcingrequest ?? test: select * from sourcingrequest where jobno = 7005
7) we seem to be getting 2 sourcingrequestxref ie sourcingid 18635 and 18636 - meaning there must be something extra to link on, or above has returned more records...
8) sourcingpapermaster will return at least two records - one for 18635 and one for 18636  test: select * from sourcingpapermaster where paperid in (select paperid from sourcingreqestxref where sourcingid in (18635,18636))
9) sourcingbrands will return at least two records - one for 18635 and one for 18636  (via sourcingpapermaster link to sourcingrequestxref) and we can see that - there are two descriptions

OK, so we get two distinct section descriptions and two distinct brand descriptions, but we have 6 rows for section 1 and 6 rows for section 2. meaning the linkage problem is most likely returing more rows than expected in steps 4,5 or 6 above - which probably also results in the two sourcingid's as well.

Would you mind running those tests ?
0
 

Author Comment

by:benissitt
ID: 21971285
Don't mind at all, enjoy!
7005.xls
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21973731
Well, binding is a definite worry - looks to have a single row, then the next three are repeated, but, we are only looking at PPE, combined with the two sourcingrequests, and the two sections - thats we have the twelve resulting rows...

So, one thing that does look obvious is that we do not actually use anything from binding - so kill that link straight up - it does nothing for the "header". Also, can see that section has a link to sourcingrequest via papertype which closes out the other "duplication" - ie each section does have a natural extension. Also we see that presstype has a link in both sourcingrequest and sourcingpapeertype, so may as well use that, though think the more critical is linking each section to each sourcingrequest.

Probably the only other thing to contrmplate is if width and depth could differ by section, and for now, looks the same, but "safer" to get the max values of those as well...

Well, given those scenarios, think we are now ready for :

SELECT
orderno, companyname, jobdesc, coverpagination, textpagination, completedate,
max(depth) as depth, 
max(width) as width, 
Max([Cover Weight]) as [Cover Weight],
Max([text weight]) as [Text Weight],
Max([Cover Brandesc]) as [Cover Brandesc],
Max([Text Brandesc]) as [Text Brandesc],
Max([Cover PaperType]) as [Cover PaperType],
Max([Text PaperType]) as [Text PaperType]
 
FROM
(
select orderheader.orderno, 
companyname,
jobheader.jobdesc,
--section.title, 
orderheader.coverpagination,
orderheader.textpagination,
orderheader.completedate,
section.depth, 
section.width, 
 
CASE WHEN iscover = 1 THEN sourcingbrands.branddesc else '' END as [Cover Brandesc],
CASE WHEN iscover = 0 THEN sourcingbrands.branddesc else '' END as [Text Brandesc],
 
CASE WHEN iscover = 1 THEN section.papertype else '' END as [Cover PaperType],
CASE WHEN iscover = 0 THEN section.papertype else '' END as [Text PaperType],
 
CASE WHEN iscover = 1 THEN papergsm else '0' END AS [cover weight],
CASE WHEN iscover = 0 THEN papergsm else '0' END AS [text weight]
 
from orderheader
inner join section on orderheader.orderno = section.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno and section.papertype = sourcingrequest.papertype
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid and sourcingrequest.presstype = sourcingpapermaster.presstype
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
 
where section.recordtype = 'ppe' and orderheader.completedate > '2008-01-01' and jobheader.jobno = '7005'
) As MyQuery
 
GROUP BY orderno, companyname, jobdesc, coverpagination, textpagination, completedate
order by orderno, completedate

Open in new window

0
 

Author Comment

by:benissitt
ID: 21973787
Hi,

I do use the binding table. Within binding there is a field "description" which I have a lengthy case statement written for. I didn't include it in the code because it is long and wasn't really part of the problem. However, in hindsight I could have left it in to avoid confusion.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21973955
Oh bugga...

lets try the query first, then lets attack the "description"...

can you show the case statement ? we might use an in-line query to get the "most relevant" description...
0
 

Author Comment

by:benissitt
ID: 21974021
The description field contains text. The problem is that one description could be entered in any number of different ways so I needed to standardise it in some way.

Here goes....
Case	when binding.description like '%Silk%' 
	then 'Silk UV'
		else
(case	when binding.description like '%Matt Lam + Spot UV%'
	or binding.description like '%Matt Laminate + Spot UV%'
	or binding.description like '%Matt lam + Spot UV%'
	or binding.description like '%Matt Laminate & Spot UV%'
	or binding.description like '%Matt Lam + Spot UV%'
	then 'Matt Laminate + Spot UV'
		else
(Case	when binding.description like '%Gloss Laminate%' 
	or binding.description like '%Gloss Lam%'
	or binding.description like '%Gloss Lamination%'
	or binding.description like '%Gloss Laminating%'
	then 'Gloss Laminate'
		else
(Case 	when binding.description like '%Matt Laminate%' 
	or binding.description like '%Matt Lam cover%'
	or binding.description like '%Matt Lam all%'
	or binding.description like '%Matt Lamination%'
	or binding.description like '%Matt Laminating%'
	or binding.description like '%matt lam  cover%'
	then 'Matt Laminate'
		else
(Case 	when binding.description = 'Matt Machine Seal and Spot UV'
	or binding.description = 'Spot U V 30% Varnish Cover+Matt Seal'
	or binding.description = 'Matt Seal + Spot U V Varnish'
	then 'Matt Machine Seal + Spot UV'
		else
(Case	when binding.description like '%Spot UV%'
	or binding.description like '%Spot%'
	THEN 'Spot UV'
		else
(Case 	when binding.description like '%Matt UV%'
	then 'Matt UV'
		else
(Case 	when binding.description like '%Matt Duct%'
	then 'Matt Duct'
		else
(Case	when binding.description like '%+UV%'
	or binding.description = '+ UV Cover'
	or binding.description = '+UV Cover'
	or binding.description like '% +UV%'
	or binding.description like '%+ U V%'
	or binding.description like '%5 secs + cover + UV Varnish%'
	or binding.description like '%3 sections + cover + UV%'
	or binding.description like '%5 sections + cover + UV%'
	or binding.description = '4 sections + UV Varnish cover'
	or binding.description like '%3 sections + UV cover%'
	or binding.description like '%2 sections + cover + UV%'
	or binding.description like '%3 sections +  UV Cover%'
	or binding.description like '%2 sections + cover UV Varnish%'
	or binding.description like '%5 sections + UV Varnish cover%'
	or binding.description like '%4 sections +  UV Cover%'
	or binding.description = '4pp Cover+ UV'
	or binding.description = '4 P Uv cover'
	or binding.description like '%4p Uv Cover%'
	or binding.description like '%uV COVER%'
	or binding.description like '%5 sections + UVCover%'
	or binding.description like '%4 P UV%'
	or binding.description like '%4pp UV%'
	or binding.description like '%cover uv%'
	or binding.description like '%3 sections + UV Cover%'
	then 'Gloss UV'
else 'No Cover Finish'
	end)end)end)end)end)end)end)end)
end as 'coverfinishing',

Open in new window

0
 

Author Comment

by:benissitt
ID: 21974104
By the way, that code without the binding.description works perfectly
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21975707
ummm, ouch. can probably be cleaned up a bit. let me look into the binding table a bit more...
0
 

Author Comment

by:benissitt
ID: 22005382
Any luck?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22007877
some luck... but can find multiple descriptions - how do you determine which one to use ?
0
 

Author Comment

by:benissitt
ID: 22007926
It will always be the first record in the binding table
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 22009060
would you mind trying the attached ?
SELECT
orderno, companyname, jobdesc, coverpagination, textpagination, completedate,
max(depth) as depth, 
max(width) as width, 
Max([BindingDesc]) as [BindingDesc],
Max([Cover Weight]) as [Cover Weight],
Max([text weight]) as [Text Weight],
Max([Cover Brandesc]) as [Cover Brandesc],
Max([Text Brandesc]) as [Text Brandesc],
Max([Cover PaperType]) as [Cover PaperType],
Max([Text PaperType]) as [Text PaperType]
 
FROM
(
select orderheader.orderno, 
companyname,
jobheader.jobdesc,
--section.title, 
orderheader.coverpagination,
orderheader.textpagination,
orderheader.completedate,
section.depth, 
section.width, 
 
(select top 1 
 Case	when binding.description like '%Silk%'				then 'Silk UV'					else
(case	when binding.description like '%Matt%Lam%Spot%UV%'	then 'Matt Laminate + Spot UV'	else
(Case	when binding.description like '%Gloss%Lam%'			then 'Gloss Laminate'			else
(Case 	when binding.description like '%Matt%Lam%'			then 'Matt Laminate'			else
(Case 	when binding.description like '%Matt%Seal%'			then 'Matt Machine Seal + Spot UV' else
(Case	when binding.description like '%Spot%'				then 'Spot UV'					else
(Case 	when binding.description like '%Matt%UV%'			then 'Matt UV'					else
(Case 	when binding.description like '%Matt%Duct%'			then 'Matt Duct'				else
(Case	when binding.description like '%+%UV%'				
		  or binding.description like '%+ U V%'
		  or binding.description = '%4%P%UV%'
		  or binding.description like '%Cover%'				then 'Gloss UV'					else 'No Cover Finish'
	end)end)end)end)end)end)end)end)
end 
from binding
where jobheader.orderno = binding.orderno and binding.recordtype = 'ppe' and binding.description not like '%guillotine%' and binding.description not like '%folder%' and binding.description not like '%sitma%'and binding.description not like '%pre stitch%'
) as [BindingDesc],
 
 
CASE WHEN iscover = 1 THEN sourcingbrands.branddesc else '' END as [Cover Brandesc],
CASE WHEN iscover = 0 THEN sourcingbrands.branddesc else '' END as [Text Brandesc],
 
CASE WHEN iscover = 1 THEN section.papertype else '' END as [Cover PaperType],
CASE WHEN iscover = 0 THEN section.papertype else '' END as [Text PaperType],
 
CASE WHEN iscover = 1 THEN papergsm else '0' END AS [cover weight],
CASE WHEN iscover = 0 THEN papergsm else '0' END AS [text weight]
 
from orderheader
inner join section on orderheader.orderno = section.orderno
inner join customers.dbo.company on orderheader.customer =  customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno and section.papertype = sourcingrequest.papertype
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid and sourcingrequest.presstype = sourcingpapermaster.presstype
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
 
where section.recordtype = 'ppe' and orderheader.completedate > '2008-01-01' and jobheader.jobno = '7005'
) As MyQuery
 
GROUP BY orderno, companyname, jobdesc, coverpagination, textpagination, completedate
order by orderno, completedate
 

Open in new window

0
 

Author Closing Comment

by:benissitt
ID: 31470898
Genius
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question