ON THE FLY

Question

ON THE FLY

Viewing 6 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I have an idea, post sample data, sample results, and what YOU have done so far and what results YOU have gotten so far. Then we can help you figure out what is wrong instead of doing your work for you.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

Read up on WITH ROLLUP in BOL....

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

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

Go for broke... read up on WITH CUBE in BOL... that's where they teach you about the GROUPING function that works both with ROLLUP and CUBE.

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

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

Here is an extremely simple example of what you might try.

I am using a pass-thru query in Access to display the number of members updated within the past five days AND they also wanted to see the grand total, all in the same query...

CREATE PROC [dbo].[procUpdatesPending]

AS

DECLARE

@today smalldatetime

SELECT

@today = dbo.fnNoTime(GETDATE())

SELECT

dbo.fnNoTime(UpdatedDateTime) AS LastUpdated,

COUNT(*) AS Members,

'A' AS TheSort

INTO

#temp

FROM

PERSON

WHERE

dbo.fnNoTime(UpdatedDateTime) BETWEEN DATEADD(d,-5,@today) AND @today

GROUP BY

dbo.fnNoTime(UpdatedDateTime)

UNION

SELECT

NULL,

COUNT(*) AS Members,

'B' AS TheSort

FROM

PERSON

WHERE

dbo.fnNoTime(UpdatedDateTime) BETWEEN DATEADD(d,-5,@today) AND @today

SELECT

LastUpdated,

Members

FROM

#temp

ORDER BY

TheSort

DROP TABLE #temp

BTW - the dbo.fnNoTime() is a UDF that strips out the time portion.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

You might want to post your code for the UDF dbo.fnNoTime() as I doubt Best will figure out how to write it.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 6

I posted in another forum just today, take a look at

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=367982