Solved

SQL Join

Posted on 2009-04-09
3
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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