Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Join

Posted on 2009-04-09
3
Medium Priority
?
188 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

722 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