SQL Server : count types with totals by date change -
i need count value (m_id
) @ each change of date (rs_date
) , create column grouped rs_date
has active total date.
so table is:
ep_id oa_id m_id m_startdate rs_date -------------------------------------------- 1 2001 5 1/1/2014 1/1/2014 1 2001 9 1/1/2014 1/1/2014 1 2001 3 1/1/2014 1/1/2014 1 2001 11 1/1/2014 1/1/2014 1 2001 2 1/1/2014 1/1/2014 1 2067 7 1/1/2014 1/5/2014 1 2067 1 1/1/2014 1/5/2014 1 3099 12 1/1/2014 3/2/2014 1 3099 14 2/14/2014 3/2/2014 1 3099 4 2/14/2014 3/2/2014
so goal
rs_date active ----------------- 1/1/2014 5 1/5/2014 7 3/2/2014 10
if m_startdate = rs_date
need count m_id
, each rs_date
not equal start date need count m_id
, add m_startdate
count , count next rs_date
, add last active count.
i can basic counts
(case when m_startdate <= rs_date [m_id] end) test.
but stuck how result want.
any appreciated.
brian
-added in response comments using server ver 10
if using sql server 2012+ can use rows
analytic/window functions:
;with cte (select rs_date ,count(distinct m_id) ct table1 group rs_date ) select *,sum(ct) over(order rs_date rows between unbounded preceding , current row) run_ct cte
demo: sql fiddle
if stuck using prior 2012 can use:
;with cte (select rs_date ,count(distinct m_id) ct table1 group rs_date ) select a.rs_date ,sum(b.ct) cte left join cte b on a.rs_date >= b.rs_date group a.rs_date
demo: sql fiddle
Comments
Post a Comment