sql - Find range of dates within same column -

i have data set, looks this:

 resourceid           requirementid        projectid            startdate               enddate                 billingpercentage -------------------- -------------------- -------------------- ----------------------- ----------------------- --------------------------------------- 1                    5066                 7505                 2015-09-15 00:00:00.000 2015-09-30 00:00:00.000 50 2                    4748                 7499                 2015-09-10 00:00:00.000 2015-09-20 00:00:00.000 50 

i want calculate range , corresponding billing % particular month query is:

     insert @datetimeline     select @monthstartdate ostartdate,@monthenddate oenddate,0       insert @datetimeline     select startdate ostartdate,enddate oenddate,billingpercentage @resource_unbilled order startdate      insert @datetimeline     select enddate ostartdate,enddate oenddate,billingpercentage  @resource_unbilled order startdate 

and data looks following:

 serialno    ostartdate              oenddate                billingpercentage ----------- ----------------------- ----------------------- --------------------------------------- 1           2015-09-01 00:00:00.000 2015-09-30 00:00:00.000          0 2           2015-09-10 00:00:00.000 2015-09-20 00:00:00.000         50 3           2015-09-15 00:00:00.000 2015-09-30 00:00:00.000         50 4           2015-09-20 00:00:00.000 2015-09-20 00:00:00.000         50 5           2015-09-30 00:00:00.000 2015-09-30 00:00:00.000         50

i want retrive data following

    ostartdate              oenddate                billingpercentage ----------- ----------------------- ----------------------- --------------------------------------- 2015-09-01 00:00:00.000 2015-09-10 00:00:00.000               0 2015-09-10 00:00:00.000 2015-09-15 00:00:00.000              50 2015-09-15 00:00:00.000 2015-09-20 00:00:00.000             100 2015-09-20 00:00:00.000 2015-09-30 00:00:00.000              50 

please suggest how can can use pivot here?

use table variable store @datestamps columns: serialno, ostartdate , oenddate.

try query:

select  d.serialno, d.ostartdate, d.oenddate         , ( select sum(t.billingpercentage)             yourtable t             d.oenddate between t.startdate , t.enddate                or d.ostartdate between t.startdate , t.enddate                or (d.ostartdate > t.startdate , d.oenddate < t.enddate)               ) billingpercentage      @datestamps d  

---

my complete code is:

declare @datepart int = 5  ;with datestamps (     select 1 serialno, cast('2015-' + convert(varchar, month(min(t.startdate))) + '-01 00:00:00.000' datetime) ostartdate                         , cast('2015-' + convert(varchar, month(min(t.startdate))) + '-01 00:00:00.000' datetime) + (@datepart - 1) oenddate      yourtable t     union      select ds.serialno + 1, ds.ostartdate + @datepart, ds.ostartdate + (@datepart * 2 - 1)     datestamps ds     month(ostartdate + @datepart) <= month(ds.ostartdate) ) select  d.serialno, d.ostartdate, d.oenddate         , ( select sum(t.billingpercentage)             t             d.oenddate between t.startdate , t.enddate                or d.ostartdate between t.startdate , t.enddate                or (d.ostartdate > t.startdate , d.oenddate < t.enddate)               ) billingpercentage      datestamps d