help with query

[ben]

I have a Session table:

Username Type
------------------
bob private
bob private
sally private
bob duet

And I have an event table

Username Date Type
---------------------------
bob 1/1/04 private
bob 1/2/04 private
bob 1/3/04 private
bob 1/3/04 duet
bob 1/3/04 duet

The way it works is this. A client signs up for appointments, and
they are placed in the event table. They buy sessions whenever they
want. The session table works like a clearing account. When they buy
sessions, rows are added to the session table, and when they use
sessions, rows are deleted. If they have an appointment, but no
sessions left, then they need to buy more. They can and will be
signed up for more appointments then sessions that they have bought.
I somehow need to join these tables, to show whether a client needs
to pay for an appointment or not. So the result set I want is:

Username Date Type Pay?
-----------------------------------
bob 1/1/04 private No
bob 1/2/04 private No
bob 1/3/04 private Yes
bob 1/3/04 duet No
bob 1/3/04 duet Yes

I can't actually delete sessions from the session table since this is
dataset is for display purposes, and the session still need to exist.

Does anybody have any ideas?

Thank you,

Ben

[David Portas]

It helps if you include DDL with your posts so that we can see what the keys
and constraints are.

Neither table, as posted, appears to have a primary key. In the Sessions
table there seems to be no obvious reason to add redundant rows to represent
the quantity of sessions purchased. Just add a quantity column and increment
or decrement it as appropriate. Since you say you are deleting the rows
anyway you are presumably not keeping any additional information about
individual session purchases?

In the Events table you have a duplicate row so for simplicity I've changed
your sample data slightly. Presumably you can include the date and time of
the event to make it unique? Or do you allow multiple events for the same
user at the same time?

Here's the DDL and sample data:

CREATE TABLE Sessions (username VARCHAR(10), type VARCHAR(10), session_qty
INTEGER NOT NULL CHECK (session_qty>=0), PRIMARY KEY (username, type))

CREATE TABLE Events (username VARCHAR(10), eventdate DATETIME, type
VARCHAR(10), PRIMARY KEY (username, eventdate, type))

INSERT INTO Sessions VALUES ('bob', 'private',2)
INSERT INTO Sessions VALUES ('sally', 'private',1)
INSERT INTO Sessions VALUES ('bob', 'duet',1)

INSERT INTO Events VALUES ('bob', '20040101', 'private')
INSERT INTO Events VALUES ('bob', '20040102', 'private')
INSERT INTO Events VALUES ('bob', '20040103', 'private')
INSERT INTO Events VALUES ('bob', '20040103', 'duet')
INSERT INTO Events VALUES ('bob', '20040104', 'duet')

Here's the query:

SELECT E.username, E.eventdate, E.type,
CASE WHEN S.session_qty >=
(SELECT COUNT(*)
FROM Events
WHERE username = E.username
AND type = E.type
AND eventdate <= E.eventdate)
THEN 'No' ELSE 'Yes' END AS to_pay
FROM Events AS E
LEFT JOIN Sessions AS S
ON S.username = E.username
AND S.type = E.type

--
David Portas
SQL Server MVP
--