Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: query for distinct multiplicity records

    I have a legacy system which manages a gym entrance with contactless card.
    There is one table, say ACCESSES, which is:
    Code:
    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
    Code:
    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
    Code:
    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    This would be my first guess ...

    Code:
    select sum(case 
               when subsctiption_type = 1 then 1 
                                          else 0 
               end) as yearly_subscription,
           sum(case 
               when subsctiption_type = 2 then 1 
                                          else 0 
               end) as monthly_subscription,
           sum(case 
               when subsctiption_type = 3 then 1 
                                          else 0 
               end) as weekly_subscription
    from subscriptions
    where datestart <= parmDate and parmDate <= dateend
    --=cf

  4. #4
    Join Date
    Mar 2010
    Posts
    2
    Quote Originally Posted by anacedent View Post
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    Here they are:
    Code:
    CREATE TABLE ACCESSES ("CARDID" CHAR(10) NOT NULL, 
        "DATETIME" DATE NOT NULL, "STATE" NUMBER(2))  
        TABLESPACE "VIOLAZIONI" ;
    CREATE TABLE SUBSCRIPTION_TYPES ("ID" NUMBER(10) NOT NULL,
        "GYMN_CODE" VARCHAR2(4) NOT NULL, "DESCRIPTION" VARCHAR2(60))  
        TABLESPACE "VIOLAZIONI" ;
    CREATE TABLE SUBSCRIPTIONS ("CARDID" CHAR(10) NOT NULL, 
        "DATESTART" DATE NOT NULL, "DATEEND" DATE NOT NULL, 
        "SUBSCRIPTION_TYPE" NUMBER(10) NOT NULL)  
        TABLESPACE "VIOLAZIONI";
    	
    --
    -- ACCESSES TABLE INSERT STATEMENTS
    --
    INSERT INTO ACCESSES ( ACCESSES.CARDID, ACCESSES.DATETIME, ACCESSES.STATE ) 
    		 VALUES ( '1', TO_DATE('01/01/2010 07:15:25 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 0 ) 
    /
    INSERT INTO ACCESSES ( ACCESSES.CARDID, ACCESSES.DATETIME, ACCESSES.STATE ) 
    		 VALUES ( '2', TO_DATE('01/01/2010 08:15:44 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 0 ) 
    /
    INSERT INTO ACCESSES ( ACCESSES.CARDID, ACCESSES.DATETIME, ACCESSES.STATE ) 
    		 VALUES ( '3', TO_DATE('01/02/2010 11:11:12 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 0 ) 
    /
    INSERT INTO ACCESSES ( ACCESSES.CARDID, ACCESSES.DATETIME, ACCESSES.STATE ) 
    		 VALUES ( '4', TO_DATE('01/03/2010 09:12:33 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 0 ) 
    /
    --
    -- SUBSCRIPTION_TYPES TABLE INSERT STATEMENTS
    --
    INSERT INTO SUBSCRIPTION_TYPES ( SUBSCRIPTION_TYPES.ID, SUBSCRIPTION_TYPES.GYMN_CODE, SUBSCRIPTION_TYPES.DESCRIPTION ) 
    		 VALUES ( 1, 'YEAR', 'Yearly subscription' ) 
    /
    INSERT INTO SUBSCRIPTION_TYPES ( SUBSCRIPTION_TYPES.ID, SUBSCRIPTION_TYPES.GYMN_CODE, SUBSCRIPTION_TYPES.DESCRIPTION ) 
    		 VALUES ( 2, 'MONT', 'Montly subscription' ) 
    /
    INSERT INTO SUBSCRIPTION_TYPES ( SUBSCRIPTION_TYPES.ID, SUBSCRIPTION_TYPES.GYMN_CODE, SUBSCRIPTION_TYPES.DESCRIPTION ) 
    		 VALUES ( 3, 'WEEK', 'Week subscription' ) 
    /
    --
    -- SUBSCRIPTIONS TABLE INSERT STATEMENTS
    --
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '1', TO_DATE('01/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('12/31/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 1 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '1', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/28/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 3 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '2', TO_DATE('01/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('12/31/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 1 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '2', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/28/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 2 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '2', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/07/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 3 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '3', TO_DATE('01/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('12/31/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 1 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '3', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/28/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 2 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '3', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/07/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 3 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '4', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/28/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 2 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '4', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/07/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 3 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '5', TO_DATE('01/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('12/31/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 1 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '5', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/28/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 2 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '5', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/07/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 3 ) 
    /
    INSERT INTO SUBSCRIPTIONS ( SUBSCRIPTIONS.CARDID, SUBSCRIPTIONS.DATESTART, SUBSCRIPTIONS.DATEEND, SUBSCRIPTIONS.SUBSCRIPTION_TYPE ) 
    		 VALUES ( '6', TO_DATE('02/01/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/07/2010 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 2 ) 
    /
    What I'd like with this data is that, if I run a query with datetime betweek 01/02/2010 00.00.00 and 07/02/2010 23.59.59 :

    Yearly subscription 3
    Monthly subscription 3
    Weekly subscription 4


    Ciao

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Ugh, surely there's a better way to signify inclusive date ranges:

    Code:
    select sum(case 
               when subscription_type = 1 then 1 
                                          else 0 
               end) as yearly_subscription,
           sum(case 
               when subscription_type = 2 then 1 
                                          else 0 
               end) as monthly_subscription,
           sum(case 
               when subscription_type = 3 then 1 
                                          else 0 
               end) as weekly_subscription
    from subscriptions
    where (datestart<=to_date('01/02/2010','MM/DD/YYYY') and dateend >= to_date('01/02/2010','MM/DD/YYYY') and dateend <= to_date('07/02/2010 23.59.59','MM/DD/YYYY HH24.MI.SS'))
       or (datestart<=to_date('01/02/2010','MM/DD/YYYY') and dateend >= to_date('07/02/2010 23.59.59','MM/DD/YYYY HH24.MI.SS'))
       or (datestart>=to_date('01/02/2010','MM/DD/YYYY') and dateend <= to_date('07/02/2010 23.59.59','MM/DD/YYYY HH24.MI.SS'))
       or (datestart>=to_date('01/02/2010','MM/DD/YYYY') and datestart<=to_date('07/02/2010 23.59.59','MM/DD/YYYY HH24.MI.SS') and dateend >= to_date('07/02/2010 23.59.59','MM/DD/YYYY HH24.MI.SS'))
    I still think I don't understand your goal, thoiugh, since I get 4, 5, and 5 back. I'm thinking that you'd want to include something in a count if it's start and end date defined a range that overlaps the 1/2-7/2/2010 date range.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •