count problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

count problem

Author

Topic

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-21 : 06:10:48

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.


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

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-21 : 08:00:39

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:


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

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-21 : 08:18:26

I am not quite sure what you are after, but a good start would be to produce test data in a form people can easily look at it:
(Including making sure dates are in ISO format.)


CREATE TABLE #t
(
	prsID int NOT NULL
	,cstID int NOT NULL
	,prsStartDate datetime NOT NULL
	,prsEndDate datetime NOT NULL
)
INSERt INTO #t
SELECT 2, 1, '20060118 13:45:00', '20070118 13:45:00'
UNION ALL SELECT 3, 2, '20060120 13:55:00', '20070120 13:55:00'
UNION ALL SELECT 4, 4, '20060228 10:42:00', '20070228 10:42:00'
-- etc

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-21 : 11:42:27

Try this:

CREATE TABLE #CUSTOMER_SUBSCRIPTIONS
(
prsID int NOT NULL
,cstID int NOT NULL
,prsStartDate datetime NOT NULL
,prsEndDate datetime NOT NULL
)
INSERt INTO #CUSTOMER_SUBSCRIPTIONS
select 48,1,'2011-02-18','2012-02-18' union all
select 43,4,'2011-08-31','2012-02-29' union all
select 36,4,'2010-08-31','2011-08-31' union all
select 40,21,'2011-02-14','2012-02-14' union all
select 44,25,'2011-02-18','2012-02-18' union all
select 45,25,'2012-02-18','2012-08-18' union all
select 37,48,'2010-07-07','2011-07-07' union all
select 25,59,'2009-07-15','2011-07-15' union all
select 26,60,'2009-07-16','2011-07-16' union all
select 38,62,'2010-07-08','2012-07-08' union all
select 27,63,'2009-07-16','2011-07-16' union all
select 28,65,'2009-07-30','2011-07-30' union all
select 30,66,'2009-08-01','2011-08-01' union all
select 46,66,'2011-08-01','2012-08-01' union all
select 47,66,'2012-08-01','2013-08-01' union all
select 39,67,'2010-08-20','2011-08-20' union all
select 41,67,'2011-08-20','2012-02-20' union all
select 42,67,'2012-02-20','2012-07-20' union all
select 49,68,'2011-02-18','2012-02-18' union all
select 50,69,'2011-02-19','2012-02-19' union all
select 51,69,'2012-02-19','2012-03-19'

Select CS.*, Case when Subtab.Rcnt=1 then 'New' else 'ReActivated' end as Status
from #CUSTOMER_SUBSCRIPTIONS CS
Inner join
(
Select Count(1) as RCnt, Max(prsid) prsid from #CUSTOMER_SUBSCRIPTIONS
group by CSTID
) as subtab
on CS.Prsid = Subtab.Prsid

Regards,
Bohra

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-21 : 16:23:14

problem solved...

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-21 : 18:17:46

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


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...


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 ;

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-21 : 23:05:01

It gives four records and as per data it is correct.
Can you tell us what the output you are expecting (how many records and the prsid ).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources