sql server - Working days SQL query -

i have problem can't head around.

i have table of events , table of users. events chargeable , not. want establish number chargeable days per week month , year every user.

here data...

id fromdate    todate       ischargeable     username 1  2014-11-03  2014-11-04        y           auser  2  2014-11-04  2014-11-06        y           auser 3  2014-11-07  2014-11-07        y           auser 

and i've written basic query calculate difference between , dates , sum them adding 1 total todate counts full day.

select distinct fromdate, todate, ischargeable,username, datediff(day, fromdate, todate) + 1) 'count1'            dbo.vdiary group fromdate, todate, ischargeable,username 

this results in...

      fromdate    todate       ischargeable     username   count1       2014-11-03  2014-11-04        y           auser      2       2014-11-04  2014-11-06        y           auser      3       2014-11-07  2014-11-07        y           auser      1 

which incorrect it's showing 6 working days because 2 events overlapping.

how can allow overlapping? need state if there event on particular day chargeable day chargeable day.

i think need calendar table i'm not sure on process of using right results.

any appreciated!!

thanks

steve

edit: trying solution below

        ;with cte      (select row_number()over(partition username order fromdate) rn,                 fromdate,todate,ischargeable,username            #calen) select a.fromdate,a.todate,a.ischargeable,a.username,        case          when a.fromdate > b.todate                or b.todate null datediff(day, a.fromdate, a.todate) + 1          else datediff(day, dateadd(dd, 1, b.todate), a.todate)+ 1        end datdiff,        *   cte        left join cte b               on a.rn = b.rn + 1                  , a.username = b.username  ( a.todate > b.todate           or b.todate null )  

produces (updated)

fromdate    todate  ischargeable    username    datdiff rn  fromdate    todate  ischargeable    username    rn  fromdate    todate  ischargeable    username 2014-11-03  2014-11-04  y   auser                2      1   2014-11-03  2014-11-04  y   auser   null    null    null    null    null 2014-11-04  2014-11-06  y   auser                2      2   2014-11-04  2014-11-06  y   auser   1   2014-11-03  2014-11-04  y   auser 2014-11-07  2014-11-07  y   auser                1      3   2014-11-07  2014-11-07  y   auser   2   2014-11-04  2014-11-06  y   auser 2014-12-03  2014-12-15  y   auser                13     4   2014-12-03  2014-12-15  y   auser   3   2014-11-07  2014-11-07  y   auser 2014-12-10  2014-12-17  y   auser                2      5   2014-12-10  2014-12-17  y   auser   4   2014-12-03  2014-12-15  y   auser 2015-12-04  2015-12-15  y   auser                12     6   2015-12-04  2015-12-15  y   auser   5   2014-12-10  2014-12-17  y   auser 2014-11-03  2014-11-03  y   buser                1      1   2014-11-03  2014-11-03  y   buser   null    null    null    null    null 2014-11-04  2014-11-04  y   buser                1      2   2014-11-04  2014-11-04  y   buser   1   2014-11-03  2014-11-03  y   buser 

try this.

if have unique id's try this.

create table #calen(id int, fromdate   date, todate date,ischargeable char(1),username varchar(20))  insert #calen values (1,'2014-11-03','2014-11-04','y ','auser'),        (2,'2014-11-04','2014-11-06','y','auser'),        (3,'2014-11-07','2014-11-07','y','auser')    select a.fromdate,a.todate,a.ischargeable,a.username,        case          when a.fromdate > b.todate                or b.todate null datediff(day, a.fromdate, a.todate) + 1          else datediff(day, dateadd(dd, 1, b.todate), a.todate)               + 1        end datdiff   #calen        left join #calen b               on a.id = b.id + 1  ( a.todate > b.todate           or b.todate null )  

update :

for more 1 user.

;with cte      (select row_number()over(partition username order fromdate) rn,                 fromdate,todate,ischargeable,username            #calen) select a.fromdate,a.todate,a.ischargeable,a.username,        case          when a.fromdate > b.todate                or b.todate null datediff(day, a.fromdate, a.todate) + 1          else datediff(day, dateadd(dd, 1, b.todate), a.todate)+ 1        end datdiff   cte        left join cte b               on a.rn = b.rn + 1                  , a.username = b.username  ( a.todate > b.todate           or b.todate null )  

output :

fromdate    todate      ischargeable    username    datdiff ----------  ----------  ------------    --------    ------- 2014-11-03  2014-11-04  y               auser           2 2014-11-04  2014-11-06  y               auser           2 2014-11-07  2014-11-07  y               auser           1