January 9, 2008 at 9:12 am
Hi,
I have a number of tables that are used for member subscription to a number of camps.
We know the max number of possible subscriptions (i.e. 50 places).
For every subscription we remember the datetime of it.
How can I find out the following:
1. get only the optional subscriptions (in this case: all subscriptions above 50).
2. get only the effective subscriptions (<= 50)
I don't have a flag that states that a given subscription is for the "reserve" list.
I only have the datetime. So if you order by the created_date field you'll see all subscriptions for a given camp.
Could you help me find out how to get the 2 above mentioned lists?
This is the query so far (the one returning ALL subscriptions where a camp has reached its limit.
SELECT DISTINCT
a.id, a.member_id, c.related_id, a.sessie_id, a.created_date
FROM ideefiks.STM_SUBSCRIPTION a
LEFT OUTER JOIN ideefiks.STM_SESSIE e ON a.sessie_id = e.id
LEFT OUTER JOIN ideefiks.STM_MEMBER c ON a.member_id = c.id
WHERE
--this where clause makes sure I only get those subscriptions from which the camp has reached its limit.
(SELECT count(r.id) FROM STM_SUBSCRIPTION r WHERE r.sessie_id = e.id) > e.max_aantal_deelnemers
group by a.id, a.member_id, c.related_id, e.id, c.id, c.verwantschap_id, e.kamp_id, a.sessie_id, a.created_date
ORDER BY a.created_date
This is a small extraction of the result (session_id = the camp).
subId member_id related_id ses_id created_Date
229930823081552008-01-05 10:30:53.930
230030833081552008-01-05 10:30:54.353
230430923091552008-01-05 12:09:53.517
230530933091552008-01-05 12:09:53.923
234431653163552008-01-06 12:32:07.810
234531663163552008-01-06 12:32:08.170
239932203219552008-01-06 20:03:52.967
247233063305552008-01-07 11:15:42.870
248033213319552008-01-07 12:26:50.390
255734313430552008-01-07 21:01:29.580
273911321129552008-01-09 07:02:36.020
278037053704552008-01-09 11:18:06.517
Thank you for the help!
Christophe
January 9, 2008 at 10:10 am
Without DDL and test data it is difficult to know what you want.
Maybe you need to use the ROW_NUMBER() function like:
SELECT *
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY S.sessie_id ORDER BY S.created_date) AS Row
        ,S.*
    FROM ideefiks.STM_SUBSCRIPTION S
) a
    LEFT JOIN ideefiks.STM_SESSIE e
        ON a.sessie_id = e.id
    LEFT JOIN ideefiks.STM_MEMBER c
        ON a.member_id = c.id
WHERE a.Row <= e.max_aantal_deelnemers
ORDER BY a.created_date
January 9, 2008 at 6:18 pm
I am not entirely sure I follow what you are after. This article will provide you some help tips on how to post your problem correctly, so members are more likely to assist in solving your problem. Not only will more people help you with your problem we will be able to better understand the issue at hand rather than making guesses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
1. get only the optional subscriptions (in this case: all subscriptions above 50).
2. get only the effective subscriptions (<= 50)
The simple answer would be to say the following, but I do not know enough about your problem to be accurate.
SELECT *
FROM TABLE
WHERE SUBSCRIPTIONS > 50
AND
SELECT *
FROM TABLE
WHERE SUBSCRIPTIONS <=50
January 10, 2008 at 12:31 am
yes, of course I understand it's not easy to understand.
Indeed, Ken, you're close to what I had in mind, but the thing is that SQL servers keeps telling me that ROW_NUMBER isn't a valid function.
When I make it rank=count(*) SQL server struggles with the OVER keyword. I work via a SQL server management studio express (2005) to a sql server (hosting at a hostingcompany) with version 8.0.2039.
Shortly:
There is a table called stm_subscription, containing all subscriptions for another table stm_sessie (containing some camp information).
Each camp has a maximum number of participants (lets say 50 for example). People must be able to subscribe continously without that limit. The application must be smart enough to determine if the member (= the one who is subscribing to a camp) is enlisted in a reserve list or not.
How can I determine who's first? Well, the subscription table contains a created_date field (up to seconds). Via this I can easily request a complete list of all participants, ordered by subscription date.
Now, when the 51th participant (member) wishes to subscribe, this person must be treated the same way as all other subscriptions, but he/she should be visualized in another list on screen. Problem is that the query is built dynamically in code (via searchcriteria). When the searchcriteria contains "showOnlyReserve", it means that I only want to see those participants where:
1. the number of subscriptions is larger than the number of maximum allowed participants
2. in our case: I want to see the 51th (and higher, 52th, 53th ...) participant
For the first one I use following in the where clause:
(SELECT count(r.id) FROM STM_SUBSCRIPTION r WHERE r.sessie_id = e.id) > e.max_number_participants
Via that query I get to see only those camps which have more subscriptions that allowed,
But this gives me ALL participants. The big question is: how can I get only the ones that are reserve - knowing that the only thing you can use is the creation_date.
I thought that, when I add a rownumber, I could then use that rownumber in my query to see if it is bigger than the max_number_participants, but I can't get it to work. So Ken's solution seems close. I've used Microsofts solution as well (http://support.microsoft.com/kb/186133), but the rank numbers I see don't make any sense.
I could send you (by mail) the complete query perhaps?
Thanks for your help,
Christophe
January 10, 2008 at 2:26 am
christophe (1/10/2008)
SQL servers keeps telling me that ROW_NUMBER isn't a valid function.When I make it rank=count(*) SQL server struggles with the OVER keyword. I work via a SQL server management studio express (2005) to a sql server (hosting at a hostingcompany) with version 8.0.2039.
If you're using SQL 2000, you should rather post in the SQL 2000 forums. In the 2005 forums, people assume you're using SQL 2005 and will offer suggestions based on that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply