alwayshouston
MIS
Hi All!
I need help in design of a database table. I have a Sales with the sales information coming from different sources. I need to priortize the output in my query to what I define in the table. Let me explain this via table structure. Lets say I have a source Sales Table as below:
Sales_ID Sales Commission Source
NEWYORK 10000 100 Adam
NEWYORK 10001 Chris
NEWYORK 107 Debbie
HOUSTON 20000 Delay
HOUSTON 100 Morrison
HOUSTON 20000 132 Perry
I have a source priority table which tells me the priority of source given to any id.
Source_ID Sales_ID Priority
ADAM NEWYORK 3
CHRIS NEWYORK 1
DEBBIE NEWYORK 2
DELAY HOUSTON 1
MORRISON HOUSTON 3
PERRY HOUSTON 2
I need to show the data in the priority as it listed in the table above. If some ask for NEWYORK data, it should retrieve data in priority (CHRIS first, Debbie 2 and ADAM 3). If the data is missing for any fields in the first priority, it should look at the second priority and give me the data. The ideal output of NEWYORK and Houston data is as follow:
Sales_ID Sales Sales_Source Comm Comm_Source
NEWYORK 10001 Chris 107 Debbie
HOUSTON 20000 Delay 132 Perry
I would like to design a table to capture above scenario and write query to show the data in sync with the prirority. How would I do that? Feel free to send me a link to a article that will help me.
Any help in this regard is appreciated!
Thanks in Advance!
I need help in design of a database table. I have a Sales with the sales information coming from different sources. I need to priortize the output in my query to what I define in the table. Let me explain this via table structure. Lets say I have a source Sales Table as below:
Sales_ID Sales Commission Source
NEWYORK 10000 100 Adam
NEWYORK 10001 Chris
NEWYORK 107 Debbie
HOUSTON 20000 Delay
HOUSTON 100 Morrison
HOUSTON 20000 132 Perry
I have a source priority table which tells me the priority of source given to any id.
Source_ID Sales_ID Priority
ADAM NEWYORK 3
CHRIS NEWYORK 1
DEBBIE NEWYORK 2
DELAY HOUSTON 1
MORRISON HOUSTON 3
PERRY HOUSTON 2
I need to show the data in the priority as it listed in the table above. If some ask for NEWYORK data, it should retrieve data in priority (CHRIS first, Debbie 2 and ADAM 3). If the data is missing for any fields in the first priority, it should look at the second priority and give me the data. The ideal output of NEWYORK and Houston data is as follow:
Sales_ID Sales Sales_Source Comm Comm_Source
NEWYORK 10001 Chris 107 Debbie
HOUSTON 20000 Delay 132 Perry
I would like to design a table to capture above scenario and write query to show the data in sync with the prirority. How would I do that? Feel free to send me a link to a article that will help me.
Any help in this regard is appreciated!
Thanks in Advance!