March 30, 2012 at 9:45 am
Hello all, I have spent the last few hours trying multiple things...finally decided to ask input.....(and no, I can't do this with a reporting front end, I need to do it in sql..)
Sample data:
create table #gmreport (
pin varchar(25),
cdate char(8),
btn varchar(25),
descr varchar(25),
callcount int,
callduration int,
charge money
)
insert #gmreport values
('000000002','20110608','5098544444','Debit','1','25','1.28'),
('000000002','20110621','2063865555','Debit','1','129','1.51'),
('001839930','20110602','2067303333','Debit','2','1080','4.54'),
('001839930','20110603','2067303333','Debit','1','26','1.28'),
('001839930','20110605','2067303333','Debit','1','892','2.91'),
('001839930','20110605','2532247777','Debit','1','811','2.80'),
('001839930','20110608','2532247777','Debit','3','2135','7.68'),
('001839930','20110609','2532247777','Debit','1','867','2.91'),
('001839930','20110610','2532247777','Debit','1','897','2.91'),
('001839930','20110611','2532247777','Debit','2','1799','5.82'),
('001839930','20110614','2532247777','Debit','1','900','2.91'),
('001839930','20110620','2067303333','Debit','1','376','1.98'),
('001839930','20110624','2532247777','Debit','1','387','1.98')
I want to create a report in tsql that will group by pin with subtotals and grand totals at the end, looks like this (with headers of course, just couldnt figure out how to line up here):
000000002 20110608 5098544444 Debit 1 25 $1.28
000000002 20110621 2063865555 Debit 1 129 $1.51
Subtotal 2 154 $2.79
001839930 20110602 2067303333 Debit 2 1,080 $4.54
001839930 20110603 2067303333 Debit 1 26 $1.28
001839930 20110605 2067303333 Debit 1 892 $2.91
001839930 20110605 2532247777 Debit 1 811 $2.80
001839930 20110608 2532247777 Debit 3 2,135 $7.68
001839930 20110609 2532247777 Debit 1 867 $2.91
001839930 20110610 2532247777 Debit 1 897 $2.91
001839930 20110611 2532247777 Debit 2 1,799 $5.82
001839930 20110614 2532247777 Debit 1 900 $2.91
001839930 20110620 2067303333 Debit 1 376 $1.98
001839930 20110624 2532247777 Debit 1 387 $1.98
Subtotal 15 10,170 $37.72
Grand Total 17 10,324 $40.51
I think rollup is required, just can't seem to get it right. thanks!
March 30, 2012 at 10:10 am
SELECT 'Subtotal',pin,SUM(CONVERT(INT,callduration)) sum_callduration,SUM(CONVERT(FLOAT,charge)) sum_charge FROM #gmreport
GROUP BY pin
Insert these values in another temp table to get the result in the format that you want.
March 30, 2012 at 10:22 am
You could try this, works pretty wellSELECT
Pin,
CDate,
btn,
callduration,
charge
FROM #gmreport
ORDER BY pin
COMPUTE SUM(callduration), SUM(charge) BY pin
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 30, 2012 at 11:15 am
Thats pretty close..!
here is the output..
000000002 20110608 5098544444 25 1.28
000000002 20110621 2063865555 129 1.51
154 2.79
1. Is there a way to name the subtotals?
2. Is there a way to indent them over under what it is summing?
3. Is there a way to do a grand total at the end?
thanks
March 30, 2012 at 11:20 am
It's all a question of Union All and columns to control order and section. You can add as many sub-totals/grand-totals/summaries as you like, interspersed with the main data, by partitioning that way.
I know you said you can't do this in the presentation layer (reports, et al). Mind if I ask why? T-SQL can do these things, but it's clunky, hard to maintain, and can get oddly buggy. For example, if the server collation is ever changed, your Order By statements for getting sub-totals interspersed into your main data will quite possibly break and start putting them in the wrong place. A reporting solution can do this kind of thing, and unless you change the report definition, will continue to do it correctly even if you upgrade your server, et al.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 30, 2012 at 11:29 am
This report will be attached to an email from the sql server and sent to the user....(big company issues...don't ask...LOL).
So its my only option for now......
Can you give me a brief example of union all to get this into that format..?
March 30, 2012 at 12:35 pm
MyDoggieJessie (3/30/2012)
You could try this, works pretty wellSELECTPin,
CDate,
btn,
callduration,
charge
FROM #gmreport
ORDER BY pin
COMPUTE SUM(callduration), SUM(charge) BY pin
Careful now... that's actually been deprecated not to mention that it will return multiple result sets instead of just one.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 12:37 pm
gmcnitt (3/30/2012)
I think rollup is required, just can't seem to get it right. thanks!
Correct. Post the code where you tried WITH ROLLUP and let's see what we can figure out for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 12:45 pm
Jeff Moden (3/30/2012)
MyDoggieJessie (3/30/2012)
You could try this, works pretty wellSELECTPin,
CDate,
btn,
callduration,
charge
FROM #gmreport
ORDER BY pin
COMPUTE SUM(callduration), SUM(charge) BY pin
Careful now... that's actually been deprecated not to mention that it will return multiple result sets instead of just one.
Drats! Busted...:-D
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 30, 2012 at 12:46 pm
MyDoggieJessie (3/30/2012)
Jeff Moden (3/30/2012)
MyDoggieJessie (3/30/2012)
You could try this, works pretty wellSELECTPin,
CDate,
btn,
callduration,
charge
FROM #gmreport
ORDER BY pin
COMPUTE SUM(callduration), SUM(charge) BY pin
Careful now... that's actually been deprecated not to mention that it will return multiple result sets instead of just one.
Drats! Busted...:-D
so i guess using it in new code is out? back to sum() over
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 31, 2012 at 11:42 am
This isn't pretty but looks like it may get what you want as far as formatting, etc.
create table #gmreport (
Type int, -- 1 = Entry or Subtotal, 2 = Final Total
pin varchar(25),
cdate char(8),
btn varchar(25),
descr varchar(25),
callcount int,
callduration int,
charge money
)
insert #gmreport values
(1,'000000002','20110608','5098544444','Debit','1','25','1.28'),
(1,'000000002','20110621','2063865555','Debit','1','129','1.51'),
(1,'001839930','20110602','2067303333','Debit','2','1080','4.54'),
(1,'001839930','20110603','2067303333','Debit','1','26','1.28'),
(1,'001839930','20110605','2067303333','Debit','1','892','2.91'),
(1,'001839930','20110605','2532247777','Debit','1','811','2.80'),
(1,'001839930','20110608','2532247777','Debit','3','2135','7.68'),
(1,'001839930','20110609','2532247777','Debit','1','867','2.91'),
(1,'001839930','20110610','2532247777','Debit','1','897','2.91'),
(1,'001839930','20110611','2532247777','Debit','2','1799','5.82'),
(1,'001839930','20110614','2532247777','Debit','1','900','2.91'),
(1,'001839930','20110620','2067303333','Debit','1','376','1.98'),
(1,'001839930','20110624','2532247777','Debit','1','387','1.98')
create table #resultsforreport (
ID int identity(1,1),
Type int,
pin varchar(25),
cdate char(8),
btn varchar(25),
descr varchar(25),
callcount int,
callduration int,
charge money
)
insert into #resultsforreport
(Type, pin, cdate, btn, descr, callcount, callduration, charge)
select * from #gmreport
union
select 1, pin, 'SubTotal', null, null, SUM(callcount), SUM(callduration), SUM(charge)
from #gmreport
group by pin
union
select 2, null, 'TOTAL', null, null, SUM(callcount), SUM(callduration), SUM(charge)
from #gmreport
update #resultsforreport
set pin = null where cdate = 'SubTotal'
select pin, cdate, btn, descr, callcount, callduration, charge from #resultsforreport
order by ID
drop table #gmreport
drop table #resultsforreport
April 2, 2012 at 2:08 pm
That is exactly what I needed! Thanks. I will set this up and see if the user agrees.......
April 2, 2012 at 4:49 pm
Excellent.
Hope that works out for you.
April 2, 2012 at 5:22 pm
You can do this with fewer table scans, without an additional temp table, and in such a fashion that you could actually turn it into a "reporting" view.
SELECT *
FROM
(
SELECT PIN,
CDate = CASE WHEN GROUPING(PIN) = 0 THEN 'SubTotal' ELSE 'Total' END,
BTN = NULL,
Descr = NULL,
CallCount = SUM(CallCount),
CallDuration = SUM(CallDuration),
Charge = SUM(Charge)
FROM #GMReport
GROUP BY PIN WITH ROLLUP
UNION ALL
SELECT PIN, CDate, BTN, Descr, CallCount, CallDuration, Charge
FROM #GMReport
)d
ORDER BY ISNULL(PIN,'Z'), CDate, BTN
;
Results using the test data given...
PIN CDate BTN Descr CallCount CallDuration Charge
--------- -------- ---------- ----- --------- ------------ ------
000000002 20110608 5098544444 Debit 1 25 1.28
000000002 20110621 2063865555 Debit 1 129 1.51
000000002 SubTotal NULL NULL 2 154 2.79
001839930 20110602 2067303333 Debit 2 1080 4.54
001839930 20110603 2067303333 Debit 1 26 1.28
001839930 20110605 2067303333 Debit 1 892 2.91
001839930 20110605 2532247777 Debit 1 811 2.80
001839930 20110608 2532247777 Debit 3 2135 7.68
001839930 20110609 2532247777 Debit 1 867 2.91
001839930 20110610 2532247777 Debit 1 897 2.91
001839930 20110611 2532247777 Debit 2 1799 5.82
001839930 20110614 2532247777 Debit 1 900 2.91
001839930 20110620 2067303333 Debit 1 376 1.98
001839930 20110624 2532247777 Debit 1 387 1.98
001839930 SubTotal NULL NULL 15 10170 37.72
NULL Total NULL NULL 17 10324 40.51
Still, that's "single purpose" and I can just see the user asking for reports like "Can I see subtotals by PIN and BTN?"
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2012 at 5:24 pm
Definitely a much improved version.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply