| Author |
 Topic  |
|
|
IgnDsouza
Starting Member
India
6 Posts |
 Posted - 04/24/2012 : 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
India
47023 Posts |
Posted - 04/24/2012 : 21:50:22
|
so what should be output for the above dataset?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
IgnDsouza
Starting Member
India
6 Posts |
Posted - 04/25/2012 : 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)
Singapore
16745 Posts |
Posted - 04/25/2012 : 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
Time is always against us
|
|
|
|
IgnDsouza
Starting Member
India
6 Posts |
Posted - 04/25/2012 : 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)
Singapore
16745 Posts |
Posted - 04/25/2012 : 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
Time is always against us
|
|
|
|
IgnDsouza
Starting Member
India
6 Posts |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 04/25/2012 : 08:11:52
|
then can you post the expected result that correspond to the sample data that you have posted ?
KH
Time is always against us
|
|
|
|
IgnDsouza
Starting Member
India
6 Posts |
Posted - 04/25/2012 : 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)
Singapore
16745 Posts |
Posted - 04/25/2012 : 10:15:55
|
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
KH
Time is always against us
|
|
|
|
IgnDsouza
Starting Member
India
6 Posts |
|
| |
Topic |
|
Multi Level Lookup
