GROUP BY problem

Question

GROUP BY problem

Tassilo Burger

Valued Member

Points: 66
More actions
May 13, 2005 at 5:28 pm

#164617

Hi everybody
any help would be great.
I have following records:
Hotel_ID | Day
152 | 1
362 | 2
193 | 3
193 | 4
362 | 5
239 | 6
239 | 7
I need the amount of days per hotel, but only if the days are in row.
I need following result:
Hotel_ID | Day1 | Days
152 | 1 | 1
362 | 2 | 1
193 | 3 | 2
362 | 5 | 1
239 | 6 | 2
Until now I'm working with this query:
SELECT
RTHotels.Hotel_ID,
MIN(RTHotels.Day) AS Day1,
Count(RTHotels.Day) AS Days
From
RTHotels
GROUP BY
RTHotels.Hotel_ID
ORDER BY
MIN(RTHotels.Day)
That brings me that:
Hotel_ID | Day1 | Days
152 | 1 | 1
362 | 2 | 2
193 | 3 | 2
239 | 6 | 2
but as you can see it's not correct; day 2 and day 5 for Hotel 362 are not in row.
Does anybody have any idea how to solve that problem?
Tassilo

Viewing 9 posts - 1 through 8 (of 8 total)

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

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 1

Hi the problem seems to be in the design of your database. In T-SQL you can not assume any relationship based on the order that data is entered. Could you supply the full definitions for your tables.

HTH Mike

Tassilo Burger Valued Member Points: 66 More actions · Answer 2

Ok, it's just one table from where I need this information:

Table name: RTHotels

Fields:

RT_ID int

Hotel_ID int

[Day] int

The query I mentioned is a Stored Procedure and works fine when the days per hotel are in a row, like for hotel 193 day 2 and day 3 are in a row.

So, the result for this hotel is correct. Day1 = 3 and Days = 2.

Tassilo

Tassilo Burger Valued Member Points: 66 More actions · Answer 3

May be it's confusing what I mean with "in a row". I don't mean a line or record. What I mean is that one day comes after the next day. So, for hotel 193 there are two days without interruption. In hotel 362 the guests stay one day (Day = 2), they leave for another hotel and are coming back on day 5. So, I need these stays seperated.

I hope it's a bit more clear.

Tassilo

Rob Farley SSCrazy Points: 2739 More actions · Answer 4

This is ugly code, I haven't tried to make it smarter yet. But I'm just posting it now to show that it is possible:

/*

create table hotels (hotel_id int, theday int)

insert into hotels values (152,1)

insert into hotels values (362,2)

insert into hotels values (193,3)

insert into hotels values (193,4)

insert into hotels values (362,5)

insert into hotels values (239,6)

insert into hotels values (239,7)

*/

select h_min.hotel_id, h_min.theday, h_max.theday - h_min.theday + 1

from hotels h_min

join

hotels h_max

on h_min.hotel_id = h_max.hotel_id

where h_max.theday =

(select max(h1.theday)

from hotels h1

where h1.hotel_id = h_min.hotel_id

and h1.theday >= h_min.theday

and not exists (select * from hotels h2

where h2.theday between h_min.theday and h1.theday

and h2.hotel_id h1.hotel_id)

)

and not exists (select * from hotels h_last where h_last.theday = h_min.theday - 1 and h_last.hotel_id = h_min.hotel_id)

To explain what I'm doing...

For each row where the previous day (h_last) is a different to the current one (h_min), then I want to find the minimum day and the maximum day. When I say maximum day, I'm meaning the largest day number which is at least as big as the minimum day, but with no different hotels in between.

I don't really have the time right now to optimise it better... but have a play around with it and see what you can do.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Tassilo Burger Valued Member Points: 66 More actions · Answer 5

Hi Rob,

I really don't know what to say. That's absolutely fantastic; thank you so so so much; and the very best thing is -> IT REALLY WORKS.

That rocks !!!!

Tassilo

Rob Farley SSCrazy Points: 2739 More actions · Answer 6

No problem. Sorry it's not very elegant. If I get the time, I will try to optimise it a bit.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Tassilo Burger Valued Member Points: 66 More actions · Answer 7

Hi Rob,

just to let you know. I adjusted your SP for my needs and found out following:

Ok, I didn't mentioned that I can have also day 0 (zero); because of that I had to bring in a isnull function. (See first statement for first where clausel)

select h_min.hotel_id, h_min.theday, h_max.theday - h_min.theday + 1

from hotels h_min

join

hotels h_max

on h_min.hotel_id = h_max.hotel_id

where h_max.theday =

isnull((select max(h1.theday)

from hotels h1

where h1.hotel_id = h_min.hotel_id

and h1.theday >= h_min.theday

and not exists (select * from hotels h2

where h2.theday between h_min.theday and h1.theday

and h2.hotel_id <> h1.hotel_id)

), 0)

and not exists (select * from hotels h_last where h_last.theday = h_min.theday - 1 and h_last.hotel_id = h_min.hotel_id)

With this change it works now also if "theday" = 0.

Thanks again,

Tassilo

Rob Farley SSCrazy Points: 2739 More actions · Answer 8

No problem.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au