Solved

Combining rows to make one master record

Posted on 2008-06-26
43
259 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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
 

Author Comment

by:benissitt
Comment Utility
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
Comment Utility
Hmmm, in that case, please post a few of the query results -- even 4-6 rows.

Thanks.
0
 

Author Comment

by:benissitt
Comment Utility
Results in attached Spreadsheet "Results"
7005.xls
0
 

Author Comment

by:benissitt
Comment Utility
Sorry, spreadsheet named "7005"
0
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Would you move the Order By to the end?
0
 

Author Comment

by:benissitt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Can you post some source & result examples of the duplication / multiplication?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
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
Comment Utility
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
Comment Utility
Can you also show us the source data for this order?  Particularly from the sourcingbrands table?

Thanks.
0
 

Author Comment

by:benissitt
Comment Utility
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
Comment Utility
  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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Don't mind at all, enjoy!
7005.xls
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
By the way, that code without the binding.description works perfectly
0
 
LVL 51

Expert Comment

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

Author Comment

by:benissitt
Comment Utility
Any luck?
0
 
LVL 51

Expert Comment

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

Author Comment

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

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
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
Comment Utility
Genius
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now