Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

SQL Join

I have a query that is joining two temp tables and not pulling the correct data.  

My first step is to dump data into a Temp table for the current month (based on a UDF).  If I pull the data in this temp table alone, I can reconcile to a correct value.

the second step puts data into a temp table for the fiscal year-to-date (based on a UDF).  If I pull the data in this temp table alone, I also am coming back to the correct YTD values.

When I join the tables the data is all coming back from #temp1 (MTD) but the correct value from #Temp2 (YTD) is not correct.

Any ideas?
select dept_id,pos_id,performing_prov_id,original_fin_class,proc_id,suM(proc_qty) as 'Proc_Qty',
sum(charges + vd_chg)AS 'Net Charges'
INTO #Temp1
from dbo.PIVOTED_TDL_TRAN_FISCALPERIOD
WHERE (dbo.PeriodToFiscalYear(dbo.PIVOTED_TDL_TRAN_FISCALPERIOD.Period) = 
					dbo.DateToFiscalYear(getdate()) AND 
					dbo.PIVOTED_TDL_TRAN_FISCALPERIOD.Period = dbo.LastMonth(getdate()))
group by dept_id,pos_id,performing_prov_id,original_fin_class,proc_id
 
 
select dept_id,pos_id,performing_prov_id,original_fin_class,proc_id,suM(proc_qty) as 'Proc_Qty',
sum(charges + vd_chg) AS 'Net Charges'
INTO #Temp2
from dbo.PIVOTED_TDL_TRAN_FISCALPERIOD
WHERE (dbo.PeriodToFiscalYear(dbo.PIVOTED_TDL_TRAN_FISCALPERIOD.Period) = 
					dbo.DateToFiscalYear(getdate()) AND 
					dbo.PIVOTED_TDL_TRAN_FISCALPERIOD.Period <= dbo.LastMonth(getdate()))
group by dept_id,pos_id,performing_prov_id,original_fin_class,proc_id
 
 
Select a.dept_id,a.pos_id,a.performing_prov_id,a.original_fin_class,a.proc_id,
a.[Net charges] as 'MTD NET Charges',a.Proc_Qty as 'MTD QTY',b.[Net Charges] as 'YTD NET Charges',
b.proc_qty as 'YTD Qty'
From #temp1 a
right outer join #temp2 b on a.dept_id = b.dept_id AND a.pos_id = b.pos_id AND a.performing_prov_id = b.performing_prov_id
AND a.original_fin_class = b.original_fin_class AND a.proc_id = b.proc_id
inner join clarity_dep on a.dept_id = clarity_dep.department_id
inner join zc_dep_rpt_grp_6 on zc_dep_rpt_grp_6.rpt_grp_six = clarity_dep.rpt_grp_six
where 
zc_dep_rpt_grp_6.rpt_grp_six = '10'

Open in new window

0
gdspeare
Asked:
gdspeare
1 Solution
 
gdspeareAuthor Commented:
I found my answer.

The issue was on my inner join on row 27.  The join was on the MTD table and should have been on the YTD table.
0
 
reb73Commented:
You are adding the following extra joins/filters below  only in the combined third query -

inner join clarity_dep on a.dept_id = clarity_dep.department_id
inner join zc_dep_rpt_grp_6 on zc_dep_rpt_grp_6.rpt_grp_six = clarity_dep.rpt_grp_six
where zc_dep_rpt_grp_6.rpt_grp_six = '10'

Why don't you try having these joins initially when populating the #Temp1 and #Temp2 tables instead?
0
 
TheRadDudeCommented:
Is each line incorrect on the YTD QTY, and YTD NET Charges.  Or is the overall total that is incorrect?  If it's the overall total you may have some values not existing in your clarity_dep table that are in your Temp2 table.


You could try taking the Temp tables out with the following SQL Statement, to see if it fixes it.  As it may be related to a timing issue.
Select A.dept_id, A.pos_id, A.performing_prov_id, A.original_fin_class, A.proc_id, sum([MTDProc_Qty]) as [MTD Proc_Qty], sum([MTDNetCharges]) as [MTD Net Charges], sum([YTDProc_Qty]) as [YTD Proc_Qty], sum([YTDNetCharges]) as [YTD Net Charges]
From
((
select dept_id,pos_id,performing_prov_id,original_fin_class,proc_id,sum(proc_qty) as [MTDProc_Qty],sum(charges + vd_chg) AS [MTDNetCharges], 0 as [YTDProc_Qty], 0 as [YTDNetCharges]
from dbo.PIVOTED_TDL_TRAN_FISCALPERIOD
WHERE (dbo.PeriodToFiscalYear(dbo.PIVOTED_TDL_TRAN_FISCALPERIOD.Period) = dbo.DateToFiscalYear(getdate()) AND dbo.PIVOTED_TDL_TRAN_FISCALPERIOD.Period = dbo.LastMonth(getdate()))
group by dept_id,pos_id,performing_prov_id,original_fin_class,proc_id union
 
select dept_id,pos_id,performing_prov_id,original_fin_class,proc_id, 0 as [MTDProc_Qty], 0 as [MTDNetCharges], suM(proc_qty) as [YTDProc_Qty], sum(charges + vd_chg) AS [YTDNetCharges]
from dbo.PIVOTED_TDL_TRAN_FISCALPERIOD
WHERE (dbo.PeriodToFiscalYear(dbo.PIVOTED_TDL_TRAN_FISCALPERIOD.Period) = dbo.DateToFiscalYear(getdate()) AND dbo.PIVOTED_TDL_TRAN_FISCALPERIOD.Period <= dbo.LastMonth(getdate()))
group by dept_id,pos_id,performing_prov_id,original_fin_class,proc_id
) as A 
 
inner join clarity_dep on a.dept_id = clarity_dep.department_id)
inner join zc_dep_rpt_grp_6 on zc_dep_rpt_grp_6.rpt_grp_six = clarity_dep.rpt_grp_six
where zc_dep_rpt_grp_6.rpt_grp_six = '10'
group by A.dept_id, A.pos_id, A.performing_prov_id, A.original_fin_class, A.proc_id

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now