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
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