filter by date 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 2008 Forums

Transact-SQL (2008)

filter by date problem?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-05-16 : 16:54:55

Hi

I am having a strange problem as below

I have table in the following way:
Customer_no Purchase_Status StaTus_date
1 yes 09/09/2012
2
3 yes 10/10/2012
4 yes 09/23/2012

From The above scenario

I want to calculate Percent for total number of customers whose purchase status is 'Yes' for the month of September to the total number of customers
w hich is 2/4 but when i pass the Status_date between '09/01/2012' and '09/30/2012'

I am only getting 2/2..How cna i achive it for 2/4..Please help...

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-05-16 : 17:00:23

Something like this will give you both numbers:

select count(*), (select TotalCustomers = COUNT(DISTINCT Customer_no) from thetable)
from thetable
where StaTus_date between '09/01/2012' and '09/30/2012' and Purchase_Status = 'yes'

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-05-16 : 17:54:44

quote:
Originally posted by influent

Something like this will give you both numbers:

select count(*), (select TotalCustomers = COUNT(DISTINCT Customer_no) from thetable)
from thetable
where StaTus_date between '09/01/2012' and '09/30/2012' and Purchase_Status = 'yes'

Thank You influent..it worked..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 20:26:21

isnt this enough?


select sum(case when StaTus_date between '09/01/2012' and '09/30/2012' then 1 else 0 end) * 1.0/count(*) as percentagecount
from thetable
where Purchase_Status = 'yes'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources