Query formation - Urgent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query formation - Urgent

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-10 : 01:00:16

Please help me with the query to get the following result. I am so much worried with this and if some one can give me a query to form this, I will be so thank full
I have 3 tables

1. Quote
2. QuoteProdList
3. QuoteVersion

In Quote Table, I am storing the QuoteNumber,QuoteReferenceNumber and CustomerName
In QuoteprodList I am storing QuoteNumber and FasValue
In QuoteVersion, I am storing different versions of these QuoteRefNo

My problem is I have to get the sum of Fas value group by Customer name, but this should be for the latest version of quote.ie, In QuoteVersion Table, if some value is there in version2 field, it should take the fas value from QuoteProdList, given with the quotenumber specified in Version2 field of QuoteVersion Table.

Quote
-----

QuoteNumber.......CustomerName......QuoteRefNo
----------------------------------------------
1.................a.................xyz
2.................b.................abc
3.................a.................xyz
4.................c.................efg
5.................d.................hij
6.................a.................klm
7.................d.................hij

QuoteVersion
------------

QuoteRefNo..............Version1........Version2
------------------------------------------------
xyz.....................1..................3
abc.....................2..................Null
efg.....................4..................Null
hij.....................5..................7
klm.....................6..................Null

QuoteProdList
-------------
QuoteNumber...........Fasvalue
------------------------------
1.......................20
2.......................32
3.......................40
4.......................12
5.......................6
6.......................10
7.......................30

For me, the out put should be

CustomerName......FasValue
--------------------------
a..................50
b..................32
c..................12
d..................30

Customer ' a is having 2 quoterefno in Quote Table,xyz & klm. From QuoteVersion Table ,take QuoteNumber 3 for xyz specified by Version2 field, take it's fas value from QuoteprodList(40)+QuoteNUmber 6 specified by Version2 field, take is fas value(10)=50)
Customer d is having two quotenumbers in version1 and 2, so take the quote number in version 2 and take fas of 2 from quoteprodlist,ie, 30

Please help me to solve this.

Regards
Ceema

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 01:11:41

Try this suggestion


SELECT		d.CustomerName,
		SUM(qpl.FasValue) AS FasValue
FROM		(
			SELECT DISTINCT	q.CustomerName,
					q.QuoteRefNo
					ISNULL(qv.Version2, qv.Version1) AS Version
			FROM		Quote AS q
			INNER JOIN	QuoteVersion AS qv ON qv.QuoteRefNo = q.QuoteRefNo
		) AS d
INNER JOIN	QuoteProdList AS qpl ON qpl.QuoteNumber = d.Version
GROUP BY	d.CustomerName
ORDER BY	d.CustomerName

Peter Larsson
Helsingborg, Sweden

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-10 : 02:39:26

Hello Peso,

