Query Subset of Data

I am fairly new to this - so please forgive me. I have a set of data that I
collected with one query but in the same stored procedure I would like to
turn around and use this data in another query to get additional info about
the agent. Please see below for example:
select paymmain.agentno,
sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor =
'Refund' or PaidFor
= 'Credit' or PaidFor = 'Reversed') then paidamount * -1
else paidamount end) as paidamount,
sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
Rev' or PaidFor =
'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
paidamount * -1 else paidamount end) else (case when
(PaidFor = 'NSF Rev' or PaidFor
= 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
(paidamount * -1) - servicefee else paidamount - servicefee
end) end) as sumamount,
sum(servicefee) as servicefeesum
from paymmain
join agendefa on paymmain.agentno = agendefa.agentno
where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
paymmain.agentno >= 0000
and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
group by paymmain.agentno
order by paymmain.agentno
This will return one record per agent. I have additional information that I
need about the agent - like bank info and status. So I would like to take
the above query and use it in another query to get the additional info
before returning from the stored procedure.
Also, I tired adding the info to this query but the status is a bit which it
gives an error that a bit can not be included ...
Any help would be appreciate - even if it is a keyword to do a search on.
You can put this query into a view and then run another select query
that join the agent table to this view.
Another way is to put this query as a inner query in the FROM clause.
SQL Server support the following syntax.
Select * from Agent, (another select statement) as SomeTempTableName
where Agent.JoinField=SomeTempTableName.JoinField.
HHickey wrote:
> I am fairly new to this - so please forgive me. I have a set of data that I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor =
> 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
> Rev' or PaidFor =
> 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount - servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>
|||You could SELECT this into a temporary table level variable, or a temp table
of some type then use it in another procedure or within the same procedure.
Rick Sawtell
MCT, MCSD, MCDBA
"HHickey" <hhickey@.pcgroup.holdings.com> wrote in message
news:er1v73%23qEHA.3172@.TK2MSFTNGP10.phx.gbl...
> I am fairly new to this - so please forgive me. I have a set of data that
I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info
about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor
=
> 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor =
'NSF
> Rev' or PaidFor =
> 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount -
servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that
I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which
it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>