Solved

Finding different status counts from one table

Posted on 2007-11-16
9
166 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:Putoch
  • 5
  • 2
  • 2
9 Comments
 
LVL 9

Expert Comment

by:CCongdon
Comment Utility
First things first, those inserts are not valid for the table create scripts you have supplied.
0
 

Author Comment

by:Putoch
Comment Utility
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
 

Author Comment

by:Putoch
Comment Utility
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
 
LVL 9

Expert Comment

by:CCongdon
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Putoch
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
 

Author Comment

by:Putoch
Comment Utility
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
 
LVL 35

Accepted Solution

by:
David Todd earned 125 total points
Comment Utility
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
 

Author Closing Comment

by:Putoch
Comment Utility
Thank you for your help!!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now