sql join two queries in stored procedure return as one to datareader

[brian123]

I want to group and then join two tables in a stored procedure and return results to a datareader. The non-grouped data in each table has a many to many relationship.

select entity, count(*) as vcnt, sum(amt) as vamt
from vtable
where date = @dateparm
and rec_type = 'V'
group by entity
 
select entity, count(*) as ccnt, sum(amt) as camt
from ctable
where date = @dateparm
and rec_type = 'C'
group by entity

I want to return joined by entity (vtable left outer join ctable); fields entity, vcnt,vamt,ccnt,camt, all on same row. By entity after group by.

Before group by entity is a many to many relationship.

[ck9663]

try:

select entity,
vcnt = sum(case when rec_type = 'v' then 1 else 0 end),
vamt = sum(case when rec_type = 'v' then amt else 0.00 end),
ccnt = sum(case when rec_type = 'c' then 1 else 0 end),
camt = sum(case when rec_type = 'c then amt else 0.00 end),
from ctable
where date = @dateparm and isnull(rec_type,'') in ('c','v')
group by entity
 

Happy Coding!!!

~~ CK

[brian123]

SELECT V.entity, V.vcnt, V.vamt, C.entity, C.ccnt, C.camt
FROM (SELECT entity, COUNT(*) AS vcnt, SUM(amt) AS vamt FROM vtable WHERE date = @dateparm AND rec_type = 'V' GROUP BY entity) AS V
LEFT OUTER JOIN
(SELECT entity, isnull(COUNT(*),0) AS ccnt, isnull(SUM(amt),0) AS camt FROM ctable WHERE date = @dateparm AND rec_type = 'C' GROUP BY entity) AS C
ON C.entity = V.entity