Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > count problem

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-21-11, 06:17
neo_phyte neo_phyte is offline
Registered User
  
Join Date: Feb 2011
Posts: 12
Smile 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
Reply With Quote
  #2 (permalink)  
Old 02-21-11, 07:08
Wim Wim is offline
Registered User
  
Join Date: Nov 2004
Posts: 1,279
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.

Quote:
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/2008/2008 R2 Earned beers: 16
Wim
Beware of bugs in the above code; I have only proved it correct, not tried it. -- Donald Knuth
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
Reply With Quote
  #3 (permalink)  
Old 02-21-11, 07:26
neo_phyte neo_phyte is offline
Registered User
  
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 08:03.
Reply With Quote
  #4 (permalink)  
Old 02-21-11, 08:48
pdreyer pdreyer is offline
Registered User
  
Join Date: May 2005
Location: South Africa
Posts: 1,268
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
Reply With Quote
  #5 (permalink)  
Old 02-21-11, 08:50
neo_phyte neo_phyte is offline
Registered User
  
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 08:53.
Reply With Quote
  #6 (permalink)  
Old 02-21-11, 09:09
pdreyer pdreyer is offline
Registered User
  
Join Date: May 2005
Location: South Africa
Posts: 1,268
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
Reply With Quote
  #7 (permalink)  
Old 02-21-11, 16:24
neo_phyte neo_phyte is offline
Registered User
  
Join Date: Feb 2011
Posts: 12
problem solved.
Reply With Quote
  #8 (permalink)  
Old 02-21-11, 18:19
neo_phyte neo_phyte is offline
Registered User
  
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 ;
Reply With Quote
  #9 (permalink)  
Old 02-22-11, 08:12
pdreyer pdreyer is offline
Registered User
  
Join Date: May 2005
Location: South Africa
Posts: 1,268
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?
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.