Subtotals in t-sql

Question

Post reply

Subtotals in t-sql

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

gmcnitt Old Hand Points: 335 More actions · Answer 1

Both work, but yes, improved.....now I just need to spend the time to figure it all out....:-)

BTW, it works perfect on my 20,000 + rows, took 7 seconds total.....

Is there any way to replace the null values with a space?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Heh... I just knew that would be the next question and I should have done that to begin with.

SELECT PIN = ISNULL(PIN,''),

CDate = ISNULL(CDate,''),

BTN = ISNULL(BTN,''),

Descr = ISNULL(Descr,''),

CallCount,

CallDuration,

Charge

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

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

gmcnitt (4/2/2012)
Both work, but yes, improved.....now I just need to spend the time to figure it all out....:-)
BTW, it works perfect on my 20,000 + rows, took 7 seconds total.....
Is there any way to replace the null values with a space?

Just curious... that included the time to output to the screen? If so, grid mode or text mode?

The reason I ask is because that just seems awfully slow to me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gmcnitt Old Hand Points: 335 More actions · Answer 4

Output to grid, and I am using a view that includes around 48 individual tables...

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Ah... the VIEW might explain it. It's actually being executed twice if you're using it directly in the code I wrote. You'll get much better performance if you do a SELECT/INTO a temp table and point my code at the temp table because the view will only need to execute once that way. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gmcnitt Old Hand Points: 335 More actions · Answer 6

Yes, thats what I did, the select into the temp table is what takes the time, running the query on the temp table is milli seconds......thanks again, still awaiting the users input, but I think its exactly what the doctor ordered.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

You bet. Thank you for the thoughtful feedback. Really appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)