Thank you for the reply, but I have one more problem, in my Quoteversion table I have fields like Quoterefno,version1,version2,version3,version4 (I haven't never imagine, you will use it in this manner, that's why I haven't menioned about that in the query,sorry) and instead of null I am storing 0 in the field. So, could you please tell me how to change the query accordingly.

Thank you so much
Ceema

quote:

Originally posted by Peso

Try this suggestion


SELECT		d.CustomerName,
		SUM(qpl.FasValue) AS FasValue
FROM		(
			SELECT DISTINCT	q.CustomerName,
					q.QuoteRefNo
					ISNULL(qv.Version2, qv.Version1) AS Version
			FROM		Quote AS q
			INNER JOIN	QuoteVersion AS qv ON qv.QuoteRefNo = q.QuoteRefNo
		) AS d
INNER JOIN	QuoteProdList AS qpl ON qpl.QuoteNumber = d.Version
GROUP BY	d.CustomerName
ORDER BY	d.CustomerName

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 03:25:16

[code]SELECT d.CustomerName,
SUM(qpl.FasValue) AS FasValue
FROM (
SELECT DISTINCT q.CustomerName,
q.QuoteRefNo
COALESCE(NULLIF(qv.Version4, 0), NULLIF(qv.Version3, 0), NULLIF(qv.Version2, 0), qv.Version1) AS Version
FROM Quote AS q
INNER JOIN QuoteVersion AS qv ON qv.QuoteRefNo = q.QuoteRefNo
) AS d
INNER JOIN QuoteProdList AS qpl ON qpl.QuoteNumber = d.Version
GROUP BY d.CustomerName
ORDER BY d.CustomerName[/code]

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-10 : 03:28:11

Your QuoteVersion table doesn't seem to be normalized. Handling this kinds of query will become more complicated if you have large no. of versions, say 10.
Why don't you keep simple table with just QuoteRefNo and Version columns. It will be much easier to manage and query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-10 : 03:32:07

Hello Peso,

Thank you, it done the trick. Thank you so much.

Regards
Ceema

quote:

Originally posted by Peso

SELECT		d.CustomerName,
		SUM(qpl.FasValue) AS FasValue
FROM		(
			SELECT DISTINCT	q.CustomerName,
					q.QuoteRefNo
					COALESCE(NULLIF(qv.Version4, 0), NULLIF(qv.Version3, 0), NULLIF(qv.Version2, 0), qv.Version1) AS Version
			FROM		Quote AS q
			INNER JOIN	QuoteVersion AS qv ON qv.QuoteRefNo = q.QuoteRefNo
		) AS d
INNER JOIN	QuoteProdList AS qpl ON qpl.QuoteNumber = d.Version
GROUP BY	d.CustomerName
ORDER BY	d.CustomerName

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 03:32:42

I agree with Harsh. Try to normalize your table.
But that might not help, if the application using this table is third party.

How many version do you have at maximum? If the version is maximum 10, use this

COALESCE(NULLIF(qv.Version10, 0), NULLIF(qv.Version9, 0), NULLIF(qv.Version8, 0), NULLIF(qv.Version7, 0), NULLIF(qv.Version6, 0), NULLIF(qv.Version5, 0), NULLIF(qv.Version4, 0), NULLIF(qv.Version3, 0), NULLIF(qv.Version2, 0), qv.Version1) AS Version

instead of

COALESCE(NULLIF(qv.Version4, 0), NULLIF(qv.Version3, 0), NULLIF(qv.Version2, 0), qv.Version1) AS Version

Peter Larsson
Helsingborg, Sweden

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-10 : 03:33:31

Hello harsh_athalye,
Some times, we are not controlling the database design, so i have to run with what I am getting, that's why I am struggling now.

Thank you
Ceema

quote:
Originally posted by harsh_athalye

Your QuoteVersion table doesn't seem to be normalized. Handling this kinds of query will become more complicated if you have large no. of versions, say 10.
Why don't you keep simple table with just QuoteRefNo and Version columns. It will be much easier to manage and query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 03:36:47

quote:
Originally posted by harsh_athalye

Handling this kinds of query will become more complicated if you have large no. of versions, say 10.

No that much more complicated, if you use this

COALESCE(NULLIF(qv.Version4, 0), NULLIF(qv.Version3, 0), NULLIF(qv.Version2, 0), qv.Version1)

compared to

ISNULL(qv.Version21, qv.Version1)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-10 : 04:01:30

Okay, that's not much complicated although bit lengthy. But still you need to change the query every time you add new version.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 04:11:06

Yes, the drawback of not normalized tables.

I think a new version columns is added for every new major version of the third party application.
Pain in the butt, but managable, if the upgrade process description is accurate to the DBA responsible for altering the query to accomodate the new column.

I think I will have to remember this one

COALESCE(NULLIF(qv.Version4, 0), NULLIF(qv.Version3, 0), NULLIF(qv.Version2, 0), qv.Version1)

I have a feeling this can come in handy in the future.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources