asked on

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

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

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.

SOLUTION