how to get a count.....complex query

[jannat]

Hi all,

I have a problem, i have table which has a effective date, termination date and client, I have to have a count for active people month by month from july 2003 till may 2004. the date fields are in datetime format, how would i do that?

Thanx in advance!

[MAK]

Question:

What column determines whether a client is active on a day. "Effective date" or "termination date"

Also Need sample data.

[skhanal]

Define active.

[jannat]

Both columns, for example if eff_date is <= '07/31/2003' and term_date is >= '07/01/2003' that person will be eligible for the month of July, 2003.

client eff_date term_date

AB 07/23/2003 07/31/2003
CD 07/23/2003 08/25/2003
EF 08/01/2003 08/30/2003

For the month of July only AB should be active, for Aug CD should be active and for Sep both CD and EF should be active, that's should be the logic.

Thanks.

[MAK]

Looks like termdate is the one that determines the "active"
Try this....

--drop table Clientactive
Create table Clientactive (Client varchar(10),
eff_date datetime,
term_date datetime)

insert into clientactive select 'AB', '07/23/2003', '07/31/2003'
insert into clientactive select 'CD', '07/23/2003', '08/25/2003'
insert into clientactive select 'EF' ,'08/01/2003' ,'08/30/2003'
insert into clientactive select 'GH' ,'09/01/2003' ,'09/30/2003'
insert into clientactive select 'IJ' ,'10/01/2003' ,'10/13/2003'
insert into clientactive select 'KL' ,'11/11/2003' ,'12/27/2003'
insert into clientactive select 'MN' ,'12/12/2003' ,'11/23/2003'
insert into clientactive select 'OP' ,'11/11/2003' ,'11/22/2003'
insert into clientactive select 'QR' ,'11/11/2003' ,'12/22/2003'
insert into clientactive select 'ST' ,'12/14/2003' ,'12/26/2003'
insert into clientactive select 'MN' ,'12/12/2003' ,'1/23/2004'
insert into clientactive select 'OP' ,'12/11/2003' ,'1/22/2004'
insert into clientactive select 'QR' ,'12/11/2003' ,'2/22/2004'
insert into clientactive select 'ST' ,'12/14/2003' ,'2/26/2004'


SELECT Year(term_date) as year,
CASE month(TERM_DATE) WHEN 1 THEN Client ELSE '' END AS Jan,
CASE month(TERM_DATE) WHEN 2 THEN Client ELSE '' END AS Feb,
CASE month(TERM_DATE) WHEN 3 THEN Client ELSE '' END AS Mar,
CASE month(TERM_DATE) WHEN 4 THEN Client ELSE '' END AS Apr,
CASE month(TERM_DATE) WHEN 5 THEN Client ELSE '' END AS May,
CASE month(TERM_DATE) WHEN 6 THEN Client ELSE '' END AS Jun,
CASE month(TERM_DATE) WHEN 7 THEN Client ELSE '' END AS Jul,
CASE month(TERM_DATE) WHEN 8 THEN Client ELSE '' END AS Aug,
CASE month(TERM_DATE) WHEN 9 THEN Client ELSE '' END AS Sep,
CASE month(TERM_DATE) WHEN 10 THEN Client ELSE '' END AS Oct,
CASE month(TERM_DATE) WHEN 11 THEN Client ELSE '' END AS Nov,
CASE month(TERM_DATE) WHEN 12 THEN Client ELSE '' END AS Dec
FROM clientactive

[jannat]

Thanks Mak.I changed the second part as following for my purpose and it works.


SELECT Year(term_date) as year,
CASE when month(eff_date) <= 1 and month(TERM_DATE) >= 1 then client ELSE '' END AS Jan,
case when month(eff_date) <=2 and month(TERM_DATE) >= 2 THEN Client ELSE '' END AS feb,
case when month(eff_date) <= 3 and month(TERM_DATE) >= 3 THEN Client ELSE '' END AS mar,
case when month(eff_date) <= 4 and month(TERM_DATE) >= 4 THEN Client ELSE '' END AS april,
case when month(eff_date) <= 5 and month(TERM_DATE) >= 5 THEN Client ELSE '' END AS may,
case when month(eff_date) <= 6 and month(TERM_DATE) >= 6 THEN Client ELSE '' END AS June,
case when month(eff_date) <= 7 and month(TERM_DATE) >= 7 THEN Client ELSE '' END AS July,
case when month(eff_date) <= 8 and month(TERM_DATE) >= 8 THEN Client ELSE '' END AS aug,
case when month(eff_date) <= 9 and month(TERM_DATE) >= 9 THEN Client ELSE '' END AS sep
FROM clientactive
order by 1

But there is one more problem, now I need to get a client count for each month, for example, number of records for the month of jan. Ant ideas?

I'm sure you do.

Thanks.

[jannat]

Thanks Mak.I changed the second part as following for my purpose and it works.


SELECT Year(term_date) as year,
CASE when month(eff_date) <= 1 and month(TERM_DATE) >= 1 then client ELSE '' END AS Jan,
case when month(eff_date) <=2 and month(TERM_DATE) >= 2 THEN Client ELSE '' END AS feb,
case when month(eff_date) <= 3 and month(TERM_DATE) >= 3 THEN Client ELSE '' END AS mar,
case when month(eff_date) <= 4 and month(TERM_DATE) >= 4 THEN Client ELSE '' END AS april,
case when month(eff_date) <= 5 and month(TERM_DATE) >= 5 THEN Client ELSE '' END AS may,
case when month(eff_date) <= 6 and month(TERM_DATE) >= 6 THEN Client ELSE '' END AS June,
case when month(eff_date) <= 7 and month(TERM_DATE) >= 7 THEN Client ELSE '' END AS July,
case when month(eff_date) <= 8 and month(TERM_DATE) >= 8 THEN Client ELSE '' END AS aug,
case when month(eff_date) <= 9 and month(TERM_DATE) >= 9 THEN Client ELSE '' END AS sep
FROM clientactive
order by 1

But there is one more problem, now I need to get a client count for each month, for example, number of records for the month of jan. Ant ideas?

I'm sure you do.

Thanks.

[MAK]

Select year,sum(Jan) as Jan,sum(feb) as Feb ,sum(mar) as Mar,
sum(apr) as Apr,sum(may) as May,sum(jun) as Jun, sum(jul) as Jul,
sum(aug) as Aug, sum(sep) as Sep, sum(oct) as Oct, sum(nov) as Nov,
sum(dec) as Dec from (
SELECT Year(term_date) as year,
CASE when month(eff_date) <= 1 and month(TERM_DATE) >= 1 then count(client) ELSE '' END AS Jan,
case when month(eff_date) <=2 and month(TERM_DATE) >= 2 THEN count(Client) ELSE '' END AS feb,
case when month(eff_date) <= 3 and month(TERM_DATE) >= 3 THEN count(Client) ELSE '' END AS mar,
case when month(eff_date) <= 4 and month(TERM_DATE) >= 4 THEN count(Client) ELSE '' END AS apr,
case when month(eff_date) <= 5 and month(TERM_DATE) >= 5 THEN count(Client) ELSE '' END AS may,
case when month(eff_date) <= 6 and month(TERM_DATE) >= 6 THEN count(Client) ELSE '' END AS Jun,
case when month(eff_date) <= 7 and month(TERM_DATE) >= 7 THEN count(Client) ELSE '' END AS Jul,
case when month(eff_date) <= 8 and month(TERM_DATE) >= 8 THEN count(Client) ELSE '' END AS aug,
case when month(eff_date) <= 9 and month(TERM_DATE) >= 9 THEN count(Client) ELSE '' END AS sep,
case when month(eff_date) <= 9 and month(TERM_DATE) >= 9 THEN count(Client) ELSE '' END AS oct,
case when month(eff_date) <= 9 and month(TERM_DATE) >= 9 THEN count(Client) ELSE '' END AS nov,
case when month(eff_date) <= 9 and month(TERM_DATE) >= 9 THEN count(Client) ELSE '' END AS dec
FROM clientactive group by eff_date,term_date,client ) as mytable
group by Year
order by 1

[jannat]

Thanks, that worked