# Counting days for the month

Given the following rows

ID       date             active
1        8/1/2011      1
1        8/10/2011    0
1        8/20/2011    1

i want to write a query that will count the number of days id=1 is active

ie

1-10 = 10
20-31 = 12

Total: 22

Allan
###### Who is Participating?

x

Commented:
and actually to limit it to a specific month
``````;with cte as (
from (select * from yourtable where active = 1 where [date] between '2011-08-01' and '2011-08-31') a
cross outer apply (select id, min([date]) as nextdate from yourtable where active = 0 and id = a.id and [date] > a.[date]) b
), cte2 as (
select id, [date], enddate, datediff(d, [date], enddate) as ndays
from cte
)
select id, sum(ndays)
from cte2
where id = 1
``````
0

Commented:
SELECT COUNT(*)
FROM TABLENAME
WHERE ID = 1 AND ACTIVE = 1;
0

Author Commented:
BusyMama Thanks for the reply, but that wont work your query would only give me 2.

there are only three rows here there isnt a row for every day.

the query need to count that from 8/1 - 8/10 that is was active and then from 8/21 - 8/31 it was also active for a total of 22

Allan
0

Commented:
try something like this

``````;with cte as (
from (select * from yourtable where active = 1) a
cross outer apply (select id, min([date]) as nextdate from yourtable where active = 0 and id = a.id and [date] > a.[date]) b
), cte2 as (
select id, [date], enddate, datediff(d, [date], enddate) as ndays
from cte
)
select id, sum(ndays)
from cte2
where id = 1
``````
0

Commented:
show some proper data...

cross a month boundary... can you have

8/20/2011   1
9/04/2011   0

?
0

Author Commented:
ralmada thx but this only gives me 9, from 8/1-8/10

I would need to add another row

select 1, '9/1/2011' 0

to my dataset to get the correct value which is not a big deal.
``````; WITH data AS
(
SELECT 1 AS id, '8/1/2011' AS [date], 1 AS active UNION ALL
SELECT 1, '8/10/2011', 0 UNION ALL
SELECT 1, '8/20/2011', 1
), cte as (
select a.id, a.[date], isnull(b.nextdate, dateadd(m, datediff(m, 0, a.[date])+1, 0)-1) as enddate
from (select * from data where active = 1) a
cross apply (select id, min([date]) as nextdate from data where active = 0 and id = a.id and [date] > a.[date] GROUP BY id) b
), cte2 as (
select id, [date], enddate, datediff(d, [date], enddate) as ndays
from cte
)

select id, sum(ndays)
from cte2
where id = 1
GROUP BY id
``````
0

Author Commented:

this is really only specific to one month.
0

Commented:
Here's the corrected version to give you the extra day
``````declare @t table (
ID       int,
[date] date,
active int
)

insert @t values(1,        '8/1/2011',      1),
(1,        '8/10/2011',    0),
(1,        '8/20/2011',    1)

select * from @t

;with cte as (
select a.id, a.[date], isnull(b.nextdate, dateadd(m, datediff(m, 0, a.[date])+1, 0)-1) as enddate
from (select * from @t where active = 1 and [date] between '2011-08-01' and '2011-08-31') a
outer apply (select id, min([date]) as nextdate from @t where active = 0 and id = a.id and [date] > a.[date] group by id) b
), cte2 as (
select id, [date], enddate, datediff(d, [date], enddate)+1 as ndays
from cte
)
select id, sum(ndays)
from cte2
where id = 1
group by id
``````
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.