sql server programming: consolidating rows -- sql server programming

Re: consolidating rows

SteveH
11/1/2007 3:31:01 PM

Hi Uri

As I mentioned in my original post, this method would not work if the
episode transferred from Ward C to Ward D then back to Ward C (this happens
alot, although only once would be enough to render it unsuitable)

X A B E 2007-10-01 00:00 2007-10-01 18:30
X A C E 2007-10-01 18:30 2007-10-02 18:45
X A D E 2007-10-02 11:00 2007-10-02 17:00

Thanks for making the effort though

The Itzik Ben-Gan article mentioned by Razvan & Hugo did the job.

Cheers
Steve

[quoted text, click to view]

Re: consolidating rows

SteveH
11/1/2007 3:35:01 PM

Thanks Hugo (and Itzik)

The article produced the result I was after, I just need to spend some time
trying to understand how/why now.

Cheers
Steve

[quoted text, click to view]

Re: consolidating rows

SteveH
11/1/2007 3:35:01 PM

Thanks Hugo (and Itzik)

The article produced the result I was after, I just need to spend some time
trying to understand how/why now.

And thanks for going the extra mile and tailoring it to my solution.

Cheers
Steve

[quoted text, click to view]

Re: consolidating rows

Hugo Kornelis
11/1/2007 10:33:51 PM

[quoted text, click to view]

Hi Steve,

If you are running SQL Server 2005, you might be able to do this
efficiently by using the new ranking functions, in a trick stolen from
Itzik Ben-Gan:

WITH TheCTE AS
(SELECT episode, unit, ward, doctor, start_datetime, end_datetiime,
ROW_NUMBER() OVER (PARTITION BY episode
ORDER BY start_datetime) AS rn1,
ROW_NUMBER() OVER (PARTITION BY episode, unit, ward, doctor
ORDER BY start_datetime) AS rn2
FROM YourTable)
SELECT episode, unit, ward, doctor,
MIN(start_datetime) AS start_datetime,
MAX(end_datetime) AS end_datetime
FROM TheCTE
GROUP BY episode, unit, ward, doctor, rn1-rn2;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

Re: consolidating rows

Steve Dassin
11/1/2007 11:52:14 PM

[quoted text, click to view]

Well here is the simplist solution that I didn't steal from anybody :P :)

Exec Rac
@transform='_Dummy_',
@rows='episode & unit & ward & doctor & start_datetime(d) &
end_datetime(d)',
@rowsort='start_datetime',
@pvtcol='Report Mode',
@from='stevetable',
@rowindicators='doctor{DenseRank}',
@defaults1='y',@rowbreak='n',
@select='select episode, unit, ward, doctor,
MIN(cast(start_datetime as datetime)) AS start_datetime,
MAX(cast(end_datetime as datetime)) AS end_datetime
from rac
group by episode,unit,ward,doctor,DenseRank
order by DenseRank'

Now how many users do you think will 'understand' either? But I think the
odds favor Rac. :-)
Does Itzik give what we're doing here a name? Exactly what would a user
search on to even try to find a solution?
Lets suppose some one did recognize this problem for what it is,
a dense rank over episode,unit,ward,doctor in the asc direction of
start_datetime.
The dense rank function in 2005 is completely useless. What's wrong with
this
picture? Users have enough trouble to begin with and then they're offered
code they can't understand. If they do understand what they want explain to
them that the function that they can understand can't understand the problem
they have. Itziks solution is a neat trick. So what. Users need simple
treats
they can eat rather than tricks that eat them.

www.beyondsql.blogspot.com
www.rac4sql.net

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.