VBBRett
asked on
Stored procedure that I need to create
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) Working
SET @Att = '02500'
SET @Type = 'B'
IF @Type = 'W'--WORKING
BEGIN
select 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_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=@Att
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 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_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=@Att
group by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
having sum(arfbal+arcbal)<>0
order by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
END
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?
ASKER
Well, the sum() values I want to add
update the statement by adding
select....
INTO #TempTable1
from.....
Then select from #temptable1 to add them together...does that make sense?
select....
INTO #TempTable1
from.....
Then select from #temptable1 to add them together...does that make sense?
ASKER
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.
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.
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.
ASKER
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
convert(decimal(11,2),roun d (sumfbal+arcbal),2))
+ convert(decimal(11,2),roun d (sum(arf1+arc1),2))
+ convert(decimal(11,2),roun d (sum(arf2+arc2),2))
+ convert(decimal(11,2),roun d (sum(arf3+arc3),2))
+ convert(decimal(11,2),roun d (sum(udf19+udf20),2))
+ convert(decimal(11,2),roun d (sum((arf4+arc4)-(udf19+ud f20)),2))
+ convert(decimal(11,2),roun d (sum(arf5+arc5),2))
+ convert(decimal(11,2),roun d (sum(arf6+arc6),2)) as Total Column
That should work as part of your query, too.
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
convert(decimal(11,2),roun
+ convert(decimal(11,2),roun
+ convert(decimal(11,2),roun
+ convert(decimal(11,2),roun
+ convert(decimal(11,2),roun
+ convert(decimal(11,2),roun
+ convert(decimal(11,2),roun
+ convert(decimal(11,2),roun
That should work as part of your query, too.
ASKER
Please clarify on how I would do this in my current stored procedure.
Declare @Att varchar(5),
@Type varchar(10); --(B) Billing or (W) Working
SET @Att = '02500'
SET @Type = 'B'
IF @Type = 'W'--WORKING
BEGIN
select 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_365,
convert(decimal(11,2),round (sumfbal+arcbal),2))
+ convert(decimal(11,2),round (sum(arf1+arc1),2))
+ convert(decimal(11,2),round (sum(arf2+arc2),2))
+ convert(decimal(11,2),round (sum(arf3+arc3),2))
+ convert(decimal(11,2),round (sum(udf19+udf20),2))
+ convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2))
+ convert(decimal(11,2),round (sum(arf5+arc5),2))
+ convert(decimal(11,2),round (sum(arf6+arc6),2)) as Total Column
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=@Att
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 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_365,
convert(decimal(11,2),round (sumfbal+arcbal),2))
+ convert(decimal(11,2),round (sum(arf1+arc1),2))
+ convert(decimal(11,2),round (sum(arf2+arc2),2))
+ convert(decimal(11,2),round (sum(arf3+arc3),2))
+ convert(decimal(11,2),round (sum(udf19+udf20),2))
+ convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2))
+ convert(decimal(11,2),round (sum(arf5+arc5),2))
+ convert(decimal(11,2),round (sum(arf6+arc6),2)) as Total Column
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=@Att
group by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
having sum(arfbal+arcbal)<>0
order by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
END
ASKER
Incorrect syntax near +
ASKER
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!
ASKER
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.
ASKER
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),roun d (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),roun d (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),roun d (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),roun d (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),roun d (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),roun d (sum((arf4+arc4)-(udf19+ud f20)),2)) as AR_121_180,
convert(decimal(11,2),roun d (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),roun d (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),roun d (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),roun d (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),roun d (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),roun d (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),roun d (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),roun d (sum((arf4+arc4)-(udf19+ud f20)),2)) as AR_121_180,
convert(decimal(11,2),roun d (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),roun d (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
--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),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
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),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
convert(decimal(11,2),roun
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) 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
into #tempAging
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
insert into #tempaging
select '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_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
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
into #tempAging
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
insert into #tempAging
select '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_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
END
select * from #tempAging
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great! Awesome! How do you move the total row to the very bottom as opposed to the top? Thank you very much!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmm, is there another way I could do this? Apparently my query did not like that and it is giving me an error stating the following:
Msg 156, Level 15, State 1, Line 67
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Line 126
Incorrect syntax near 'END'.
Msg 156, Level 15, State 1, Line 67
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Line 126
Incorrect syntax near 'END'.
ASKER
Nevermind, it works. I just didn't add the word end at the end of the queries. Thank you very much, amazing work!