Creating a Union from two working SP's exactly the same field names different tables

I have two working SP's included below that work independently in two seperate Crystal Reports perfectly. I need to combine them. They are the ROSS_SALESHDR and the ROSS_PRE_SALESHDR. The fields are exactly the same. However I do not know how to combine the two as a Union or where to place the Union. Can someone look at these and tell me where to place them so they wont break.....I would think you would either put UNION in the middle of the two or you would put UNION in between each SELECT.....don't know which. Any advice appreciated.....and if you coud show me exactly where it would go on the line with an explanation .... a BONUS. Thank you.

USE [RAMSDB]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_Route_Depositby_Date_2]    Script Date: 10/14/2013 10:41:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[Rpt_Route_Depositby_Date_2]
		@FirstDeliveryDate as Datetime, 
		@LastDeliveryDate as Datetime

as
------------------------------------------------------------
--     First Select Putting Data into Temp Table 4
------------------------------------------------------------
select distinct

		RT_ROUTE,
		RT_DESCRIPTION,
		RT_BRANCH 
into #temp4 
from dbo.ROSS_ROUTE r
      
------------------------------------------------------------
--     Second Select Putting Data into Temp Table 1
------------------------------------------------------------
select distinct

		sh.OH_ROUTE_NUMBER , 
		sh.OH_DELIVERY_DATE as PostDate, 
		isnull(sum(sh.OH_TICKET_TOTAL_DISCOUNT),0) as Discount,
		isnull(sum(sh.OH_TICKET_TOTAL),0) as InvoiceAmt,
		isnull(sum (ROA.ROA_AMT),0) as ROA_AMT,
		isnull(sum(de.AMOUNT),0) as Expenses  
into #temp1
from dbo.ROSS_SALESHDR sh 
		left outer join dbo.ROSS_DRIVER_EXPENSES de on sh.OH_ROUTE_NUMBER = de.ROUTE_NUMBER
		and  sh.OH_DELIVERY_DATE = de.DELIVERY_DATE	


left outer join 

(select distinct
		sh.OH_ROUTE_NUMBER,
		sh.OH_DELIVERY_DATE as PostDate,
		isnull(sum(sh.OH_TICKET_TOTAL_DISCOUNT),0) as Discount,
		sh.OH_CUSTOMER_NUMBER, 
		sum (sh.OH_CASH_RECEIVED_PREV_DELIV) as ROA_AMT
from dbo.ROSS_SALESHDR sh 
where sh.OH_ACCT_AR_TYPE = '000001' and  sh.OH_CUSTOMER_TYPE_ID In ('000003','000006')
		and sh.OH_CASH_RECEIVED_PREV_DELIV <> 0
group by sh.OH_ROUTE_NUMBER, sh.OH_DELIVERY_DATE, sh.OH_CUSTOMER_NUMBER) ROA
		on sh.OH_ROUTE_NUMBER  = ROA.OH_ROUTE_NUMBER and sh.OH_DELIVERY_DATE = ROA.PostDate
		and sh.OH_CUSTOMER_NUMBER = RoA.OH_CUSTOMER_NUMBER
where sh.OH_ACCT_AR_TYPE = '000001' and  sh.OH_CUSTOMER_TYPE_ID = '000003'
		and sh.OH_DELIVERY_DATE between @FirstDeliveryDate  and @LastDeliveryDate 
group by 
		sh.OH_ROUTE_NUMBER, 
		sh.OH_DELIVERY_DATE
		--sh.OH_TICKET_TOTAL_DISCOUNT
		


select distinct
		rsh.SH_ROUTE,
		rsh.SH_ROUTE_DATE, 
		isnull(sum(SH_DEPOSIT),0) as Deposit,
		rsh.SH_SETTLED
		
into #temp2
from dbo.ROSS_ROUTE_SETTLEMENT_HEADER rsh
group by 
		rsh.SH_ROUTE,
		rsh.SH_ROUTE_DATE,
		rsh.SH_DEPOSIT,
		rsh.SH_SETTLED



select distinct
		t1.OH_ROUTE_NUMBER , 
		t1.PostDate,
		t1.InvoiceAmt,
		t1.ROA_AMT,
		t1.Expenses,
		t2.Deposit,
		t2.SH_SETTLED,
		t1.DISCOUNT		

into #temp3 
from #temp1 t1 left outer join #temp2 t2 on 
		t1.OH_ROUTE_NUMBER = t2.SH_ROUTE and t1.PostDate = t2.SH_ROUTE_DATE
order by 1,2,4

/* Join distinct route table */
select 
		t3.*,
		r.RT_DESCRIPTION,
		r.RT_BRANCH
from #temp3 t3 inner join #temp4 r
on t3.OH_ROUTE_NUMBER  = r.RT_ROUTE

drop table #temp1;
drop table #temp2;
drop table #temp3;
drop table #temp4;
 
 
 
 
--select * from #temp1 

--exec [dbo].[Rpt_Route_Depositby_Date_2] '2013-01-14 00:00:00.000', '2013-3-14 00:00:00.000'

Open in new window



SECOND SET OF CODE FROM THE WORKING SP AND REPORT.

USE [RAMSDB]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_Route_Depositby_Date_NOT_Settled]    Script Date: 10/14/2013 10:42:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
                        
ALTER Procedure [dbo].[Rpt_Route_Depositby_Date_NOT_Settled]
		@FirstDeliveryDate as Datetime, 
		@LastDeliveryDate as Datetime

as
------------------------------------------------------------
--     First Select Putting Data into Temp Table 4
------------------------------------------------------------
select distinct

		RT_ROUTE,
		RT_DESCRIPTION,
		RT_BRANCH 
into #temp4 
from dbo.ROSS_ROUTE r
      
------------------------------------------------------------
--     Second Select Putting Data into Temp Table 1
------------------------------------------------------------
select distinct

		psh.OH_ROUTE_NUMBER , 
		psh.OH_DELIVERY_DATE as PostDate,
		ISNULL(SUM(psh.OH_TICKET_TOTAL_DISCOUNT),0) as Discount, 
		isnull(sum(psh.OH_TICKET_TOTAL),0) as InvoiceAmt,
		isnull(sum (ROA.ROA_AMT),0) as ROA_AMT,
		isnull(sum(de.AMOUNT),0) as Expenses  
into #temp1
from dbo.ROSS_PRE_SALESHDR psh 
		left outer join dbo.ROSS_DRIVER_EXPENSES de on psh.OH_ROUTE_NUMBER = de.ROUTE_NUMBER
		and  psh.OH_DELIVERY_DATE = de.DELIVERY_DATE	


left outer join 

(select distinct
		psh.OH_ROUTE_NUMBER,
		psh.OH_DELIVERY_DATE as PostDate,
		ISNULL(SUM(psh.OH_TICKET_TOTAL_DISCOUNT),0) as Discount,
		psh.OH_CUSTOMER_NUMBER, 
		sum (psh.OH_CASH_RECEIVED_PREV_DELIV) as ROA_AMT
from dbo.ROSS_PRE_SALESHDR psh 
where psh.OH_ACCT_AR_TYPE = '000001' and  psh.OH_CUSTOMER_TYPE_ID In ('000003','000006')
		and psh.OH_CASH_RECEIVED_PREV_DELIV <> 0
group by psh.OH_ROUTE_NUMBER, psh.OH_DELIVERY_DATE, psh.OH_CUSTOMER_NUMBER) ROA
		on psh.OH_ROUTE_NUMBER  = ROA.OH_ROUTE_NUMBER and psh.OH_DELIVERY_DATE = ROA.PostDate
		and psh.OH_CUSTOMER_NUMBER = RoA.OH_CUSTOMER_NUMBER
where psh.OH_ACCT_AR_TYPE = '000001' and  psh.OH_CUSTOMER_TYPE_ID = '000003'
		and psh.OH_DELIVERY_DATE between @FirstDeliveryDate  and @LastDeliveryDate 
group by 
		psh.OH_ROUTE_NUMBER, 
		psh.OH_DELIVERY_DATE
		
		


select distinct
		rsh.SH_ROUTE,
		rsh.SH_ROUTE_DATE, 
		isnull(sum(SH_DEPOSIT),0) as Deposit,
		rsh.SH_SETTLED
		
into #temp2
from dbo.ROSS_ROUTE_SETTLEMENT_HEADER rsh
group by 
		rsh.SH_ROUTE,
		rsh.SH_ROUTE_DATE,
		rsh.SH_DEPOSIT,
		rsh.SH_SETTLED



select distinct
		t1.OH_ROUTE_NUMBER , 
		t1.PostDate,
		t1.InvoiceAmt,
		t1.ROA_AMT,
		t1.Expenses,
		t2.Deposit,
		t1.Discount,
		t2.SH_SETTLED

into #temp3 
from #temp1 t1 left outer join #temp2 t2 on 
		t1.OH_ROUTE_NUMBER = t2.SH_ROUTE and t1.PostDate = t2.SH_ROUTE_DATE
order by 1,2,4

/* Join distinct route table */
select 
		t3.*,
		r.RT_DESCRIPTION,
		r.RT_BRANCH
from #temp3 t3 inner join #temp4 r
on t3.OH_ROUTE_NUMBER  = r.RT_ROUTE

drop table #temp1;
drop table #temp2;
drop table #temp3;
drop table #temp4;
 
 
 
 
--select * from #temp1 

--exec [dbo].[Rpt_Route_Depositby_Date_2] '2013-01-14 00:00:00.000', '2013-3-14 00:00:00.000'

Open in new window

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
You can do the UNION in the command
The syntax is something like

Exec SP_1 parameters
UNION ALL
Exec SP_2 parameters

mlmcc
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Can you give me a little more on the location of the code. There is no Command like in CR just two SPs I need to combine as one SP and point the report to it. So if there were four selects in each SP would there be four UNIONS or just one between he two SP's. Not quite following there.....sorry.
0
coreconceptsCommented:
Hi Ruavol,

        You could create a new stored procedure that executes both, modify both of the old procedures so that instead of selecting the results from #temp3 and #temp4 the first would SELECT INTO #yournewtemptable and the second would do an INSERT INTO SELECT and then select the results from that dataset.  

Before you do that though, are there other applications that would use these stored procedures as they are already (if so, you can just create an entirely new SPROC that combines all the code of the others)?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mlmccCommented:
You could create a view/query in the database or a SP that does the union

You can use a COMMAND in Crystal to do the UNION

There is a single UNION between the 2 SPs

mlmcc
0
James0628Commented:
FWIW, if you use a Command, as mlmcc suggested, then I guess you'll need to create parameters in the Command to pass to the SP parameters.  I only mention that because CR usually handles SP parameters automatically.


 Personally, I would probably create a new SP that combined the queries from those two.  By that, I mean actually combining the code from both SP's in one, not just doing an exec of each SP and combining the results.  Then I'd use the new SP for the report.  It's not necessarily the best solution (if the SP's change frequently, maintenance could be an issue), but I would prefer that to trying to combine the output from two different SP's.  But that's just me.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
That worked out well. Thank you for the expertise.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.