Finding different status counts from one table

Hi There,
I know this should be straight forward, but i can't seem to get the correct results.

I want to find out the #of Active Customers
and the # of Cancelled Customers during a certain time period.

now if someone decides to choose the dates for 3 months ago i want to see who had a status of Active and who had a status of Cancelled at that time.

The ACCOUNT TABLE holds what a customer currently has
The ACCOUNT STATUS HISTORY TABLE holds all the status a customer whold have gone through

here is the code for the tables


/****** Object:  Table [dbo].[account]    Script Date: 11/16/2007 13:57:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[account](
      [account_id] [int] NOT NULL,
      [product_id] [smallint] NOT NULL,
      [customer_id] [int] NOT NULL,
      [account_status_id] [smallint] NOT NULL,
      [account_name] [varchar](75) NOT NULL,
      [username] [varchar](64) NULL,
      [password] [varchar](64) NULL,
      [date_entered] [datetime] NOT NULL,
      [entered_by] [smallint] NOT NULL,
      [setup] [decimal](10, 2) NOT NULL,
      [recurring] [decimal](10, 2) NOT NULL,
      [usage_fee] [decimal](10, 2) NOT NULL,
      [bill_period_id] [int] NOT NULL,
      [parent_account_id] [bigint] NULL,
      [billing_start_date] [datetime] NULL,
      [renewal_date] [datetime] NULL,
      [region_id] [smallint] NOT NULL,
      [original_product_id] [smallint] NULL,
      [package_id] [smallint] NULL,
      [original_account_id] [int] NULL,
      [one_time_billed] [smallint] NOT NULL,
      [account_status_date] [datetime] NOT NULL,
      [agency_id] [smallint] NOT NULL,
      [lead_id] [smallint] NULL,
      [sales_rep_id] [smallint] NULL,
      [provisioner_id] [smallint] NULL,
      [note] [varchar](255) NULL,
      [radius_profile_id] [int] NOT NULL,
      [cancel_status_id] [int] NULL,
      [status_reason_id] [int] NULL,
      [status_reason_description] [text] NOT NULL,
      [domain_id] [int] NOT NULL,
      [provider_id] [smallint] NOT NULL,
      [brand_id] [smallint] NOT NULL,
      [location_id] [bigint] NULL,
      [shipping_location_id] [bigint] NULL,
      [jurisdiction_id] [int] NULL,
      [billable_number_access] [smallint] NULL,
      [package_template_product_id] [int] NULL,
      [shipping_fee] [int] NULL,
      [promotion_code] [int] NULL,
      [promotion_code_original] [int] NULL,
      [free_billing_periods] [int] NULL,
      [free_billing_periods_orig] [int] NULL,
      [bundle_id] [int] NULL,
      [source_id] [int] NULL,
      [offer_id] [int] NULL,
      [terms_date] [datetime] NULL,
      [account_type_id] [int] NULL,
      [service_credits] [int] NULL,
      [renewal_offer_set_id] [int] NULL,
      [offer_set_id] [int] NULL,
      [discount_id] [int] NULL,
      [tower_id] [int] NULL,
      [sector_id] [int] NULL,
      [renewal_promotion_code] [varchar](100) NULL,
      [renewal_offer_id] [int] NULL,
      [offer_end_date] [datetime] NULL,
      [offer_start_date] [datetime] NULL,
      [offer_notified_date] [datetime] NULL,
      [dealer_id] [int] NULL,
      [future_cancel_date] [datetime] NULL,
      [tax_rate] [varchar](100) NULL,
      [equipment_activation_date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF


/****** Object:  Table [dbo].[account_status_history]    Script Date: 11/16/2007 14:03:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[account_status_history](
      [account_status_history_id] [int] NOT NULL,
      [account_id] [int] NOT NULL,
      [account_status_id] [smallint] NOT NULL,
      [account_status_date] [datetime] NOT NULL,
      [entered_by] [smallint] NOT NULL,
      [report_exclude] [smallint] NULL
) ON [PRIMARY]

--INSERTS
insert into account_status_history values (301341,32,3,'2007-03-21 12:45:41.000')
insert into account_status_history values (406064,55,3,'2007-08-22 02:00:11.000')
insert into account_status_history values  (1,19,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (2,32,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (3,39,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (4,47,3,'2006-04-29 12:48:37.000')
insert into account_status_history values  (5,55,1,'2006-04-29 12:48:37.000')

insert into account values (19,1,'2004-05-26 00:00:00.000','Bizquip Limited (Reseller Account) (19)');
insert into account values (32,3,'2004-09-01 00:00:00.000','Campbell (32)');
insert into account values (39,1,'2005-11-07 00:00:00.000','Clearview Signs Ltd (39)');
insert into account values (47,3,'2005-03-03 00:00:00.000','Digital Switch Systems (DSS) (47)');
insert into account values (55,3,'2004-09-23 00:00:00.000','Entropy Limited (55)');

Status id
 3 = Cancelled
 1 = Active

So if i run the report using the account_status_history date for 2006-04-29 to 2006-05-30
I would hoping to see back 1 Cancelled customer and 4 Active Customers

However when i try to do this i can not get back teh results i want: this is what i;ve tried:
I've taken out the Cancelled part as this does not work, you can see the active count does.
How can i get a Count of Active and a count of Cancelled at this time , Please Advise.

DECLARE @FROMDATE datetime,@todate datetime
SET @FROMDATE = '2006-04-28'
SET @TODATE = '2006-05-30'

SELECT count(sub1.account_id)Active_cnt

 FROM
ACCOUNT A

--Active Accounts
LEFT JOIN (SELECT max(account_status_history_id)maxid, account_id
                  FROM account_status_history where account_status_id = 1
                           Group by account_id )sub1 on sub1.account_id = a.account_id
LEFT JOIN account_status_history ash on ash.account_status_history_id = sub1.maxid

----Cancelled Accounts
--LEFT JOIN (SELECT max(account_status_history_id)maxid2, account_id
--                  FROM account_Status_history where account_status_id = 3
--                  Group by account_id)sub2 on sub2.account_id = a.account_id
--LEFT JOIN account_status_history ash1 on ash1.account_status_history_id =  sub2.maxid2

Where  ash.account_status_date between @fromdate and @todate
-- and ash1.account_status_date between @fromdate and @todate


Thanks,
Putoch
/****** Object:  Table [dbo].[account]    Script Date: 11/16/2007 13:57:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[account](
	[account_id] [int] NOT NULL,
	[product_id] [smallint] NOT NULL,
	[customer_id] [int] NOT NULL,
	[account_status_id] [smallint] NOT NULL,
	[account_name] [varchar](75) NOT NULL,
	[username] [varchar](64) NULL,
	[password] [varchar](64) NULL,
	[date_entered] [datetime] NOT NULL,
	[entered_by] [smallint] NOT NULL,
	[setup] [decimal](10, 2) NOT NULL,
	[recurring] [decimal](10, 2) NOT NULL,
	[usage_fee] [decimal](10, 2) NOT NULL,
	[bill_period_id] [int] NOT NULL,
	[parent_account_id] [bigint] NULL,
	[billing_start_date] [datetime] NULL,
	[renewal_date] [datetime] NULL,
	[region_id] [smallint] NOT NULL,
	[original_product_id] [smallint] NULL,
	[package_id] [smallint] NULL,
	[original_account_id] [int] NULL,
	[one_time_billed] [smallint] NOT NULL,
	[account_status_date] [datetime] NOT NULL,
	[agency_id] [smallint] NOT NULL,
	[lead_id] [smallint] NULL,
	[sales_rep_id] [smallint] NULL,
	[provisioner_id] [smallint] NULL,
	[note] [varchar](255) NULL,
	[radius_profile_id] [int] NOT NULL,
	[cancel_status_id] [int] NULL,
	[status_reason_id] [int] NULL,
	[status_reason_description] [text] NOT NULL,
	[domain_id] [int] NOT NULL,
	[provider_id] [smallint] NOT NULL,
	[brand_id] [smallint] NOT NULL,
	[location_id] [bigint] NULL,
	[shipping_location_id] [bigint] NULL,
	[jurisdiction_id] [int] NULL,
	[billable_number_access] [smallint] NULL,
	[package_template_product_id] [int] NULL,
	[shipping_fee] [int] NULL,
	[promotion_code] [int] NULL,
	[promotion_code_original] [int] NULL,
	[free_billing_periods] [int] NULL,
	[free_billing_periods_orig] [int] NULL,
	[bundle_id] [int] NULL,
	[source_id] [int] NULL,
	[offer_id] [int] NULL,
	[terms_date] [datetime] NULL,
	[account_type_id] [int] NULL,
	[service_credits] [int] NULL,
	[renewal_offer_set_id] [int] NULL,
	[offer_set_id] [int] NULL,
	[discount_id] [int] NULL,
	[tower_id] [int] NULL,
	[sector_id] [int] NULL,
	[renewal_promotion_code] [varchar](100) NULL,
	[renewal_offer_id] [int] NULL,
	[offer_end_date] [datetime] NULL,
	[offer_start_date] [datetime] NULL,
	[offer_notified_date] [datetime] NULL,
	[dealer_id] [int] NULL,
	[future_cancel_date] [datetime] NULL,
	[tax_rate] [varchar](100) NULL,
	[equipment_activation_date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
 
 
/****** Object:  Table [dbo].[account_status_history]    Script Date: 11/16/2007 14:03:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[account_status_history](
	[account_status_history_id] [int] NOT NULL,
	[account_id] [int] NOT NULL,
	[account_status_id] [smallint] NOT NULL,
	[account_status_date] [datetime] NOT NULL,
	[entered_by] [smallint] NOT NULL,
	[report_exclude] [smallint] NULL
) ON [PRIMARY]
 
--INSERTS
insert into account_status_history values (301341,32,3,'2007-03-21 12:45:41.000')
insert into account_status_history values (406064,55,3,'2007-08-22 02:00:11.000')
insert into account_status_history values  (1,19,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (2,32,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (3,39,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (4,47,3,'2006-04-29 12:48:37.000')
insert into account_status_history values  (5,55,1,'2006-04-29 12:48:37.000')
 
insert into account values (19,1,'2004-05-26 00:00:00.000','Bizquip Limited (Reseller Account) (19)');
insert into account values (32,3,'2004-09-01 00:00:00.000','Campbell (32)');
insert into account values (39,1,'2005-11-07 00:00:00.000','Clearview Signs Ltd (39)');
insert into account values (47,3,'2005-03-03 00:00:00.000','Digital Switch Systems (DSS) (47)');
insert into account values (55,3,'2004-09-23 00:00:00.000','Entropy Limited (55)');
 
 
 
DECLARE @FROMDATE datetime,@todate datetime
SET @FROMDATE = '2006-04-28'
SET @TODATE = '2006-05-30'
 
SELECT count(sub1.account_id)Active_cnt
 
 FROM 
ACCOUNT A 
 
--Active Accounts
LEFT JOIN (SELECT max(account_status_history_id)maxid, account_id
			FROM account_status_history where account_status_id = 1 
		   		Group by account_id )sub1 on sub1.account_id = a.account_id 
LEFT JOIN account_status_history ash on ash.account_status_history_id = sub1.maxid
 
----Cancelled Accounts
--LEFT JOIN (SELECT max(account_status_history_id)maxid2, account_id
--			FROM account_Status_history where account_status_id = 3
--			Group by account_id)sub2 on sub2.account_id = a.account_id 
--LEFT JOIN account_status_history ash1 on ash1.account_status_history_id =  sub2.maxid2
 
Where  ash.account_status_date between @fromdate and @todate
-- and ash1.account_status_date between @fromdate and @todate

Open in new window

PutochAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Okay, if you want the number of accounts that cancelled during the month or timeframe, and the number of accounts that went active in that time, then fine. I've added a line which uses from date.

If a new account was added during the month, and then cancelled, it will be counted in the cancels, but not in the active, as the query is looking at the last entry in the history table.

Cheers
  David
declare @FromDate datetime, @ToDate datetime
set @FromDate = '2006-04-28'
set @ToDate = '2006-05-30'
 
select
	count( A.account_id ) as TotalCount
	, sum(
		case ash.account_status_id
			when 1 then 1 -- Active
			else 0
		end
	) as Active_cnt
	, sum(
		case ash.account_status_id
			when 2 then 1 -- Not sure, I'm guessing about the  Cancelled status
			else 0
		end
	) as Cancelled_cnt
from dbo.account A
left join
	(
	select
		max( ashi.account_status_history_id ) as maxid
		, ashi.account_id
    from dbo.account_status_history ashi
	where
		ashi.account_status_date <= @ToDate
		-- Added Line Here
		and ashi.account_status_date >= @FromDate
    group by ashi.account_id
	) sub1 on sub1.account_id = a.account_id
left join dbo.account_status_history ash
	on ash.account_status_history_id = sub1.maxid
 

Open in new window

0
 
CCongdonCommented:
First things first, those inserts are not valid for the table create scripts you have supplied.
0
 
PutochAuthor Commented:
i would prefare to get this working without using a union like the following:

DECLARE @FROMDATE datetime,@todate datetime
SET @FROMDATE = '2006-04-28'
SET @TODATE = '2006-05-30'

SELECT count(sub1.account_id),'Active_cnt'

 FROM
ACCOUNT A

--Active Accounts
LEFT JOIN (SELECT max(account_status_history_id)maxid, account_id
                  FROM account_status_history where account_status_id = 1
                           Group by account_id )sub1 on sub1.account_id = a.account_id
LEFT JOIN account_status_history ash on ash.account_status_history_id = sub1.maxid

Where  ash.account_status_date between @fromdate and @todate


UNION


SELECT count(sub2.account_id),'Cancelled_cnt'

 FROM
ACCOUNT A


--Cancelled Accounts
LEFT JOIN (SELECT max(account_status_history_id)maxid2, account_id
                  FROM account_Status_history where account_status_id = 3
                  Group by account_id)sub2 on sub2.account_id = a.account_id
LEFT JOIN account_status_history ash1 on ash1.account_status_history_id =  sub2.maxid2

Where   ash1.account_status_date between @fromdate and @todate

This is because i want to use a martix  in Visual Studio.
And this report is only part of a much larger report so i don't want to be adding on unions or anything that could potentially slow down the run time of the report if possiable.

Thanks,
Putoch.



0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
PutochAuthor Commented:
Sorry CCongdon:, i don't see what is wrong with the inserts? what error are you getting, i am useing a SQL Server Database with T-sql and thsi works perfect for me?
0
 
CCongdonCommented:
I'm using Microsft SQL Server Management Studio (NOT the express version) on MS-SQL Server 2005 Standard.

I'm getting an error that says the insert does not match the table definition. Which makes sense. The script to build the table contains a number of NOT NULL fields. More NOT NULL fields than are contained in the inserts. There are also more fields contained in the tables than are even in the inserts and since the inserts aren't declaring any fields, this is going to cause a problem as well. For instance, I added a ,1,1 to the end of the inserts for the history table and that allowed those scripts to execute without column definitions because the insert now has the same number of columns as the table. Try setting up a clean database on your server and then executing your scripts on that clean DB and see what you get.
0
 
PutochAuthor Commented:
Excuse me i changed the tables as there are so many rows in my production table and then i poseted the create statement for the production table Sorry.

The create tables statements are:
/****** Object:  Table [dbo].[account]    Script Date: 11/16/2007 15:08:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[account](
	[account_id] [int] NOT NULL,
	[account_status_id] [smallint] NOT NULL,
	[date_entered] [datetime] NOT NULL,
	[account_name] [varchar](75) NOT NULL
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
 
 
 
/****** Object:  Table [dbo].[account_status_history]    Script Date: 11/16/2007 15:09:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[account_status_history](
	[account_status_history_id] [int] NOT NULL,
	[account_id] [int] NOT NULL,
	[account_status_id] [smallint] NOT NULL,
	[account_status_date] [datetime] NOT NULL
) ON [PRIMARY]
 
 
--INSERTS
insert into account_status_history values (301341,32,3,'2007-03-21 12:45:41.000')
insert into account_status_history values (406064,55,3,'2007-08-22 02:00:11.000')
insert into account_status_history values  (1,19,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (2,32,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (3,39,1,'2006-04-29 12:48:37.000')
insert into account_status_history values  (4,47,3,'2006-04-29 12:48:37.000')
insert into account_status_history values  (5,55,1,'2006-04-29 12:48:37.000')
 
insert into account values (19,1,'2004-05-26 00:00:00.000','Bizquip Limited (Reseller Account) (19)');
insert into account values (32,3,'2004-09-01 00:00:00.000','Campbell (32)');
insert into account values (39,1,'2005-11-07 00:00:00.000','Clearview Signs Ltd (39)');
insert into account values (47,3,'2005-03-03 00:00:00.000','Digital Switch Systems (DSS) (47)');
insert into account values (55,3,'2004-09-23 00:00:00.000','Entropy Limited (55)');
 

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

I've reworked your query for the active accounts. I think it had a flaw or two and that you were lucky it worked.

The final where clause wasn't needed.

The @FromDate - @ToDate isn't confusing. The @FromDate isn't needed, just an @AsAtDate, for which I've used the @ToDate.

Your active Accounts sub-query I've changed slightly to be just the last status history prior to the @AsAtDate - @ToDate in your query.

I've used sum on a case to provide the Active and Cancelled counts.

HTH
  David
DECLARE @FROMDATE datetime,@todate datetime
SET @FROMDATE = '2006-04-28' -- unnecessary imho
SET @TODATE = '2006-05-30'
 
SELECT 
	count( A.account_id ) as TotalCount
	, sum(
		case ash.account_status_id
			when 1 then 1 -- Active
			else 0
		end
	) Active_cnt
	, sum(
		case ash.account_status_id
			when 2 then 1 -- Not sure, I'm guessing about the  Cancelled status
			else 0
		end
	) Cancelled_cnt
 
FROM dbo.ACCOUNT A
 
--Active Accounts   -- Last Account status prior to date
LEFT JOIN 
	(
	SELECT max(account_status_history_id)maxid, account_id
    FROM account_status_history 
	where 
		--account_status_id = 1 -- wrong place for this test
		--and
		account_status_date <= @ToDate
    Group by account_id 
	)sub1 on sub1.account_id = a.account_id
LEFT JOIN account_status_history ash 
	on ash.account_status_history_id = sub1.maxid
 
----Cancelled Accounts
--LEFT JOIN (SELECT max(account_status_history_id)maxid2, account_id
--                  FROM account_Status_history where account_status_id = 3
--                  Group by account_id)sub2 on sub2.account_id = a.account_id
--LEFT JOIN account_status_history ash1 on ash1.account_status_history_id =  sub2.maxid2
 
-- where clause not needed imho
--Where  ash.account_status_date between @fromdate and @todate
-- and ash1.account_status_date between @fromdate and @todate

Open in new window

0
 
PutochAuthor Commented:
Thank you dtodd, that is great, however i would like to be able to see for example how many people cancelled and were active during this month and this is whey i was using the two dates?
If i only use the AsAtDate i will get from the begining of the Product Going Live to the Date chosen Rather then The chosen being date to the end chosen date.
Would you be able to advise me on integrating the second date please?

Thank you,
Putoch.
0
 
PutochAuthor Commented:
Thank you for your help!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.