Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

suggestions on one to may relationship in the data processing Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 9:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 132, Visits: 821
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.
Post #1586546
Posted Thursday, June 26, 2014 12:07 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 674, Visits: 6,807
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.
Post #1586602
Posted Thursday, June 26, 2014 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 132, Visits: 821
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.
Post #1586607
Posted Thursday, June 26, 2014 1:17 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 674, Visits: 6,807
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?
Post #1586630
Posted Thursday, June 26, 2014 1:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 132, Visits: 821
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
Post #1586642
Posted Friday, June 27, 2014 9:30 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 674, Visits: 6,807
Could also be written as a CTE.
Moving Gold and Silver to the partners table, or other changes to more normalize (keys instead of descriptions) will change things.

select
[partner_id]
,[competency_name]
,[program]
,[SKUgroup]
,[competencyname]
,[start_date]
,[end_date]
from [dbo].[partners] a
inner join [dbo].[mastercompetency] b on a.[competency_name]=b.[competencyname]
where
[partner_id] IN (
select
[partner_id]
from
[dbo].[partners]
where
[competency_name] IN ('Gold Cloud Productivity','Gold Small and MidMarket Cloud Solutions')
group by
[partner_id]
HAVING count([competency_name]) = 2
UNION
select
[partner_id]
from
[dbo].[partners]
where
[competency_name] = 'Silver Small and MidMarket Cloud Solutions')
Post #1586932
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse