Help with view

I have a table with the following layout:
Docnumber, SlspersonA, AcctStatus, ThirdPartyName, SlspersonB, SlsManager,
SlsperACommissionRate, SlsperBCommissionRate, SlsManagerRate.
I need to create a view (or report) somehow that allows me to return
multiple rows where the Salesperson = X. The problem is that the SlspersonI
D
can exist in SlspersonA on one transaction and be SlspersonB on another. A
sample result set is below:
INV1000, GOEBEL, New, Marriott, HENDERSON,NULL,5.0,5.0,0
INV2000,HENDERSON, New, Hyatt, GOEBEL,NULL,5.0,5.0,0
How can I structure the select statement using variables to pull in just the
following:
Docnumber, Slsperson, AcctStatus, ThirdPartyName, CommissionRate.
Any help would be greatly appreciatedlooks fairly straight forward unless I am completely mistaken
declare @.salesperson varchar(10)
Set @.salesperson = 'GOEBEL'
Select Docnumber, @.salesperson, AcctStatus, ThirdPartyName, CommissionRate
from tbl
where @.salesperson in (SlspersonA,SlspersonB)Let me know if this is what u
wanted.|||create view SomeView as
select Docnumber
, SlspersonA as Slsperson
, AcctStatus
, ThirdPartyName
, CommissionRate
from SomeTable
union
select Docnumber
, SlspersonB as Slsperson
, AcctStatus
, ThirdPartyName
, CommissionRate
from SomeTable;
go
Select from SomeView where Slsperson = 'HENDERSON';
"Dan Shepherd" <DanShepherd@.discussions.microsoft.com> wrote in message
news:DF69F7BD-88FC-4B52-B613-7FEEA696F9C3@.microsoft.com...
> I have a table with the following layout:
> Docnumber, SlspersonA, AcctStatus, ThirdPartyName, SlspersonB, SlsManager,
> SlsperACommissionRate, SlsperBCommissionRate, SlsManagerRate.
> I need to create a view (or report) somehow that allows me to return
> multiple rows where the Salesperson = X. The problem is that the
SlspersonID
> can exist in SlspersonA on one transaction and be SlspersonB on another.
A
> sample result set is below:
> INV1000, GOEBEL, New, Marriott, HENDERSON,NULL,5.0,5.0,0
> INV2000,HENDERSON, New, Hyatt, GOEBEL,NULL,5.0,5.0,0
> How can I structure the select statement using variables to pull in just
the
> following:
> Docnumber, Slsperson, AcctStatus, ThirdPartyName, CommissionRate.
> Any help would be greatly appreciated