• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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

Open in new window

0
VBBRett
Asked:
VBBRett
  • 12
  • 9
2 Solutions
 
twolCommented:
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?
0
 
VBBRettAuthor Commented:
Well, the sum() values I want to add
0
 
twolCommented:
update the statement by adding
select....
INTO #TempTable1
from.....

Then select from #temptable1 to add them together...does that make sense?
0
Free Tool: Path Explorer

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.

 
VBBRettAuthor Commented:
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?
0
 
Anthony PerkinsCommented:
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.
0
 
twolCommented:
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!
0
 
twolCommented:
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),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

That should work as part of your query, too.


0
 
VBBRettAuthor Commented:
Please clarify on how I would do this in my current stored procedure.
0
 
twolCommented:
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 

Open in new window

0
 
VBBRettAuthor Commented:
Incorrect syntax near +
0
 
VBBRettAuthor Commented:
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?
0
 
twolCommented:
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
0
 
twolCommented:
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    

Open in new window

0
 
VBBRettAuthor Commented:
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.
0
 
twolCommented:
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) 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 #tempAging1
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 #tempaging1
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

select * from #tempAging1

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 #tempAging2
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 #tempAging2
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

select * from #tempAging2

END

Open in new window

0
 
VBBRettAuthor Commented:
Great!  Awesome!  How do you move the total row to the very bottom as opposed to the top?  Thank you very much!
0
 
twolCommented:
Updatse the two select statements, adding this order by


select * from #tempAging1
order by case WA_ID when 'total' then 'zzzz' else WA_ID end


select * from #tempAging2
order by case BP_ID when 'total' then 'zzzz' else BP_ID end
0
 
VBBRettAuthor Commented:
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'.
0
 
VBBRettAuthor Commented:
Nevermind, it works.  I just didn't add the word end at the end of the queries.  Thank you very much, amazing work!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

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.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now