在六个月内保留的参与者百分比

燕飞大侠

我是一位学校老师，对MS SQL Server非常陌生。每个人都建议尝试此站点。开始！
我正在尝试编写查询，以测试针对学术课程参与程度的不同类型的结果度量。我想尝试几种计算此结果度量的方法。我要计算的结果是：
: k1 k& {1 ]! `/ G8 x- X& F1 b( \# z在该计划的六个月中保留的参与者百分比是多少？ 我正在测试定义参与者和不同时间范围的不同方法。我正在尝试产生4个查询。不幸的是，我必须使用不同的表：
9 X5 p+ D7 d) a; a出勤率，状态，取消注册，无效 。我从下面列出了每个示例数据
查询
[ol]参与者被定义为大家，至少参加一类，每周两次为6个月（第181天累积的总）开始于2012年7月1日和结束2013年6月30日，所以财政year.If长度的参与者 deenrolled 或 不活动 ，他们被丢弃。
参与者定义为从2013年1月1日开始，每周至少参加两次课程，共6个月（共181天）的 所有人 。如果参与者 取消注册 或变得 不活跃， 他们将被放弃。
从2013年1月1日到今天，每周至少参加两次课程的每个人都定义为参加者
7 F) H2 f( J. {' @. ~0 q! H参与者被定义为学生的 入学 开始日期，直到他们被取消注册或变得不活跃为止。
, Y/ ^4 i5 C# E[/ol]
参与者（分子）参与者/所服务的所有学生（分母）
* _( \. |+ M2 C4 N我要查找的4个查询输出是此版本的不同版本：
' a0 u, P0 p) |$ M. a例子
Participants    Served   Percent_Served
* y- Q( l5 i( D( v% ]+ v0 v# A- A) Y75               100        75%
5 @% F0 z" e& @7 A  Z我一直在下面弄乱查询的不同版本
% e/ v+ A# l! m9 O7 `8 |0 b+ s SELECT
Count (distinct ID) as Count,
  Count  ( DATEADD( dd, -181, DATEADD(wk, DATEDIFF(wk,0,Date), 0)) > 2 as Participants ,
( Q. l5 x. f1 N3 Z6 t, lFROM Attendance
' ?& Z2 E( t% m/ w+ iwhere Attendence_date date between '07/01/2012' and '06/30/2013'
and ID not in (Select ID from Inactive)
or ID not in (select ID from Deenrolled)
GROUP BY ID
和
$ _6 }+ x8 I! @) V, Y SELECT
Count (distinct ID) as Count,
  Count  ( DATEADD( dd, -181, DATEADD(wk, DATEDIFF(wk,0,Date), 0)) - Enrolled_Date  as Participants ,
FROM Attendance
6 N- f2 d& ^" K" s* M  o. Iwhere Attendence_date date between '07/01/2012' and '06/30/2013'
and ID not in (Select ID from Inactive)
or ID not in (select ID from Deenrolled)
GROUP BY ID
) w; |6 O  d* O, y6 l3 e对于这些查询的任何编程帮助将不胜感激。
以下是样本/示例数据集。
Attendence_date是学生参加一个班级的日期。
7 h$ d+ s# i) A  wCREATE TABLE Attendance (
0 p1 Y! v  h$ ]! `0 i2 q) ]! e    ID int,
; A5 ]% n/ k/ K3 X- t    Attendence_date datetime
* J! j+ N* E3 j7 l0 l* }3 m    )
2 Z2 V) T- E  v# c3 KINSERT INTO Attendance VALUES
(4504498,  '7/1/2012'),
) t7 N: Z7 F4 e$ q# u+ b8 q(4504498,  '7/2/2012'),
. x( P: c4 g/ E1 M% @+ G) A(4504498,   '7/3/2012'),
0 z/ X6 m# s1 w' M/ u(4504498,   '7/4/2012'),
7 F& u7 e# D! A(4504498,   '7/5/2012'),
- x% C% c1 @" M2 D  Z(4504498,   '7/8/2012'),
(4504498,   '7/9/2012'),
(4504498,   '7/10/2012'),
0 ]$ K! L* N# w' c(4504498,   '7/11/2012'),
(4504498,   '7/12/2012'),
/ `; o* n; r1 u, f* `(4504498,   '7/1/2012'),
(4504498,   '7/2/2012'),
: V  l1 O$ ~5 C3 O/ |2 O2 W$ ]  J(4504498,   '7/3/2012'),
6 \7 B! i! @8 G2 U# a(4504498,   '7/4/2012'),
(4504498,   '7/5/2012'),
- p6 ]% r8 g: {3 D; X6 m' Z(4504498,   '7/8/2012'),
0 B" c6 K# M" j' C5 R7 H(4504498,   '7/9/2012'),
(4504498,   '7/10/2012'),
& k8 ]: |, T& m5 A9 j+ c' ]6 d$ H(4504498,   '7/11/2012'),
(4504498,   '7/12/2012'),
  N" p8 Q8 l3 P(9201052,   '7/15/2012'),
(9201052,   '7/16/2012'),
7 H- Y  g4 X3 ^(9201052,   '7/17/2012'),
4 f* n- r9 R, I* d9 z. a. I' s(9201052,   '7/17/2012'),
7 p: l! t+ H! v" G7 a(9201052,   '7/18/2012'),   
( Y) h  a0 m$ E" j3 \6 L(7949745,   '7/17/2012'),   
(7949745,   '7/18/2012'),
8 F* \( \3 S% U7 e7 v7 @  E(7949745,   '7/23/2012'),   
(7949745,   '7/23/2012'),   
% G5 M. T$ B. ^" r(7949745,   '7/24/2012'),
: w9 `1 ~6 h) R& L6 M(7949745,   '7/26/2012'),
$ X$ k% g% K# R& b* w. p(7949745,   '7/26/2012'),   
(7949745,   '8/8/2012'),   
(7949745,   '8/8/2012'),   
(7949745,   '11/5/2012'),   
(7949745,   '11/5/2012'),   
(7949745,   '11/5/2012'),   
  `% C- R- X) x: x) Y(7949745,   '11/6/2012'),   
(7949745,   '11/6/2012'),   
( ?% V7 E. b- R! k4 e(7949745,   '11/6/2012'),   
(7949745,   '11/7/2012'),   
8 N1 K. y' z1 g5 W2 @5 v(7949745,   '11/7/2012'),   
$ S' i1 n+ m% }# D1 R, o(7949745,   '11/7/2012')
' n. b: q* ^' m; `! E- k( q: R5 v+ O这是包含注册日期的信息。
% [7 b  ?+ m! K& \0 G, |4 LCREATE TABLE [Status] (
/ f3 Y* c1 C" ]3 a- H    ID int,
    Intake_Date datetime ,
   Engaged_Date datetime ,
   Enrolled_Date datetime)
6 \) h* G9 [- r, X' _0 G/ |9 WINSERT INTO [Status] VALUES
(7949745, '3/7/2012',   '7/17/2012', '3/8/2012'),
(4504498, '2/21/2013',  '3/5/2013',  '3/22/2013'),
(1486279, '4/18/2013',  '5/7/2013',   '5/20/2013'),
(9201052, '5/15/2012',  '7/13/2012',  '5/15/2012'),
(1722390, '3/5/2012',   '8/27/2012', '3/8/2012'),
$ c) Z/ O* \& R1 n5 m(7735695, '9/7/2012',   '9/7/2012',  '9/28/2012'),
(9261549, '3/7/2012',   '7/24/2012', '3/8/2012'),
(3857008, '3/15/2013',  '3/18/2013', '4/3/2013'),
9 Q3 S* {4 E# ^9 V2 P(8502583, '3/14/2013',     '4/15/2013', '5/3/2013'),
& L# x' Q' u! k(1209774,  '4/19/2012',  '1/1/2012',   '4/24/2012')
9 S( i8 k7 A6 f这是包含取消注册日期的信息。
6 i& e6 B+ S1 B/ oCREATE TABLE Deenrolled (
" {0 j! H' |9 h9 X& X1 _; m# g    ID int,
    Deenrolled_Date datetime)
INSERT INTO Deenrolled  VALUES
(7949745,    '2/4/2013'),
(5485272,    '07/08/2013'),
& l& E' B8 ]9 c" A# h(8955628,    '01/10/2013'),
(5123221,    '7/8/2013'),
(5774753,    '7/18/2013'),
(3005451,    '2/18/2013'),
(7518818,    '05/29/2013'),
(9656985,    '6/20/2013'),
7 S3 M$ z7 V5 O, l(2438101,    '7/17/2013'),
- |0 r0 D' Q( C9 o0 Q' L(1437052,    '7/25/2013'),
(9133874,    '4/25/2013'),
1 ]5 c& k" b6 L; U5 }(7007375,    '6/19/2013'),
/ L; b+ }- a% q(3178181,    '5/24/2013')
0 Q4 d; W7 }8 j, K$ U而且不活跃
CREATE TABLE Inactive (
    ID int,
   Effect_Date datetime)
