?
Solved

Stored procedure that I need to create

Posted on 2011-10-18
22
Medium Priority
?
289 Views
Last Modified: 2012-05-12
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
Comment
Question by:VBBRett
  • 12
  • 9
22 Comments
 
LVL 7

Expert Comment

by:twol
ID: 36986276
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
 

Author Comment

by:VBBRett
ID: 36987187
Well, the sum() values I want to add
0
 
LVL 7

Expert Comment

by:twol
ID: 36987325
update the statement by adding
select....
INTO #TempTable1
from.....

Then select from #temptable1 to add them together...does that make sense?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:VBBRett
ID: 36987694
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36987876
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
 
LVL 7

Expert Comment

by:twol
ID: 36988045
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
 

Author Comment

by:VBBRett
ID: 36991860
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
 
LVL 7

Expert Comment

by:twol
ID: 36992689
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
 

Author Comment

by:VBBRett
ID: 36992838
Please clarify on how I would do this in my current stored procedure.
0
 
LVL 7

Expert Comment

by:twol
ID: 36993006
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
 

Author Comment

by:VBBRett
ID: 36993077
Incorrect syntax near +
0
 

Author Comment

by:VBBRett
ID: 36993126
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
 

Author Comment

by:VBBRett
ID: 36993338
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
 
LVL 7

Expert Comment

by:twol
ID: 36993539
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
 

Author Comment

by:VBBRett
ID: 36993557
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
 
LVL 7

Expert Comment

by:twol
ID: 36993602
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
 

Author Comment

by:VBBRett
ID: 36993746
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
 
LVL 7

Accepted Solution

by:
twol earned 2000 total points
ID: 36993811
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
 

Author Comment

by:VBBRett
ID: 36993869
Great!  Awesome!  How do you move the total row to the very bottom as opposed to the top?  Thank you very much!
0
 
LVL 7

Assisted Solution

by:twol
twol earned 2000 total points
ID: 36994046
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
 

Author Comment

by:VBBRett
ID: 36994437
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
 

Author Comment

by:VBBRett
ID: 36994474
Nevermind, it works.  I just didn't add the word end at the end of the queries.  Thank you very much, amazing work!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question