Solved

Posted on 2011-10-18

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
```

22 Comments

select....

INTO #TempTable1

from.....

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

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.

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.

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.

```
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
```

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

```
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
```

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
```

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

Title | # Comments | Views | Activity |
---|---|---|---|

SQL query to yield additional records | 9 | 39 | |

Custom auto number | 6 | 27 | |

Windows Management Instrument | 6 | 8 | |

SQL - combining multiple records into 1 record | 5 | 11 |

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

Connect with top rated Experts

**22** Experts available now in Live!