June 24, 2014 at 10:00 pm
Hi all,
I need your suggestion on below program development
I have a scenario where one partner can have multiple competency like Grade A,Grade B or Grade C and Grade AB,Grade BC etc.
I need to pull the records of competency of higher level also there is a chance of changing this competency level in feature like Grade A of Grade AA like this.
What would be the best option to select this kind of records.
June 24, 2014 at 11:58 pm
Can you give the table DDL if possible, some sample input and the desired output?
In the first link of my signature you find an article that helps you with those things.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 25, 2014 at 12:38 am
i hope i understand it right, i think you are looking for how you want to design you tables to handle such one to many relation with Grades and partner, if so then please share your problem in more detail so that we can help you. please do review Koen suggestion.
June 25, 2014 at 3:54 am
Hard to completely understand what you're going for, but it actually sounds like a many to many relationship, not one to many. In this case, I'd have TableA which is the owner of these Grades. Then I'd have a Grade table that defines the grades. Then I'd have an OwnerGrades table that matches between the primary key of TableA and the primary key of the Grades table. Make those two columns the primary key of the third table. That way any given owner can only own one grade, but grades can be distributed to lots of owners and if you have to change the definition of a grade, it changes for everyone at the same time.
That's assuming I understand what you're asking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2014 at 9:17 pm
Let me elaborate want i want to do.
I have a table
create table mastercompetency
(program varchar(10),
SKUgroup varchar(10),
competencyname varchar(10))
insert into mastercompetency values('DIRECT','SKG1','GOLD 1')
insert into mastercompetency values('DIRECT','SKG1','GOLD 2')
insert into mastercompetency values('EA','SKG2','GOLD 1')
insert into mastercompetency values('EA','SKG1','SILVER 1')
insert into mastercompetency values('DIRECT','SKG1','GOLD 1')
I need to process the records based on above competency which should follow the below criteria.
It should consider (GOLD1 and GOLD 2) OR SILVER 1 kind of condition for the input data in order to pass and that competencyname which be any of either AND/OR condition.
What will be my table design for this.
June 26, 2014 at 1:26 am
Gangadhara MS (6/25/2014)
Let me elaborate want i want to do.It should consider (GOLD1 and GOLD 2) OR SILVER 1 kind of condition for the input data in order to pass and that competencyname which be any of either AND/OR condition.
Still not clear
June 26, 2014 at 1:35 am
Can you elaborate further?
if you had the 3 tables grade,owner,ownergrades
it would be something like
select owner
from owners
where (
exists (select * from ownergrades inner join grades on ownergrades.grade=grades.grade and ownergrades.owner=owners.owner and grades.grade='GOLD 1')
and exists (select * from ownergrades inner join grades on ownergrades.grade=grades.grade and ownergrades.owner=owners.owner and grades.grade='GOLD 2')
)
or exists (select * from ownergrades inner join grades on ownergrades.grade=grades.grade and ownergrades.owner=owners.owner and grades.grade='SILVER 1')
June 26, 2014 at 3:30 am
I'm back where I was. I'd break it down into three tables, one to define the owner, one to define the competency and one to relate them to each other.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 26, 2014 at 3:37 am
Grant Fritchey (6/26/2014)
I'm back where I was. I'd break it down into three tables, one to define the owner, one to define the competency and one to relate them to each other.
+1, The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood. 😀
June 26, 2014 at 6:50 am
Expected result is still not real clear.
It sounds like are looking for the Max for each competency.
I tend to agree with Grant's scenario, although you might also consider a slowly changing dimension or current record flag.
Post DDL for 3 tables, sample data, and expected results.
This should help generate better answers.
June 26, 2014 at 9:58 am
Apology,pls consider this and suggest me.
I need some design help here.
This is my source input data which i need to select based on condition which is given by business.
create table partners(
parter_id varchar(10),
start_date datetime,
end_date datetime,
competency_name varcgar(50))
insert into partners ( 'P1','1/14/2013','2/5/2014','Gold Cloud Productivity')
insert into partners ( 'P1','5/10/2013','2/6/2014','Gold Small and MidMarket Cloud Solutions ')
insert into partners ( 'P1','7/14/2013','9/19/2014','Silver Small and MidMarket Cloud Solutions')
insert into partners ( 'P2','9/14/2013','10/15/2014','Gold Cloud Productivity')
and i have config table (not sure how it will be but initial my thought of design ) i will have this information from business which are the competency should exist to select particular partner and they specify the condition to select meaning( if partner should be more than one or 2 competency to be eligible for payment)
create table mastercompetency
(program varchar(10),
SKUgroup varchar(10),
competencyname varchar(50))
insert into mastercompetency values('DIRECT','SKG1','Gold Cloud Productivity ')
insert into mastercompetency values('DIRECT','SKG1','Silver Cloud Productivity ')
insert into mastercompetency values('EA','SKG2','Gold Small and MidMarket Cloud Solutions ')
insert into mastercompetency values('EA','SKG1','Silver Small and MidMarket Cloud Solutions ')
Now i need to select the Data from source which should match criteria from business to select eligible partner, how to design this.
If input data matches (Gold Cloud Productivity and Gold Small and MidMarket Cloud Solutions) OR
(Silver Small and MidMarket Cloud Solutions ) that partner need be selected from input source data for payment
The condition i said is one example, it may have any combinations of competencies to be consider as eligible partner.
Hope its clear now.
Thanks for all your help till now.
June 26, 2014 at 12:07 pm
So P1 overlaps in Small / MidMarket Cloud Solutions?
Gold and Silver - is this related to Partner? Or to the Service being provided?
insert into partners ( 'P1','5/10/2013','2/6/2014','Gold Small and MidMarket Cloud Solutions ')
insert into partners ( 'P1','7/14/2013','9/19/2014','Silver Small and MidMarket Cloud Solutions')
Direct and EA - are these related to SKU Group or the Service being provided?
insert into mastercompetency values('DIRECT','SKG1','Gold Cloud Productivity ')
insert into mastercompetency values('DIRECT','SKG1','Silver Cloud Productivity ')
insert into mastercompetency values('EA','SKG2','Gold Small and MidMarket Cloud Solutions ')
insert into mastercompetency values('EA','SKG1','Silver Small and MidMarket Cloud Solutions ')
I would spend effort on normalizing the tables first, then the query.
This could be IN (Gold Cloud Productivity,Gold Small and MidMarket Cloud Solutions) OR (Silver Small and MidMarket Cloud Solutions ),
or (MidMarket Cloud Solutions) Level IN (Gold,Silver) AND (Cloud Productivity AND Level = (Gold))
depending on how you normalize the tables.
If input data matches (Gold Cloud Productivity and Gold Small and MidMarket Cloud Solutions) OR
(Silver Small and MidMarket Cloud Solutions ) that partner need be selected from input source data for payment
To me, you have a Partners table, then one that may have many Partner / Services Provided, and a Services table.
I'd probably go as far as Level (Gold, Silver) having a table too.
Keys - are important. Things like Gold and Silver are more of a Description, and are subject to change.
You need to be able to allow for flexibility in the future.
June 26, 2014 at 12:20 pm
Gold and Silver - is this related to Partner? Or to the Service being provided?
Its related to partner which they own it after meeting some sales criteria.
Direct and EA - are these related to SKU Group or the Service being provided?
Its for a different program of type Direct and EA and related to SKU group.
Do i need to add any further here ? not sure about your suggestion Greg ?
Thanks for your help.
June 26, 2014 at 1:17 pm
Gangadhara MS (6/26/2014)
Gold and Silver - is this related to Partner? Or to the Service being provided?Its related to partner which they own it after meeting some sales criteria.
So it sounds like it should be modeled as an attribute of the Partner, not directly into the Services they provide.
i.e. - the Services table does not have this, only the different Services that can be related to any given Partner.
Direct and EA - are these related to SKU Group or the Service being provided?
Its for a different program of type Direct and EA and related to SKU group.
Ask yourself if this should be an attribute of the Partner.
Sounds kind of like where we used to have Direct Customers we sold to as individuals, and stores that we stocked.
Do i need to add any further here ? not sure about your suggestion Greg ?
Thanks for your help.
So I hope you are seeing some of this, like how the Partner provides Services, and the Gold and Silver are related to the Partner.
It sounds like any Services a Partner offers are Gold or Silver based on the Partner.
So if their volume increases, or decreases, you need to only change the attribute of the Partner, not all the Services.
Likewise, a table with the Services should only need to list the Services available, not have a Gold and Silver level Service listed.
Make sense?
June 26, 2014 at 1:41 pm
yes i will list all the competency in one table as mentionedbefore but how do we select the partner for multiple conditions met.
I mean i need to select a partner who is having multiple competency level (Gold Cloud Productivity AND Gold Small and MidMarket Cloud Solutions OR Silver Cloud Productivity)
but i can't list this possibality of selection criteria for all the values.
If u give some more info will be helpful Greg..thanks for for your time
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply