title: Select daily data group by weekly in sql server 2008
body: <p>hi friend 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)) &gt; Sum(ISNULL(rjoin,0)) Then Sum(ISNULL(ljoin,0))-Sum(ISNULL(rjoin,0))  
   End LeftCary,
   Case 
   When Sum(ISNULL(rjoin,0)) &gt; Sum(ISNULL(ljoin,0)) Then Sum(ISNULL(rjoin,0))-Sum(ISNULL(ljoin,0))  
   End RightCary
  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>
  
  <p>exec pair_Scounting 'LDS','2014-02-17','2014-02-28'</p>
  
  <pre><code> userid ljoin   rjoin   WeekNumber  DateRange           LeftCary     RightCary
  
  
   LDS    10        2       8   17-02-2014  TO 23-02-2014    8          NULL
   LDS    1         3       9   24-02-2014  TO 2-03-2014    NULL         2
  </code></pre>
  
  <p>so how we can add this LeftCary (8) with ljoin of next DateRange(24-02-2014 To 2-03-2014)and </p>
  
  <p>RightCary(2)with rjoin of next DateRange(4-03-2014  TO 10-03-2014)</p>
  
  <p>means here ljoin of DateRange(24-02-2014  TO 2-03-2014) is 1 and LeftCary of DateRange( 17-02-2014  TO 23-02-2014) is 8 so total ljoin of DateRange((24-02-2014  TO 2-03-2014)) is 8+1=9</p>
  
  <p>My Desired result as follows</p>
  
  <p>userid ljoin   rjoin   WeekNumber  DateRange           LeftCary     RightCary</p>
  
  <pre><code> LDS    10        2       8   17-02-2014  TO 23-02-2014    8          NULL
   LDS    9         3       9   24-02-2014  TO 2-03-2014    NULL         2
  </code></pre>
  
  <p>please any one can suggest us how this can be done </p>
  
  <p>thanks </p>