Home » SQL & PL/SQL » SQL & PL/SQL » Query doubt
Query doubt [message #283230] Mon, 26 November 2007 07:01 Go to next message
sandman42
Messages: 14
Registered: June 2007
Junior Member
I have a legacy system which manages a gym entrance with contactless card.
There is one table, say ACCESSES, which is:

CARDID CHAR(10),
DATETIME DATE,
STATE NUMBER(2)

which keeps track of users entering the gymn. State is 0 (= paying entrance, i.e. user pays at the cash desk before entering), 1 (subscription entrance), 2 (sample entrance), which is like a subscription, but no money has been paid.

There is a SUBSCRIPTION_TYPES table, which is

ID NUMBER(10),
GYMN_CODE VARCHAR2(4),
DESCRIPTION VARCHAR2(60)

For example, ID=1, 2, 3; GYMN_CODE= 'YEAR', 'MONTH', 'WEEK'; DESCRIPTION = 'Yearly subscription', 'Monthly subscription', 'Weekly Subscription' and so on

Then there is a SUBSCRIPTIONS table, which is

CARDID CHAR(10),
DATESTART DATE,
DATEEND DATE,
SUBSCRIPTION_TYPE NUMBER(10)

where of course SUBSCRIPTION_TYPE has an entry in SUBSCRIPTION_TYPES.

One CARDID may have more than one subscription, and I'd like to write a query that gives me how many passages of subscribed or sample people I have during a certain period, assuming that if the same card id has more than one valid subscription for the period (say he/she has N), then it counts as N, one per category. I.e.:

Card 1 has subscription 1 and 3 valid during the period,
Card 2 has subscription 1, 2, and 3 valid during the period
Card 3 has subscription 1, 2, and 3 valid during the period
Card 4 has subscription 2 and 3 valid during the period
Card 5 has subscription 1, 2, and 3 valid during the period
Card 6 has subscription 2 valid during the period

Result should be:

Yearly subscription 4
Monthly subscription 5
Weekly subscription 5

How can I write such a query?

Thanks a lot

Ciao Smile
Re: Query doubt [message #283233 is a reply to message #283230] Mon, 26 November 2007 07:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hint :
Join 3 (or two if One is irrelevant) tables
Use group by
Use count

Direct answer otherwise i afraid could be spoonfeeding Smile
Thumbs Up
Rajuvan

[Updated on: Mon, 26 November 2007 07:23]

Report message to a moderator

Re: Query doubt [message #283236 is a reply to message #283230] Mon, 26 November 2007 07:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Although I love cryptic questions I'm afraid I can't do much with it. So far I could distil this creation script from your post:
CREATE TABLE accesses( cardid   VARCHAR(10)
                     , datetime DATE
                     , state    NUMBER(2)
                     )
/

CREATE TABLE subscription_types(  id         NUMBER(10)
                               ,  gymn_code  VARCHAR2(4)
                               , description VARCHAR2(60)
                               )
/

INSERT INTO subscription_types VALUES (1, 'YEAR' , 'Yearly subscription' );
INSERT INTO subscription_types VALUES (2, 'MONT', 'Monthly subscription');
INSERT INTO subscription_types VALUES (3, 'WEEK' , 'Weekly Subscription' );

CREATE TABLE subscriptions( cardid            CHAR(10)
                          , datestart         DATE
                          , dateend           DATE
                          , subscription_type NUMBER(10)
                          )
/

INSERT INTO subscriptions VALUES (1,SYSDATE-20,SYSDATE+20, 1);
INSERT INTO subscriptions VALUES (1,SYSDATE-20,SYSDATE+20, 3);
INSERT INTO subscriptions VALUES (2,SYSDATE-20,SYSDATE+20, 1);
INSERT INTO subscriptions VALUES (2,SYSDATE-20,SYSDATE+20, 2);
INSERT INTO subscriptions VALUES (2,SYSDATE-20,SYSDATE+20, 3);
INSERT INTO subscriptions VALUES (3,SYSDATE-20,SYSDATE+20, 1);
INSERT INTO subscriptions VALUES (3,SYSDATE-20,SYSDATE+20, 2);
INSERT INTO subscriptions VALUES (3,SYSDATE-20,SYSDATE+20, 3);
INSERT INTO subscriptions VALUES (4,SYSDATE-20,SYSDATE+20, 2);
INSERT INTO subscriptions VALUES (4,SYSDATE-20,SYSDATE+20, 3);
INSERT INTO subscriptions VALUES (5,SYSDATE-20,SYSDATE+20, 1);
INSERT INTO subscriptions VALUES (5,SYSDATE-20,SYSDATE+20, 2);
INSERT INTO subscriptions VALUES (5,SYSDATE-20,SYSDATE+20, 3);
INSERT INTO subscriptions VALUES (6,SYSDATE-20,SYSDATE+20, 2);

DROP TABLE subscriptions
/

DROP TABLE subscription_types
/

DROP TABLE accesses
/
MHE
Re: Query doubt [message #283238 is a reply to message #283230] Mon, 26 November 2007 07:33 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Even I like this kind of questions .

Initially I could have not resist myself from answering directly to such questions . Now i am in a mood of Self control Smile

Even thats better for OPs .

Thumbs Up
Rajuvan
Previous Topic: Query on two tables
Next Topic: how to clone the record
Goto Forum:
  


Current Time: Wed Dec 07 10:48:39 CST 2016

Total time taken to generate the page: 0.07895 seconds