Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to join both queries?

Status
Not open for further replies.

nc297

Programmer
Apr 7, 2010
162
US
How would I join these two queries? Below is my attempt at it but I don't like the results, too many 0's coming out.

If I run the queries alone I don't get as many numbers. When I do them alone I don't get that many 0's. Since the Group by has been moved up underneath the where clause, I'm confused as to where to join them.

Select n.doc, ISNULL(PendinDDS, 0) as PendinDDS, ISNULL(PendinPCO, 0) as PendinPCO
From natdocfile n
LEFT JOIN

(Select t.doc, ISNULL(count(t.clmssn),0) as PendinDDS, dib_mvt_seq_num
from t2dibpend t
where DIB_MVT_TYP='R' and (org_id like 'R%' or org_id like 'S%') and t.clmssn in (select ssn from seb2a21.specnew.dbo.people where completedt is null)
group by t.doc, dib_mvt_seq_num
Having (dib_mvt_seq_num = max(dib_mvt_seq_num)
and dib_mvt_seq_num is not null))a
on n.doc =a.doc
left join

--Pending in PCO
(Select t.doc, ISNULL(count(t.clmssn),0) as PendinPCO, dib_mvt_seq_num
from t2dibpend t
where (DIB_MVT_TYP='R') and (org_id like 'P%')
group by t.doc, dib_mvt_seq_num, t.clmssn
Having (dib_mvt_seq_num = max(dib_mvt_seq_num)
and dib_mvt_seq_num is not null))b
on n.doc = b.doc


I want the results to come out as:

DOC PendinDDS PendinPCO
488 6 2
492 1 1
001 5 6

etc...

I thought I could do it like my other one's but the group by is not under the where clause.

SELECT n.doc, ISNULL(totpenfodds,0) AS totpenfodds, ISNULL(pend250,0) AS pend250
FROM natdocfile n
LEFT JOIN
(SELECT t.fo, Isnull(COUNT(t.cossn), 0) AS [TotPenFODDS]
FROM t16pendall t
WHERE (mft_posn1_cd IN( 'b', 'd' ) OR ( mft_posn1_cd = 'a'
AND aged_alien_rsw = 'y' ))
GROUP BY t.fo)a
on n.doc = a.fo
LEFT JOIN
(SELECT t.fo, Isnull(COUNT(t.cossn), 0) AS [Pend250]
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') OR (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
AND ( Datediff(DAY, flg_cdt, Getdate()) > 250 )
AND ( Datediff(DAY, flg_cdt, Getdate()) < 299 )
GROUP BY t.fo)b
ON n.doc = b.fo
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top