Solved

SQL Join

Posted on 2009-04-09
3
182 Views
Last Modified: 2012-05-06
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
Comment
Question by:gdspeare
3 Comments
 

Accepted Solution

by:
gdspeare earned 0 total points
ID: 24109244
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
 
LVL 25

Expert Comment

by:reb73
ID: 24109264
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
 
LVL 1

Expert Comment

by:TheRadDude
ID: 24109620
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question