Solved

SQL Join

Posted on 2009-04-09
3
178 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
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.​
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now