在 sql Server 2008 中按周选择每日数据组

Question

嗨，朋友，我有以下我的表结构和数据

   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

这是我存储的过程.

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]))) < 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)) > Sum(ISNULL(rjoin,0)) Then Sum(ISNULL(ljoin,0))-Sum(ISNULL(rjoin,0))  
 End LeftCary,
 Case 
 When Sum(ISNULL(rjoin,0)) > 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

如果我按如下方式执行我的存储过程，它会返回以下结果

执行pair_Scounting"LDS"，"2014-02-17"，"2014-02-28"

 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

那么我们如何添加这个 LeftCary （8） 与下一个日期范围的 ljoin（24-02-2014 至 2-03-2014）和

右卡里（2）与下一个日期范围（4-03-2014 TO 10-03-2014）

在这里表示日期范围（24-02-2014 至 2-03-2014）的 ljoin 为 1，日期范围（ 17-02-2014 至 23-02-2014）

的 LeftCary 为 8，因此 DateRange（（24-02-2014 至 2-03-2014）） 的总联接为 8+1=9

我想要的结果如下

userid ljoin rjoin 周数 日期范围 左卡里 右卡里

 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

请任何人都可以建议我们如何做到这一点

谢谢

Answer 1

我会将存储过程转换为内联表值函数并自行连接它。以下假设您不会有重复的周数：

SELECT    x.userid,
          (x.ljoin + ISNULL((SELECT y.LeftCarry FROM dbo.InLine(@param1, @param2, @param3) AS y WHERE y.WeekNumber = (x.WeekNumber - 1)), 0)) AS ljoin_carry,
          (x.rjoin + ISNULL((SELECT z.RightCarry FROM dbo.InLine(@param1, @param2, @param3) AS z WHERE z.WeekNumber = (x.WeekNumber - 1)), 0)) AS rjoin_carry,
          x.WeekNumber,
          x.DateRange,
          x.LeftCarry,
          x.RightCarry
FROM      dbo.InLine(@param1, @param2, @param3) AS x
ORDER BY  x.WeekNumber