I need to create a stored procedure that basically gives a summarized list of records added together. Please tell me the best way to write this stored procedure.

Declare @Att varchar(5),@Type varchar(10); --(B) Billing or (W) WorkingSET @Att = '02500'SET @Type = 'B'IF @Type = 'W'--WORKINGBEGINselect tkinit as WA_ID, tklast+', ' + tkfirst as WA_name,clnum as Cl_id,clname1 as Cl_name,mmatter as Matter_id,mdesc1 as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365from df_dwtkmat, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand watty=tkinitand rectype=4020and pe=defvalueand watty=@Attgroup by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1having sum(arfbal+arcbal)<>0order by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1ENDELSEBEGIN --by Billing Partnerselect tkinit as BP_ID, tklast+', ' + tkfirst as BP_name,clnum as Cl_id,clname1 as Cl_name,mmatter as Matter_id,mdesc1 as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365from df_dwmatter, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand mbillaty=tkinitand rectype=4020and pe=defvalueand mbillaty=@Attgroup by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1having sum(arfbal+arcbal)<>0order by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1END

It might be helpful to get a little more clarity here on what you want to do. What does the procedure above do, and what do you want to different? What is the grouping you are looking for?

update the statement by adding
select....
INTO #TempTable1
from.....

Then select from #temptable1 to add them together...does that make sense?

0

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

My query appears to work just fine, I posted a question here to see if there is anything I can do to fine tune or improve my query before I make it a stored procedure. Any suggestions?

I would make the following changes:
1. Stop using the old style for JOINs. (Start using INNER JOIN, LEFT JOIN, etc.)
2. Add aliases to all your tables.
3. Add aliases to all your columns.
4. Change "tklast + ', ' + tkfirst" to "tklast , tkfirst" in your GROUP BY
5. Change "tklast + ', ' + tkfirst" to "WA_name" and "BP_name" in your ORDER BY

That is the best I can come up with, without knowing your table schemas.

Sorry, your question wasn't very clear to me. acperkins suggestions are good.

If you have an index on tklast, tkfirst, your query is not able to take advantage of it since you are stringing the order by togethter.

0

VBBRettAuthor Commented:

Hi, I'm sorry that I wasn't clear enough, but for all the columns that have a number value and that have the AR values, I need one extra row added to the query so that it shows a some of all the records for that column. Thanks!

That was why I suggested writing to a temp file first. Then you can just
select *,sum(a+b+c...) as TOTAL from #temp table order by.....
at the end of the proc....you can actual sum the way you are doing it as welll....it just would be a huge query

Thank you, that looks great but what I was really asking for was one more row that adds each numeric row at the very bottom. Any chance you can come up with the query to do that also? Thanks!

0

VBBRettAuthor Commented:

Do you have any idea on how to do a union or get it so that I have the total of each column at the very bottom please?

A union is one way to do it, but the order by will give you a problem,because the last line might get sorted out. One way is to write to a temp table like I mentioned previously, so the records are loaded to the temp table. Then insert the last (summary) row into the temp table, and then select * from the temp table.

0

VBBRettAuthor Commented:

Can you please show me the syntax on how to get this done please with the following query.

--Page 1.5: My Financials – Accounts Receivable

--Still needs grand total of each column

Declare @Attorney varchar(5),
@Type varchar(10); --(B) Billing or (W) Working

SET @Attorney = '02500'
SET @Type = 'B'

IF @Type = 'W'--WORKING
BEGIN
select tkinit as WA_ID, tklast+', ' + tkfirst as WA_name,
clnum as Client_id,
clname1 as Client_name,
mmatter as Matter_id,
mdesc1 as Matter_name,
convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365
from df_dwtkmat, df_dwcontrol, timekeep, matter, client
where matter=mmatter
and mclient=clnum
and watty=tkinit
and rectype=4020
and pe=defvalue
and watty=@Attorney
group by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
having sum(arfbal+arcbal)<>0
order by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1

