Inserting fields inside of a Stored Procedure with Temp Tables

The following code below was written by an Expert......I would like to be as good as he is one day to feed my own famliy. My problem is I am just now learning the fundamentals of Stored Procedures and SQL Queries.

I need to embed the field called OH_TICKET_TOTAL_DISCOUNT. It comes from the Table ROSS_SALESHDR table.

What I do not understand is the logic that would have to occur to put it in the code below. I do not understand the use of the temp tables and why they do not all have the same number of fields in them. I have tried several things like inserting
sh.OH_TICKET_TOTAL_DISCOUNT AS Discount,    ........ in the first select statement. but then got an aggregate and group by error.
I need to learn how to insert fields to these SP's your help is appreciated. The end result will be a stored procedure used for a Crystal Report.

USE [RAMSDB]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_Route_Depositby_Date_2]    Script Date: 09/27/2013 08:55:08 ******/
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

select distinct RT_ROUTE, RT_DESCRIPTION, RT_BRANCH into #temp4
      from dbo.ROSS_ROUTE r
     
--------------------------------------------------------------------------------

select
sh.OH_ROUTE_NUMBER ,
sh.OH_DELIVERY_DATE as PostDate,
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
sh.OH_ROUTE_NUMBER,
sh.OH_DELIVERY_DATE as PostDate,
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

---------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------

select
t1.OH_ROUTE_NUMBER ,
t1.PostDate,
t1.InvoiceAmt,
t1.ROA_AMT,
t1.Expenses,
t2.Deposit 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'
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:
Try changing the second one to

select 
sh.OH_ROUTE_NUMBER , 
sh.OH_DELIVERY_DATE as PostDate,
OH_TICKET_TOTAL_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 
sh.OH_ROUTE_NUMBER,
sh.OH_DELIVERY_DATE as PostDate,
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,
OH_TICKET_TOTAL_DISCOUNT 

Open in new window


WHen you have a group by to handle the sums in the select, all fields must be in a Aggregate (sum) or the group by

mlmcc
0
Brian CroweDatabase AdministratorCommented:
If you have a GROUP BY clause then every field in the SELECT statement must be an aggregate statement (SUM, AVG, COUNT, etc.) or in the GROUP BY clause itself.  To start try adding sh.OH_TICKET_TOTAL_DISCOUNT to both clauses and see if that works for you.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
It executes the command just fine but when I run the SP I get no new field...?

USE [RAMSDB]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_Route_Depositby_Date_2]    Script Date: 09/27/2013 08:55:08 ******/
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

select distinct RT_ROUTE, RT_DESCRIPTION, RT_BRANCH into #temp4
      from dbo.ROSS_ROUTE r
     
--------------------------------------------------------------------------------

select
sh.OH_ROUTE_NUMBER ,
sh.OH_DELIVERY_DATE as PostDate,
OH_TICKET_TOTAL_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
sh.OH_ROUTE_NUMBER,
sh.OH_DELIVERY_DATE as PostDate,
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,
OH_TICKET_TOTAL_DISCOUNT
 

---------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------

select
t1.OH_ROUTE_NUMBER ,
t1.PostDate,
t1.InvoiceAmt,
t1.ROA_AMT,
t1.Expenses,
t2.Deposit 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'
0
Brian CroweDatabase AdministratorCommented:
That's because you've got several levels of queries running here dumping them into temp tables and then pulling from those temp tables.  You would also have to add it to

t1.OH_ROUTE_NUMBER ,
t1.PostDate,
t1.InvoiceAmt,
t1.ROA_AMT,
t1.Expenses,
t2.Deposit,
t1.OH_TICKET_TOTAL_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

The query that pulls from #temp3 uses a SELECT * so you don't need to add it there.
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 was it thank you gentlemen.
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.