Counting clients on first day of month adhoc

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

General SQL Server Forums

New to SQL Server Programming

Counting clients on first day of month adhoc

Author

Topic

Ulli2015
Starting Member

2 Posts

Posted - 2015-04-17 : 15:40:38

I am trying to write a query that counts how many clients were part of program at the first of each month.

To make it simple, the data comes out of one table which looks like this:

Client # Program Start_date End_date
1 Fruit eater 03-27-2014 01-10-2015
2 Veggi eater 01-16-2015 null
3 Veggi eater 12-05-2013 04-16-2015
4 Fruit eater 10-01-2014 11-30-2014

Currently I have a very ugly solution that I know is not the best one:

Select
sum(convert(int,Jan_2014))as Tiers_in_Jan_2014
, sum(convert(int,Feb_2014)) as Tiers_in_Feb_2014
, sum(convert(int,Mar_2014 )) as Tiers_in_Mar_2014
, sum(convert(int,Apr_2014 ))as Tiers_in_Apr_2014
From --b
(
Select *
, case when '01-01-2014' between start_date and end_date then '1' when '01-01-2014' not between start_date and end_date then '0' end 'Jan_2014'
, case when '02-01-2014' between start_date and end_date then '1' when '02-01-2014' not between start_date and end_date then '0' end 'Feb_2014'
, case when '03-01-2014' between start_date and end_date then '1' when '03-01-2014' not between start_date and end_date then '0' end 'Mar_2014'
, case when '04-01-2014' between start_date and end_date then '1' when '04-01-2014' not between start_date and end_date then '0' end 'Apr_2014'
From --a
(select client_ID
, start_date
, end_date
from client_Program
) a
)b group by Jan_2014

Is there a better way to write this query?

Thanks!
Ulli2015

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-17 : 16:22:18

Run this to see if it gives you close to what you are looking for. May need some work.

DECLARE 
	@startDate DATE = '20140101',
	@endDate DATE = '20151231';
		
SELECT
	DATEADD(mm,Number,@StartDate) AS [Date],
	SUM( 
		CASE WHEN 
			Start_date <= DATEADD(mm, s.number, @startDate) AND
			ISNULL(End_date, '20991231') >= DATEADD(mm, s.number, @startDate) THEN 1 ELSE 0
		END
	)
FROM
	client_Program c
	CROSS APPLY
	(
		SELECT Number FROM master..spt_values s
		WHERE DATEADD(mm,Number,@StartDate) <= @endDate
		AND s.TYPE = 'P'
	) s
GROUP BY
	Number
ORDER BY
	[Date];

Ulli2015
Starting Member

2 Posts

Posted - 2015-04-20 : 11:49:54

I tried using the code ( played around quite a bit, but it doesn't sum up the client's correctly. However the format of the output is exactly how I would like it to be:
Date Sum of clients

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-20 : 13:34:59

For the sample data in your original posting, what is the output that you should be getting?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources