Complex Query. Please help

Question

Complex Query. Please help

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

eli.misael (1/8/2014)
other solution is using the CTE and PIVOT like the follow script, in the sample I define the tables, but you can use your defined tables and only use the CTE statement, I wish resolve the problem :

That seems really complex compared to the cross tabs approach. At least for me.:-P

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sqlnaive SSCoach Points: 17437 More actions · Answer 2

Everything was working fine but got the requirement change.Idea is:

a. base table can have multiple values of ID based on different books but can have only two values for object (juice and/or nojuice). b. nobase table can have multiple values for an ID based on different books with different values of Values for their Type.

output should be calculated as:

c. In final output, we need one entry per ID with Object "juice" as priority. That is, if there is only juice value for an ID then it will be taken, if nojuice only then it will be taken, if for an ID, there are both, then Juice will be taken and nojuice entries should be removed from point d calculation..

d. once object type "juice" or "nojuice" for decided for an ID, also we need to consider, which book for that ID has max value for "value" column. Only that book has to be considered PLUS the final "value" should contain net of all "Value" (in case the ID has both juice and nojuice, then only consider books with juice for netting the value)

Base table data

IDBookObject

1234abc1juice

1234abc2juice

1234abc3juice

1234abc4juice

1111abc1juice

1111abc2nojuice

1111abc3juice

1111abc4nojuice

2222kkk1nojuice

2222kkk2nojuice

2222kkk3nojuice

NoBase table data

IDBookTypeValue

1234abc1Simple500

1234abc2Simple600

1234abc3Simple1000

1234abc4Simple200

1111abc1Simple300

1111abc2Simple400

1111abc3Simple200

1111abc4Simple500

2222kkk1Medium200

2222kkk2Medium300

2222kkk3Medium400

Output Needed

IDBookObjectSimpleTypeSimpleValue

1234abc3JuiceSimple2300

1111abc1JuiceSimple500

2222kkk3nojuiceMedium900

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

To get this query, i would use nested CTEs (or subqueries) first using a RANK() function to determine which products I will include, then ROW_NUMBER() on the result to get the book with highest the highest value and finally get the aggregates over this.

If you want a coded answer from me, you need to provide at least consumable sample data.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

sqlnaive (1/10/2014)
Everything was working fine but got the requirement change.Idea is:
a. base table can have multiple values of ID based on different books but can have only two values for object (juice and/or nojuice). b. nobase table can have multiple values for an ID based on different books with different values of Values for their Type.
output should be calculated as:
c. In final output, we need one entry per ID with Object "juice" as priority. That is, if there is only juice value for an ID then it will be taken, if nojuice only then it will be taken, if for an ID, there are both, then Juice will be taken and nojuice entries should be removed from point d calculation..
d. once object type "juice" or "nojuice" for decided for an ID, also we need to consider, which book for that ID has max value for "value" column. Only that book has to be considered PLUS the final "value" should contain net of all "Value" (in case the ID has both juice and nojuice, then only consider books with juice for netting the value)
Base table data
IDBookObject
1234abc1juice
1234abc2juice
1234abc3juice
1234abc4juice
1111abc1juice
1111abc2nojuice
1111abc3juice
1111abc4nojuice
2222kkk1nojuice
2222kkk2nojuice
2222kkk3nojuice
NoBase table data
IDBookTypeValue
1234abc1Simple500
1234abc2Simple600
1234abc3Simple1000
1234abc4Simple200
1111abc1Simple300
1111abc2Simple400
1111abc3Simple200
1111abc4Simple500
2222kkk1Medium200
2222kkk2Medium300
2222kkk3Medium400
Output Needed
IDBookObjectSimpleTypeSimpleValue
1234abc3JuiceSimple2300
1111abc1JuiceSimple500
2222kkk3nojuiceMedium900

I strongly recommend you do yourself a favor and start posting data in a readily consumable format as previously requested on this and many other posts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)