Need Help Merging Two Queries into One

[txdave35]

Hey guys,

I'm a bit stuck trying to join two queries so I can get a single report.

Query 1 contains a list of everyone I need on the report. Query 2 shows people's monthly and yearly salary. I was able to do this with derived tables.
The kicker is not everyone in query 1 has a salary so since they dont have records in my query 2 tables, they would get dropped off the list and I don't want that.

I'm trying to left join the two queries but I keep getting syntax error. The vast majority of my query requests have worked with inner joins so this is kinda new to me.

Can anyone show me how to join or merge the two queries?

query 1

SELECT a.recip_ssn_nbr

FROM 

(SELECT   a.RECIP_SSN_NBR,
         CASE 
              when a.RECIP_TYPE_CD = '10' then 'MEMBER' 
              when recip_type_cd = '20' then 'SPOUSE' 
              when recip_type_cd = '39' then 'TRUSTEE' 
              when recip_type_cd = '40' then 'FEDERAL' 
              when recip_type_cd = '50' then 'BENEFICIARY' 
              when recip_type_cd = '90' then 'OTHER' 
         end AS type,

         case 
              when a.benef_stat_cd = 'AC' then 'ACTIVE'
              when a.benef_stat_cd = 'DP' then 'DROP'
              when a.benef_stat_cd = 'DD' then 'DECEASE'
              when a.benef_stat_cd = 'IN' then 'INACTIVE'

         end as status,

       
            
         a.BENEF_STAT_CD,
         a.RECIP_RETIR_DT,
         b.ANTY_PYMT_TOT_AMT,
         b.ANTY_PYMT_DT
FROM     DSNP.PR01_T_RECIP_SYS a,
         DSNP.PR01_T_ANTY_PYMT b
WHERE    a.RECIP_SSN_NBR=b.RECIP_SSN_NBR
and      a.benef_seq_nbr = b.benef_seq_nbr
AND      a.RECIP_RETIR_DT >= '2011-01-01'
AND      a.BENEF_SEQ_NBR = 1
and      b.anty_pymt_dt = (select min(e.anty_pymt_dt) from dsnp.pr01_t_anty_pymt e
                            where e.recip_ssn_nbr = b.recip_ssn_nbr) ) as A

query 2

select a.mbr_ssn_nbr,
       sum(a.year)as YEARLY,
       b.monthly



FROM



(SELECT   a.MBR_SSN_NBR,
         a.MBR_HIST_SVC_CR_DT,
         a.MBR_SAL_AMT as YEAR


FROM     DSNP.PR01_T_MBR_HIST a


where year(a.mbr_hist_svc_cr_dt) = (select max(year(b.mbr_hist_svc_cr_dt)) from dsnp.pr01_t_mbr_hist b
                                                         where b.mbr_ssn_nbr = a.mbr_ssn_nbr)



) as A,




(SELECT   a.MBR_SSN_NBR,
         a.MBR_HIST_SVC_CR_DT,
         sum(a.MBR_SAL_AMT) as MONTHLY


FROM     DSNP.PR01_T_MBR_HIST a



where a.mbr_hist_svc_cr_dt = (select max(b.mbr_hist_svc_cr_dt) from dsnp.pr01_t_mbr_hist b
                                                         where b.mbr_ssn_nbr = a.mbr_ssn_nbr)



group by a.mbr_ssn_nbr,
a.mbr_hist_svc_cr_dt    ) as B


where a.mbr_ssn_nbr = b.mbr_ssn_nbr

group by a.mbr_ssn_nbr,
         b.monthly

 

[gk53]

it looks like to stand alone queries
so first one should be something like

; with e as
(
	select MIN_anty_pymt_dt = min(anty_pymt_dt), 
			recip_ssn_nbr 
		from dsnp.pr01_t_anty_pymt
		GROUP BY recip_ssn_nbr
)
SELECT   a.RECIP_SSN_NBR,
         CASE 
              when a.RECIP_TYPE_CD = '10' then 'MEMBER' 
              when recip_type_cd = '20' then 'SPOUSE' 
              when recip_type_cd = '39' then 'TRUSTEE' 
              when recip_type_cd = '40' then 'FEDERAL' 
              when recip_type_cd = '50' then 'BENEFICIARY' 
              when recip_type_cd = '90' then 'OTHER' 
         end AS type,
         case 
              when a.benef_stat_cd = 'AC' then 'ACTIVE'
              when a.benef_stat_cd = 'DP' then 'DROP'
              when a.benef_stat_cd = 'DD' then 'DECEASE'
              when a.benef_stat_cd = 'IN' then 'INACTIVE'
         end as status,
         a.BENEF_STAT_CD,
         a.RECIP_RETIR_DT,
         b.ANTY_PYMT_TOT_AMT,
         b.ANTY_PYMT_DT
FROM     DSNP.PR01_T_RECIP_SYS a
	left join DSNP.PR01_T_ANTY_PYMT b
	on    a.RECIP_SSN_NBR=b.RECIP_SSN_NBR
	and      a.benef_seq_nbr = b.benef_seq_nbr
	AND      a.RECIP_RETIR_DT >= '2011-01-01'
	AND      a.BENEF_SEQ_NBR = 1
	left join e
	on b.anty_pymt_dt = MIN_anty_pymt_dt
		and e.recip_ssn_nbr = b.recip_ssn_nbr

the second one

;with m as 
(
	select year_mbr_hist_svc_cr_dt = max(year(mbr_hist_svc_cr_dt)), 
		   last_mbr_hist_svc_cr_dt = max(mbr_hist_svc_cr_dt),  
		   mbr_ssn_nbr
		from dsnp.pr01_t_mbr_hist
		group by mbr_ssn_nbr
)
select a.mbr_ssn_nbr,
       sum(a.year)as YEARLY,
       b.monthly as monthly
FROM 
	(SELECT   a.MBR_SSN_NBR,
			 a.MBR_HIST_SVC_CR_DT,
			 a.MBR_SAL_AMT as YEAR
	FROM     DSNP.PR01_T_MBR_HIST a
		inner join m
		on a.mbr_ssn_nbr = m.mbr_ssn_nbr
	) as A,
(SELECT   a.MBR_SSN_NBR,
         a.MBR_HIST_SVC_CR_DT,
         sum(a.MBR_SAL_AMT) as MONTHLY
FROM     DSNP.PR01_T_MBR_HIST a
		inner join m
		on a.mbr_ssn_nbr = m.mbr_ssn_nbr
	group by a.mbr_ssn_nbr,
		a.mbr_hist_svc_cr_dt    ) as B
where a.mbr_ssn_nbr = b.mbr_ssn_nbr
group by a.mbr_ssn_nbr,
         b.monthly

I did not see any link between queries and reason to use left join in second query

 

[TamarGranor]

Can you use a CTE to create the first table to make it available to the second. Something like this:

WITH EmpList AS
(Your first query)

(Your second query including an outer join with EmpList)

Tamar