Help! A query question - 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

Help! A query question

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-17 : 17:26:30

Hi all. I am a newbie with SQL. Here is my question:

The table is like
col1 col2 col3
a1 1 c
a1 2 a
...
a1 100 t
a2 1 d
a2 2 c
...
a2 100 g
a3 1 h
...
a3 100 d

now I am trying to make a function. The parameters are 2 ints of col2(such as 2,81). I want to return a table containing the composition of that two input parameters which is like

for parameter (2,81)
dt 10%
ac 40%
tt 50%

could anyone help me out?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 17:33:30

Yes, if you can provide expected output that matches the sample data.
And explain your business rules.

E 12°55'05.63"
N 56°04'39.26"

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-17 : 17:37:57

Thanks for your response.
the expected output is like

With parameter (2,81):

dt 10%
ac 40%
tt 50%

quote:
Originally posted by Peso

Yes, if you can provide expected output that matches the sample data.
And explain your business rules.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 17:46:01

Sigh...

I can see that records for Col2 with value 2 is "a" and "c" in col3.
How do you relate the value of 81 to your sample data?

E 12°55'05.63"
N 56°04'39.26"

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-17 : 17:55:20

Sorry for the confusion.
the value in "col3" does not matter too much.

this query is like suppose I have a sequence a1 with "agdgteddgg", then I put it into the table as below:

col1 col2 col3
a1 1 a
a1 2 g
a1 3 d
...
a1 10 g

then I get 9 other different sequences (a2-a10) and put them into the table in the same format.

Then I want to check for all sequences, what is the combination of composition at position 2 and position 8.

so it could be
--------
gd 10% //(g at pos 2 and d at pos 8)
gc 20%
...
--------

Did I make it clear?

Thanks.

quote:
Originally posted by Peso

Sigh...

I can see that records for Col2 with value 2 is "a" and "c" in col3.
How do you relate the value of 81 to your sample data?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 17:59:48

A little more.
Just an advice - If you refer to position 8, make sure there is a position 8 in the provided sample data...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:00:52

And how does the percentage value become the wanted value?
How is it calculated?

Please remember we can't read minds.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:02:39

Here is how you get the letter combination right.

DECLARE @param1 INT, @param2 INT

SELECT MAX(CASE WHEN col2 = @param1 THEN col3 ELSE '' END)
+ MAX(CASE WHEN col2 = @param2 THEN col3 ELSE '' END)
FROM Table1
GROUP BY col1

E 12°55'05.63"
N 56°04'39.26"

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-17 : 18:06:33

For example:

Within 10 sequences, if 2 of them have "g" at pos 2 and "d" at pos 8,
then the percentage for "gd" is 20%.

quote:
Originally posted by Peso

And how does the percentage value become the wanted value?
How is it calculated?

Please remember we can't read minds.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:09:38

DECLARE @param1 INT, @param2 INT

select @param1 = 2, @param2 = 8

select grp, 100.0E * count(*) / (SELECT COUNT(DISTINCT Col1) FROM Table1)
from (
SELECT MAX(CASE WHEN col2 = @param1 THEN col3 ELSE '' END)
+ MAX(CASE WHEN col2 = @param2 THEN col3 ELSE '' END) as grp
FROM Table1
GROUP BY col1
) AS d
GROUP BY grp
order by grp

E 12°55'05.63"
N 56°04'39.26"

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-17 : 18:12:26

Thank you so much.

I am a newbie, so do not understand ur query very well.
Could u plz explain a little bit detail for
"SELECT MAX(CASE WHEN col2 = @param1 THEN col3 ELSE '' END)+ MAX(CASE WHEN col2 = @param2 THEN col3 ELSE '' END)"

just wandering why u use "MAX"?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:25:21

I use that to create a mini-pivot, because you want to add the two characters.

E 12°55'05.63"
N 56°04'39.26"

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-17 : 19:56:09

I got it.

Thank you so much.

quote:
Originally posted by Peso

I use that to create a mini-pivot, because you want to add the two characters.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources