title: How to weekly group if I have daily data
body: <p>i have following my table structure and data </p>
  
  <pre><code>   CREATE TABLE [dbo].[Pairs_Details](
     [sno] [int] IDENTITY(1,1) NOT NULL,
     [userid] [nvarchar](50) NULL,
     [date] [datetime] NULL,
     [ljoin] [int] NULL,
     [rjoin] [int] NULL
         ) ON [PRIMARY]
  
  sno userid          date                             ljoin  rjoin
  
  1   LDS         2014-02-17 00:00:00.000              1     NULL
  2   LDS         2014-02-17 00:00:00.000             NULL    1
  3   LDS1        2014-02-18 00:00:00.000              1     NULL
  4   LDS         2014-02-18 00:00:00.000              1     NULL
  5   LDS1        2014-02-18 00:00:00.000              NULL   1
  6   LDS         2014-02-18 00:00:00.000              1     NULL
  7   SUNIL1      2014-02-19 00:00:00.000              1     NULL
  8   LDS1        2014-02-19 00:00:00.000              1     NULL
  9   LDS         2014-02-19 00:00:00.000              1     NULL
  10  SUNIL1      2014-02-19 00:00:00.000             NULL    1
  11  LDS1       2014-02-19 00:00:00.000               1      NULL
  12  LDS         2014-02-19 00:00:00.000              1      NULL
  13  SUNIL2     2014-02-19 00:00:00.000               1  NULL
  14  LDS1          2014-02-19 00:00:00.000               NULL    1
  15  LDS       2014-02-19 00:00:00.000                1      NULL
  16  rajesh123  2014-02-19 00:00:00.000                   1  NULL
  17  SUNIL1     2014-02-19 00:00:00.000              NULL    1
  18  LDS1       2014-02-19 00:00:00.000               1        NULL
  19  LDS      2014-02-19 00:00:00.000                  1       NULL
  20  SUNIL2     2014-02-19 00:00:00.000              NULL    1
  21  LDS1       2014-02-19 00:00:00.000              NULL    1
  22  LDS        2014-02-19 00:00:00.000              1          NULL
  23  LDS2       2014-02-19 00:00:00.000              1         NULL
  24  LDS        2014-02-19 00:00:00.000              NULL    1
  25  SUNIL1     2014-02-20 00:00:00.000                  NULL        1
  26  LDS1       2014-02-20 00:00:00.000              1        NULL
  27  LDS    2014-02-20 00:00:00.000              1         NULL
  28  rajesh123  2014-02-20 00:00:00.000              NULL     1
  29  SUNIL1     2014-02-20 00:00:00.000              NULL    1
  30  LDS1       2014-02-20 00:00:00.000              1         NULL
  31  LDS     2014-02-20 00:00:00.000             1   NULL
  32  LDS    2014-02-24 00:00:00.000              NULL    1
  33  Jitendra123 2014-02-27 00:00:00.000             1   NULL
  34  LDS2            2014-02-27 00:00:00.000             1   NULL
  35  LDS         2014-02-27 00:00:00.000             NULL    1
  36  rajeev123   2014-02-27 00:00:00.000              1  NULL
  37  Jitendra123 2014-02-27 00:00:00.000              1  NULL
  40  jyoti123    2014-02-27 00:00:00.000              1  NULL
  41  SUNIL1          2014-02-27 00:00:00.000              1  NULL
  42  LDS1            2014-02-27 00:00:00.000              1  NULL
  43  LDS         2014-02-27 00:00:00.000              1  NULL
  44  meeta           2014-03-01 00:00:00.000              1  NULL
  45  jyoti123    2014-03-01 00:00:00.000              1  NULL
  46  SUNIL1   2014-03-01 00:00:00.000                 1  NULL
  47  LDS1     2014-03-01 00:00:00.000                      1 NULL
  48  LDS  2014-03-01 00:00:00.000              1 NULL
  38  LDS2     2014-02-27 00:00:00.000              1 NULL
  39  LDS  2014-02-27 00:00:00.000              NULL  1
  </code></pre>
  
  <p><strong>and this is my stored procdure .</strong></p>
  
  <pre><code>create proc [dbo].[pair_Scounting]
  (
  @userid nvarchar(50),
  @start_date datetime,
  @end_date datetime
  )
  as
  begin
  SET DATEFIRST 1;
  SELECT  userid,
  Sum(ISNULL(ljoin,0)) AS ljoin,
  Sum(ISNULL(rjoin,0)) AS rjoin, DATEPART(wk, Date) AS WeekNumber,
  CASE 
  WHEN YEAR(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min([date])), Min([date]))) &lt; YEAR(Min([date]))
  THEN CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0 ,GETDATE())), 0) AS Varchar(50))   + ' TO ' + Cast(DATEADD(dd, 7-(DATEPART(dw, Min([date]))), Min([date]))  AS Varchar(50))
  ELSE
  Cast(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min([date])), Min([date])) AS Varchar(50)) + ' TO ' + Cast(DATEADD(dd, 7-(DATEPART(dw, Min([date]))), Min([date]))  AS Varchar(50))
  END  DateRange,
  Case 
   When Sum(ISNULL(ljoin,0)) &lt; Sum(ISNULL(rjoin,0)) Then Sum(ISNULL(ljoin,0)) 
    When Sum(ISNULL(rjoin,0)) &lt; Sum(ISNULL(ljoin,0)) Then Sum(ISNULL(rjoin,0)) 
    Else (Sum(ISNULL(rjoin,0))-1) 
  End  pair
  FROM Pairs_Details  where  userid=@userid and date between @start_date and @end_date
  Group By userid,DATEPART(wk, Date)
  end
  GO
  </code></pre>
  
  <p>if i execute my stored procedure as follows it return following result</p>
  
  <pre><code>exec pair_Scounting 'LDS','2014-02-17','2014-02-28'
  
  
  
  userid  ljoin   rjoin   WeekNumber  DateRange                               pair
  
   LDS    10        2       8     Feb 17 2014 12:00AM TO Feb 23 2014 12:00AM    2
   LDS    1         3       9     Feb 24 2014 12:00AM TO Mar  2 2014 12:00AM    1
  </code></pre>
  
  <p>but i want if i execute my stored procedure it return my desired result as follows</p>
  
  <p>please read carefully above my stored procedure  which is used to count pair weekly</p>
  
  <p>my desired output following  </p>
  
  <pre><code>userid  ljoin   rjoin   WeekNumber  DateRange                               pair
  
   LDS    10        2       8     Feb 17 2014 12:00AM TO Feb 23 2014 12:00AM    2
   LDS    9         3       9     Feb 24 2014 12:00AM TO Mar  2 2014 12:00AM    3
  </code></pre>
  
  <p>if (ljoin>rjoin) then ljoin-rjoin(10-2=8) value must be added to ljoin of next week and </p>
  
  <p>so ljoin of next week from Feb 24 2014 12:00AM TO Mar  2 2014 12:00AM is 8+1=9 according my data </p>
  
  <p>if (rjoin>ljoin) then rjoin-ljoin value must be added to rjoin of next week</p>
  
  <p>so how it can be done please any can suggest us.</p>
  
  <p>thanks</p>