Help creating SQL Virtual Column (computed column) for combining Sales person to Industry

Question

I have a table with a column labelled SalespersonID, this has 5 Values
Sales Person 1, Sales Person 2, Sales Person 3, Sales Person 4, Sales Person 5.

Not sure how to create a new column (Industry) that has 1 of 3 values (Industrial, Civil, Fire) dependant on the Sales Person assigned to that row.

eg Sales Person 1&2 = Civil, Sales Person 3&4 = Industrial, Sales Person 5 = Fire.

so far managed to pull 7 columns from 3 tables, including a COnvert(float,sum (quantity*value)) as Total and grouped by ID, which i am super proud of, but completely lost on new column as mentioned above.

Apologies for a really vague description, not great with the right terms to use here

Any help greatly appreciated.

Answer 1

Hi Ashley,
Seeing your description where you wrote that a person might belong to multiple industry type, I think you might need to have a connector table that bridges the SalesPerson and Industry table.

Assuming you have ...

(SalesPerson)
ID
Name
...

(Industry)
ID
IndustryType
...

You can connect the two tables with a connector table

(SalesPerson_Industry)
SalesPersonID
IndustryID

Where `SalesPersonID` references to SalesPerson.ID, and `IndustryID` references to Industry.ID

This way you can store multiple Industry ID for a single sales person.

Just an idea ...

Answer 2

Thanks Ipang,
I do understand this but not possible at the moment I do not have access to write or add to our database. I am purely trying to utilise existing data controlled by our ERP software.
The only way I could add table is via SQL query but not confident doing that yet.
There are multiple industry IDs in our database one for every category of record and it's messy trying to link.
Our old process Industry ID was on the quote level.
Sales reps would select an industry ID when creating quote. Now we want to default industry ID based on Sales rep name. That's why I was hoping to create a virtual column where if salespersonid is 1 or 2 then IndustryID is "Civil"
If salespersonid is 3 or 4 then industryid ID "industrial"
If salespersonid is 3 then Industryid is "Fire"

We purely want this data in a grid to then convert to a graph for our manager. Without adding or editing existing data in our database.

I appreciate your help Ipang, hope you understand.

Answer 3

Ashley Morgan
I did a google search and found this reference

To perform an SQL add column operation, use the ALTER TABLE ADD command. This command alters a table and adds in a column with the specified data type. Using this command, you can add a column to a table after you have created the table.

Don't know if this will help as I have not tested.

Answer 4

Cheers Rik,

As mentioned I do not want to alter or modify existing data. That's why I'm hoping for a virtual column. I already have a calculated column for calculating quote total with the convert (float,sum quotequantity*quoteunitpricebase) as quote total group by Quote I'd)

I can then save the query & table to be able to see it or reference any time and filter any field in ERP..

I do apologise if I'm not making myself clear enough.

I need a query that gives a "temporary/virtual" result. I can then save that grid (table) for use at a later date.

My other option which I don't want to do is export data to excel and do it there.. kinda defeats the purpose of using SQL.

If there is no way using SQL to add a virtual column, I apologise on my lack of SQL and thank you for your support.

Answer 5

Ashley Morgan
Your knowledge on SQL is way above mine, so please don't apologise.

The problem is that your virtual column must cross-reference your existing data-bases in a meaningful manner.

If you don't want to alter your existing db's, then you must create a db which may be JOINed to your existing db's

SALESPERSON DUTY

You would then be able to cross-reference these people across a number of db's -> virtually