INSERT INTO Inactive VALUES
(1209774,       '10/12/2012'),
4 O( {1 |$ F0 Q(5419494,       '10/12/2012'),
" z. Q0 _8 ?5 H7 P2 ^) P(4853049,       '10/9/2012'),
% Z5 H( p. f8 Q5 g; O  U  I(1453678,       '5/23/2013'),
(1111554,       '7/16/2012'),
9 i7 A( U% ]  }$ C+ i(5564128,       '2/15/2013'),
(1769234,       '7/16/2012')
2 {+ L1 ^; F8 w- N               
, Z6 `) ~: ^; \, y- Z3 v4 Y( W, \解决方案:
               

: p4 b/ U; D  s* V4 \6 Z* H: p
                好吧，这不是一件容易的事。主要问题是要解决“六个月至少每周两次”的部分-每周计算两次很容易，但是应该连续6个月！  
0 m5 f- V' j0 Z0 w$ v在尝试解决该问题时，我找到了尼尔斯·范德·雷斯特（Niels van derRest）的绝妙答案-在一组数字中找到连续范围。因此，我将为您提供
" d: O* [* u8 e5 _, z, P+ {第1部分的 一般查询，您可以更改参数并获取 第2部分的 结果：
declare @Weeks int, @PerWeek int, @StartDate date, @EndDate date, @count
1 j) y3 P% w. Q( Y  ^' w! uselect
    @StartDate = '20120701',
- @9 h8 r0 d9 ^6 y/ X3 i! X$ q    @EndDate = '20130630',
; E- U4 W" i9 ?! p+ V    @Weeks = 26, -- 6 month or 26 weeks
, q: ~  N& c5 t    @PerWeek = 2 -- twice per week
4 B) ]! H4 y! w" f- n$ z& Bselect @count = count(distinct A.ID)
from Attendance as A
where
- q; G3 h1 `. b0 x) y    A.Attendence_date between @StartDate and @EndDate and
1 v$ d8 j" {+ j+ p7 D5 c5 Z5 [    A.ID not in (select T.ID from Deenrolled as T) and
    A.ID not in (select T.ID from Inactive as T)
$ s8 N7 U0 v6 m6 L;with CTE as (
    -- Week numbers, filter by dates
    select
" \  x5 x, s8 M/ z. ~- A: P4 F3 Z        A.ID,
        datediff(dd, @StartDate, A.Attendence_date) / 7 as Wk
" y7 p( v6 V! _/ G. J  j/ n5 @8 F! A    from Attendance as A
+ X! Z# x6 Q' r# `- M- B    where
        A.Attendence_date between @StartDate and @EndDate and
        A.ID not in (select T.ID from Deenrolled as T) and
        A.ID not in (select T.ID from Inactive as T)
  ), CTE2 as (
    -- Group by week, filter less then @PerWeek per week, calculate row number
1 \% S. @& G2 g8 w    select
        Wk, ID,
        row_number() over (partition by ID order by Wk) as Row_Num
    from CTE
0 ~' O' q# v4 `( H    group by Wk, ID
    having count(*) >= @PerWeek
9 _  ]1 v4 _0 S, ^3 L: })
" U6 J0 J. A( b0 s- L-- Final query - group by difference between week and row_number
select 100 * cast(count(distinct ID) as float) / @count
! ?- r* B! c* [% ?. U( afrom CTE2
) T; k7 i6 q9 c5 ?+ ^group by ID, Wk - Row_Num
. `7 e6 v% L9 J5 ~having count(*) >= @Weeks
* @1 K; {7 |9 q! @. V我创建了 SQL FIDDLE EXAMPLE ，您可以测试查询。
第3部分 很简单
% S  z* z) {% }4 [+ t7 }declare @PerWeek int, @StartDate date
7 z, E  n2 ^  P- J( V- U$ Aselect
    @StartDate = '20130101',
6 U5 \) |1 C1 m6 b3 t* J9 l    @PerWeek = 2 -- twice per week
" L2 c6 L" w' ?4 _6 dselect @count = count(distinct A.ID)
from Attendance as A
where
/ ~' K6 y% ^  A; Z: h) k    A.Attendence_date >= @StartDate and
6 G; w: H' S) o" ~/ B    A.ID not in (select T.ID from Deenrolled as T) and
    A.ID not in (select T.ID from Inactive as T)
;with CTE as (
) `9 o4 v% s- S" u1 H# v: ^8 @    -- Week numbers, filter by dates
    select
        A.ID,
$ @% z2 |/ |' A; y' _        datediff(dd, @StartDate, A.Attendence_date) / 7 as Wk
    from Attendance as A
" w+ d6 `. j5 C/ g. `' n* Q    where
        A.Attendence_date >= @StartDate and
        A.ID not in (select T.ID from Deenrolled as T) and
        A.ID not in (select T.ID from Inactive as T)
  ), CTE2 as (
+ G3 Y, {; ^& W8 r$ t6 w+ E' R    -- Group by week, filter less then @PerWeek per week
4 G0 E% c! M) j& K* N1 {. ~    select distinct ID
    from CTE
8 N' [4 M1 r% }. D/ c4 Z6 B    group by Wk, ID
    having count(*) >= @PerWeek
)
select 100 * cast(count(*) as float) / @count from CTE2
第4部分 对我来说似乎有点不清楚，您能澄清一下吗？