END
ELSE
BEGIN --by Billing Partner
select tkinit as BP_ID, tklast+', ' + tkfirst as BP_name,
clnum as Client_id,
clname1 as Client_name,
mmatter as Matter_id,
mdesc1 as Matter_name,
convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365
from df_dwmatter, df_dwcontrol, timekeep, matter, client
where matter=mmatter
and mclient=clnum
and mbillaty=tkinit
and rectype=4020
and pe=defvalue
and mbillaty=@Attorney
group by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
having sum(arfbal+arcbal)<>0
order by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
END

You can try this,...you may have to alter some of the columns in the first query to be character for the final insert to work.

Declare @Attorney varchar(5),@Type varchar(10); --(B) Billing or (W) WorkingSET @Attorney = '02500'SET @Type = 'B'IF @Type = 'W'--WORKINGBEGINselect tkinit as WA_ID, tklast+', ' + tkfirst as WA_name,clnum as Client_id,clname1 as Client_name,mmatter as Matter_id,mdesc1 as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365into #tempAgingfrom df_dwtkmat, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand watty=tkinitand rectype=4020and pe=defvalueand watty=@Attorneygroup by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1having sum(arfbal+arcbal)<>0order by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1insert into #tempagingselect 'TOTAL' as WA_ID, '' as WA_name,'' as Client_id,'' as Client_name,'' as Matter_id,'' as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365from df_dwtkmat, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand watty=tkinitand rectype=4020and pe=defvalueand watty=@AttorneyENDELSEBEGIN --by Billing Partnerselect tkinit as BP_ID, tklast+', ' + tkfirst as BP_name,clnum as Client_id,clname1 as Client_name,mmatter as Matter_id,mdesc1 as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365into #tempAgingfrom df_dwmatter, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand mbillaty=tkinitand rectype=4020and pe=defvalueand mbillaty=@Attorneygroup by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1having sum(arfbal+arcbal)<>0order by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1insert into #tempAgingselect 'TOTAL' as BP_ID, '' as BP_name,'' as Client_id,'' as Client_name,'' as Matter_id,'' as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365from df_dwmatter, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand mbillaty=tkinitand rectype=4020and pe=defvalueand mbillaty=@AttorneyENDselect * from #tempAging

There is already an object named #tempAging in the database. From my understanding, the first #tempAging into statement and the second #tempAging statement have to be different names. Please confirm because it will not allow me to declare into #tempAging more then once.

Ok, call the first one #tempAging1
and the second #tempaging2

Then move the last select to within the begin..end of the statements, like this:

Declare @Attorney varchar(5),@Type varchar(10); --(B) Billing or (W) WorkingSET @Attorney = '02500'SET @Type = 'B'IF @Type = 'W'--WORKINGBEGINselect tkinit as WA_ID, tklast+', ' + tkfirst as WA_name,clnum as Client_id,clname1 as Client_name,mmatter as Matter_id,mdesc1 as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365into #tempAging1from df_dwtkmat, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand watty=tkinitand rectype=4020and pe=defvalueand watty=@Attorneygroup by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1having sum(arfbal+arcbal)<>0order by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1insert into #tempaging1select 'TOTAL' as WA_ID, '' as WA_name,'' as Client_id,'' as Client_name,'' as Matter_id,'' as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365from df_dwtkmat, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand watty=tkinitand rectype=4020and pe=defvalueand watty=@Attorneyselect * from #tempAging1ENDELSEBEGIN --by Billing Partnerselect tkinit as BP_ID, tklast+', ' + tkfirst as BP_name,clnum as Client_id,clname1 as Client_name,mmatter as Matter_id,mdesc1 as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365into #tempAging2from df_dwmatter, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand mbillaty=tkinitand rectype=4020and pe=defvalueand mbillaty=@Attorneygroup by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1having sum(arfbal+arcbal)<>0order by tkinit, tklast+', ' + tkfirst,clnum,clname1,mmatter,mdesc1insert into #tempAging2select 'TOTAL' as BP_ID, '' as BP_name,'' as Client_id,'' as Client_name,'' as Matter_id,'' as Matter_name,convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365from df_dwmatter, df_dwcontrol, timekeep, matter, clientwhere matter=mmatterand mclient=clnumand mbillaty=tkinitand rectype=4020and pe=defvalueand mbillaty=@Attorneyselect * from #tempAging2END

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.