Multi Level Lookup - 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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Multi Level Lookup

Author

Topic

IgnDsouza
Starting Member

6 Posts

Posted - 2012-04-24 : 11:50:01

Hi,

I hope someone could help me. I've been working on this a long time & have not got the desired result.

I am trying to get a compatibility done with Add-On's active on a particular account.

I have one query which gives me the Add-On's active on a particular account along with the Price Plan for that account.

Account# Price Plan Add-On
999999 Talk 900 SMS 150
999999 Talk 900 MMS 250
999999 Talk 900 Int 150
999999 Talk 900 Internet
999999 Talk 900 Skype

The Account# & the Price Plan remains the same for an account whereas the Add-On differs & hence the multiple entries for the same account.

I have another table which I have created on a different database which gives me the compatibility matrix of the Price Plans with the Add-On's

Price Plan Add-On
Talk 900 SMS 150
Talk 900 MMS 250
Talk 900 Int 150
Talk 900 Internet
Video 100 MMS 250
Video 100 Int 150
Video 100 Internet
Video 100 Skype
Video Talk 300 SMS 150
Video Talk 300 MMS 250
Video Talk 300 Int 150
Video Talk 300 Internet
Video Talk 300 Skype

I know how to join two databases on SQL Server; however, I am unable to get the compatibility done on the 1st query.

The result I require is to run the 1st Query, Look-up with the compatibility matrix & return a column TRUE or FALSE.

I Hope someone can help me. Thanks in advance

Ignatius D'souza
Revenue Assurance & Billing - Ireland
M: +91-99677 14397
E: Ignatius.Dsouza2@3globalservices.com
MSN: dsouza.ignatius@hotmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:50:22

so what should be output for the above dataset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

IgnDsouza
Starting Member

6 Posts

Posted - 2012-04-25 : 04:32:42

The output for the query should give the results of the 1st query along with another column stating whether TRUE or FALSE as compared to the compatibility matrix.

The output should look something like below
Account# Price Plan Add-On Condition
999999 Talk 900 SMS 150 TRUE
999999 Talk 900 MMS 250 TRUE
999999 Talk 900 Int 150 FALSE
999999 Talk 900 Internet FALSE
999999 Talk 900 Skype TRUE

Ignatius D'souza
Revenue Assurance & Billing - Ireland
M: +91-99677 14397
E: Ignatius.Dsouza2@3globalservices.com
MSN: dsouza.ignatius@hotmail.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 04:39:10

why is these 2 FALSE
- 999999 Talk 900 Int 150 FALSE
- 999999 Talk 900 Internet FALSE

and this TRUE ?
- 999999 Talk 900 Skype TRUE

KH
[spoiler]Time is always against us[/spoiler]

IgnDsouza
Starting Member

6 Posts

Posted - 2012-04-25 : 07:12:34

It's just an example that I have used.

The 1st query looks up the compatibility table & it a Particular add-on does not match the Price Plan on the matrix, it should return the value FALSE else TRUE

Ignatius D'souza
Revenue Assurance & Billing - Ireland
M: +91-99677 14397
E: Ignatius.Dsouza2@3globalservices.com
MSN: dsouza.ignatius@hotmail.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 07:15:46

So does the required result that you posted reflect the business logic that you required ? Or is it just a random example ?

KH
[spoiler]Time is always against us[/spoiler]

IgnDsouza
Starting Member

6 Posts

Posted - 2012-04-25 : 08:10:16

Yes this is the required Business logic for the query.
But the data given is only for illustration purpose

Ignatius D'souza
Revenue Assurance & Billing - Ireland
M: +91-99677 14397
E: Ignatius.Dsouza2@3globalservices.com
MSN: dsouza.ignatius@hotmail.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 08:11:52

then can you post the expected result that correspond to the sample data that you have posted ?

KH
[spoiler]Time is always against us[/spoiler]

IgnDsouza
Starting Member

6 Posts

Posted - 2012-04-25 : 09:05:10

The expected output is as below.

Account # Price Plan Add-On Condition
12345 Talk 900 SMS 150 TRUE
12345 Talk 900 SMS 250 TRUE
12345 Talk 900 Internet FALSE
23125 Video 100 SMS 150 TRUE
23125 Video 100 SMS 250 TRUE
23125 Video 100 Internet FALSE
31254 VideoTalk300 Internet TRUE

The places where the condition shows FALSE is when the combination of Price Plan or Add-on is not present in the compatibility matrix.

Ignatius D'souza
Revenue Assurance & Billing - Ireland
M: +91-99677 14397
E: Ignatius.Dsouza2@3globalservices.com
MSN: dsouza.ignatius@hotmail.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 10:15:55

[code]
SELECT . . .
Condition = case when exists ( select * from matrix x
where x.PricePlan = a.PricePlan
and x.AddOn = a.AddOn )
then 'TRUE'
else 'FALSE'
end
FROM account a
[/code]

KH
[spoiler]Time is always against us[/spoiler]

IgnDsouza
Starting Member

6 Posts

Posted - 2012-04-25 : 11:08:34

Hey,

Thanks for you help. The query now works & gives me the desired output.

Thanks a ton

Ignatius D'souza
Revenue Assurance & Billing - Ireland
M: +91-99677 14397
E: Ignatius.Dsouza2@3globalservices.com
MSN: dsouza.ignatius@hotmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources