Results 1 to 9 of 9

Thread: count problem

  1. #1
    Join Date
    Feb 2011
    Posts
    12

    Smile Unanswered: count problem

    Hello guys,

    I have this table for me to create a query. The table called "customer subscription". I have to create a query to count the number of subscription started and to create a query to count the number of subscription reactivated.

    Here is the scenario:

    1. Number of subscription started, no previous subscription for the same cstID
    2. Number of subscription reactivated, at least one previous subscription of the same cstID.


    Code:
    prsID	cstID	prsStartDate		prsEndDate	
    2	1	1/18/2006 1:45:00 PM	1/18/2007 1:45:00 PM
    3	2	1/20/2006 1:55:00 PM	1/20/2007 1:55:00 PM
    4	4	2/28/2006 10:42:00 AM	2/28/2007 10:42:00 AM
    5	6	3/1/2006 8:08:00 PM	3/1/2007 8:08:00 PM
    6	9	3/8/2006 8:55:00 AM	3/8/2007 8:55:00 AM
    7	10	3/10/2006 11:33:00 AM	3/10/2007 11:33:00 AM
    8	11	3/11/2006 2:09:00 PM	3/11/2007 2:09:00 PM
    9	12	3/11/2006 4:19:00 PM	3/11/2007 4:19:00 PM
    10	19	3/13/2006 2:50:00 PM	3/13/2007 2:50:00 PM
    11	21	3/14/2006 11:56:00 AM	3/14/2007 11:56:00 AM
    12	24	3/30/2006 1:16:00 PM	3/30/2007 1:16:00 PM
    13	8	4/6/2006 2:33:00 PM	4/6/2007 2:33:00 PM
    14	34	4/10/2007 2:47:00 PM	4/10/2008 2:47:00 PM
    15	41	4/11/2007 11:39:00 PM	4/11/2008 11:39:00 PM
    16	42	6/6/2007 12:13:00 AM	6/6/2008 12:13:00 AM
    17	4	7/9/2007 12:19:00 PM	7/9/2008 12:19:00 PM
    18	43	7/23/2007 3:10:00 PM	7/23/2008 3:10:00 PM
    19	44	8/6/2007 6:32:00 PM	8/6/2008 6:32:00 PM
    20	4	8/28/2008 7:53:00 PM	8/28/2009 7:53:00 PM
    21	44	1/3/2009 1:59:00 PM	1/3/2010 1:59:00 PM
    22	45	3/9/2009 4:35:00 PM	3/9/2010 4:35:00 PM
    23	47	4/8/2009 1:49:00 AM	4/8/2010 1:49:00 AM
    24	49	4/8/2009 2:08:00 AM	4/8/2010 2:08:00 AM
    25	59	7/15/2009 3:57:00 PM	7/15/2011 3:57:00 PM
    26	60	7/16/2009 4:54:00 AM	7/16/2011 4:54:00 AM
    27	63	7/16/2009 5:42:00 AM	7/16/2011 5:42:00 AM
    28	65	7/30/2009 6:29:00 PM	7/30/2011 6:29:00 PM
    29	57	7/31/2009 1:42:00 PM	7/31/2010 1:42:00 PM
    30	66	8/1/2009 9:38:00 PM	8/1/2011 9:38:00 PM
    31	13	8/5/2009 12:43:00 AM	8/5/2010 12:43:00 AM
    32	26	8/28/2009 1:35:00 AM	8/28/2010 1:35:00 AM
    33	25	8/28/2009 5:18:00 AM	8/28/2010 5:18:00 AM
    34	4	8/31/2009 3:55:00 AM	8/31/2010 3:55:00 AM
    35	46	9/23/2009 12:16:00 PM	9/23/2010 12:16:00 PM
    36	4	8/31/2010 3:55:00 AM	8/31/2011 3:55:00 AM
    37	48	7/7/2010 11:26:00 PM	7/7/2011 11:26:00 PM
    38	62	7/8/2010 5:36:00 PM	7/8/2012 5:36:00 PM
    39	67	8/20/2010 6:44:00 PM	8/20/2011 6:44:00 PM
    40	21	2/14/2011 8:42:00 AM	2/14/2012 8:42:00 AM
    41	67	8/20/2011 6:44:00 PM	2/20/2012 6:44:00 PM
    42	67	2/20/2012 6:44:00 PM	7/20/2012 6:44:00 PM
    43	4	8/31/2011 3:55:00 AM	2/29/2012 3:55:00 AM
    44	25	2/18/2011 7:10:00 AM	2/18/2012 7:10:00 AM
    45	25	2/18/2012 7:10:00 AM	8/18/2012 7:10:00 AM
    46	66	8/1/2011 9:38:00 PM	8/1/2012 9:38:00 PM
    47	66	8/1/2012 9:38:00 PM	8/1/2013 9:38:00 PM
    48	1	2/18/2011 5:29:00 PM	2/18/2012 5:29:00 PM
    49	68	2/18/2011 6:35:00 PM	2/18/2012 6:35:00 PM
    50	69	2/19/2011 1:57:00 PM	2/19/2012 1:57:00 PM
    51	69	2/19/2012 1:57:00 PM	3/19/2012 1:57:00 PM

    Please help.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Is this a school task? We won't do your homework for you, but if you can show us what you have come up with so far, there will be plenty of people willing to help you further.

    The fastest way to solve this kind of questions, is by gradually fine-tuning the query. Start with a query that is easy to write. Verify that the results are correct.

    Once you have verified the correctness of the SQL query, you can start thinking of incorporating another part of the requirement. Take a copy of the previous query and make modifications on the copy. If you get struck, you can restart from the original query, that you have already proven to be correct.

    Sometimes you may have to combine multiple queries to get the required result.

    1. Number of subscription started, no previous subscription for the same cstID
    Possible steps
    - write a query that calculates the number of records per cstID
    - select only those cstID for whom the number of records is 1
    - Select only those records where the current date falls between prsStartDate and prsEndDate.
    - ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2011
    Posts
    12
    Tried to query this one:

    SELECT CUSTOMER_SUBSCRIPTIONS.*
    FROM CUSTOMER_SUBSCRIPTIONS
    WHERE CUSTOMER_SUBSCRIPTIONS.prsEndDate > getDate()
    ORDER BY CUSTOMER_SUBSCRIPTIONS.cstID

    I got this result:

    Code:
    prsID cstID prsStartDate             prsEndDate
    
    48    1    2011-02-18 17:29:00    2012-02-18 17:29:00
    43    4    2011-08-31 03:55:00    2012-02-29 03:55:00
    36    4    2010-08-31 03:55:00    2011-08-31 03:55:00
    40    21    2011-02-14 08:42:00    2012-02-14 08:42:00
    44    25    2011-02-18 07:10:00    2012-02-18 07:10:00
    45    25    2012-02-18 07:10:00    2012-08-18 07:10:00
    37    48    2010-07-07 23:26:00    2011-07-07 23:26:00
    25    59    2009-07-15 15:57:00    2011-07-15 15:57:00
    26    60    2009-07-16 04:54:00    2011-07-16 04:54:00
    38    62    2010-07-08 17:36:00    2012-07-08 17:36:00
    27    63    2009-07-16 05:42:00    2011-07-16 05:42:00
    28    65    2009-07-30 18:29:00    2011-07-30 18:29:00
    30    66    2009-08-01 21:38:00    2011-08-01 21:38:00
    46    66    2011-08-01 21:38:00    2012-08-01 21:38:00
    47    66    2012-08-01 21:38:00    2013-08-01 21:38:00
    39    67    2010-08-20 18:44:00    2011-08-20 18:44:00
    41    67    2011-08-20 18:44:00    2012-02-20 18:44:00
    42    67    2012-02-20 18:44:00    2012-07-20 18:44:00
    49    68    2011-02-18 18:35:00    2012-02-18 18:35:00
    50    69    2011-02-19 13:57:00    2012-02-19 13:57:00
    51    69    2012-02-19 13:57:00    2012-03-19 13:57:00

    Based on the result, it should be like this:

    Subscription started = 9

    Subscription reactivated = 5 because those are (cstIDs: 4, 25, 66, 67 and 69)



    Ignore those expired subscriptions, how I will query to achieve above results? Please help
    Last edited by neo_phyte; 02-21-11 at 09:03.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You are missing some?
    What about cstID 1 and 21
    there is already a previous subscription
    Code:
    prsID	cstID	prsStartDate		prsEndDate	
    2	1	1/18/2006 1:45:00 PM	1/18/2007 1:45:00 PM
    11	21	3/14/2006 11:56:00 AM	3/14/2007 11:56:00 AM

  5. #5
    Join Date
    Feb 2011
    Posts
    12
    those are expired subscriptions, we don't need that one... we will ignore that one.

    sorry, i did not explain this, that a customer can subscribe even if his subscription is not yet expire (so two active subscriptions for instance...). we don't care about expired subscription.
    Last edited by neo_phyte; 02-21-11 at 09:53.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Is this what you want?
    Code:
    select started=sum(case when cnt=1 then 1 end)
      ,reactivated=sum(case when cnt>1 then 1 end)
    from 
    (select cstID, cnt=count(*) 
     from CUSTOMER_SUBSCRIPTIONS 
     where prsEndDate>getdate() 
     group by cstID
    )view1

  7. #7
    Join Date
    Feb 2011
    Posts
    12
    problem solved.

  8. #8
    Join Date
    Feb 2011
    Posts
    12

    Smile

    Not yet solved....


    I have still problem, my solution is this...

    Code:
    SELECT
    SUM ( CASE WHEN FirstDate = LastDate AND EndDate > GETDATE () THEN 1 ELSE 0 END ) AS NewSubscriptions ,
    SUM ( CASE WHEN FirstDate < LastDate AND EndDate > GETDATE () THEN 1 ELSE 0 END ) AS ReactivatedSubscriptions
    FROM
    ( SELECT  
    MIN ( prsStartDate ) AS FirstDate ,
    MAX ( prsStartDate ) AS LastDate ,
    MAX ( prsEndDate ) AS EndDate  
    FROM  CUSTOMER_SUBSCRIPTIONS
    GROUP BY
    cstId ) Subscriptions ;
    but when I used BETWEEN, it display now then uncorrect results, please advised...

    Code:
    SELECT
    SUM ( CASE WHEN FirstDate = LastDate AND EndDate > GETDATE () THEN 1 ELSE 0 END ) AS NewSubscriptions ,
    SUM ( CASE WHEN FirstDate < LastDate AND EndDate > GETDATE () THEN 1 ELSE 0 END ) AS ReactivatedSubscriptions
    FROM
    ( SELECT  
    MIN ( prsStartDate ) AS FirstDate ,
    MAX ( prsStartDate ) AS LastDate ,
    MAX ( prsEndDate ) AS EndDate  
    FROM  CUSTOMER_SUBSCRIPTIONS
    WHERE CUSTOMER_SUBSCRIPTIONS . prsStartDate
    BETWEEN '1/1/2010 12:00:00 AM' AND '1/31/2011 11:59:59 PM'
    
    GROUP BY
    cstId ) Subscriptions ;

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by neo_phyte View Post
    but when I used BETWEEN, it display now then uncorrect results
    Let me see if I understand it.
    Your answer to use BETWEEN is incorrect and you would like us to provide the correct answer by guessing what the question is?

Posting Permissions

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