GROUP BY problem
-
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
-
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
-
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
-
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
-
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 -
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
-
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 -
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
-
No problem.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply