我是一位学校老师,对MS SQL Server非常陌生。每个人都建议尝试此站点。开始!
我正在尝试编写查询,以测试针对学术课程参与程度的不同类型的结果度量。我想尝试几种计算此结果度量的方法。我要计算的结果是: 在该计划的六个月中保留的参与者百分比是多少? 我正在测试定义参与者和不同时间范围的不同方法。我正在尝试产生4个查询。不幸的是,我必须使用不同的表: 出勤率,状态,取消注册,无效 。我从下面列出了每个示例数据
参与者(分子)参与者/所服务的所有学生(分母)
我要查找的4个查询输出是此版本的不同版本:
例子
Participants Served Percent_Served 75 100 75%
我一直在下面弄乱查询的不同版本
SELECT Count (distinct ID) as Count, Count ( DATEADD( dd, -181, DATEADD(wk, DATEDIFF(wk,0,Date), 0)) > 2 as Participants , FROM Attendance where 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
和
SELECT Count (distinct ID) as Count, Count ( DATEADD( dd, -181, DATEADD(wk, DATEDIFF(wk,0,Date), 0)) - Enrolled_Date as Participants , FROM Attendance where 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
对于这些查询的任何编程帮助将不胜感激。
以下是样本/示例数据集。
Attendence_date是学生参加一个班级的日期。
CREATE TABLE Attendance ( ID int, Attendence_date datetime ) INSERT INTO Attendance VALUES (4504498, '7/1/2012'), (4504498, '7/2/2012'), (4504498, '7/3/2012'), (4504498, '7/4/2012'), (4504498, '7/5/2012'), (4504498, '7/8/2012'), (4504498, '7/9/2012'), (4504498, '7/10/2012'), (4504498, '7/11/2012'), (4504498, '7/12/2012'), (4504498, '7/1/2012'), (4504498, '7/2/2012'), (4504498, '7/3/2012'), (4504498, '7/4/2012'), (4504498, '7/5/2012'), (4504498, '7/8/2012'), (4504498, '7/9/2012'), (4504498, '7/10/2012'), (4504498, '7/11/2012'), (4504498, '7/12/2012'), (9201052, '7/15/2012'), (9201052, '7/16/2012'), (9201052, '7/17/2012'), (9201052, '7/17/2012'), (9201052, '7/18/2012'), (7949745, '7/17/2012'), (7949745, '7/18/2012'), (7949745, '7/23/2012'), (7949745, '7/23/2012'), (7949745, '7/24/2012'), (7949745, '7/26/2012'), (7949745, '7/26/2012'), (7949745, '8/8/2012'), (7949745, '8/8/2012'), (7949745, '11/5/2012'), (7949745, '11/5/2012'), (7949745, '11/5/2012'), (7949745, '11/6/2012'), (7949745, '11/6/2012'), (7949745, '11/6/2012'), (7949745, '11/7/2012'), (7949745, '11/7/2012'), (7949745, '11/7/2012')
这是包含注册日期的信息。
CREATE TABLE [Status] ( ID int, Intake_Date datetime , Engaged_Date datetime , Enrolled_Date datetime) INSERT 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'), (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'), (8502583, '3/14/2013', '4/15/2013', '5/3/2013'), (1209774, '4/19/2012', '1/1/2012', '4/24/2012')
这是包含取消注册日期的信息。
CREATE TABLE Deenrolled ( ID int, Deenrolled_Date datetime) INSERT INTO Deenrolled VALUES (7949745, '2/4/2013'), (5485272, '07/08/2013'), (8955628, '01/10/2013'), (5123221, '7/8/2013'), (5774753, '7/18/2013'), (3005451, '2/18/2013'), (7518818, '05/29/2013'), (9656985, '6/20/2013'), (2438101, '7/17/2013'), (1437052, '7/25/2013'), (9133874, '4/25/2013'), (7007375, '6/19/2013'), (3178181, '5/24/2013')
而且不活跃
CREATE TABLE Inactive ( ID int, Effect_Date datetime) INSERT INTO Inactive VALUES (1209774, '10/12/2012'), (5419494, '10/12/2012'), (4853049, '10/9/2012'), (1453678, '5/23/2013'), (1111554, '7/16/2012'), (5564128, '2/15/2013'), (1769234, '7/16/2012')
好吧,这不是一件容易的事。主要问题是要解决“六个月至少每周两次”的部分-每周计算两次很容易,但是应该连续6个月!
在尝试解决该问题时,我找到了尼尔斯·范德·雷斯特(Niels van derRest)的绝妙答案-在一组数字中找到连续范围。因此,我将为您提供 第1部分的 一般查询,您可以更改参数并获取 第2部分的 结果:
declare @Weeks int, @PerWeek int, @StartDate date, @EndDate date, @count select @StartDate = '20120701', @EndDate = '20130630', @Weeks = 26, -- 6 month or 26 weeks @PerWeek = 2 -- twice per week select @count = count(distinct A.ID) from Attendance as A 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) ;with CTE as ( -- Week numbers, filter by dates select A.ID, datediff(dd, @StartDate, A.Attendence_date) / 7 as Wk from Attendance as A 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 select Wk, ID, row_number() over (partition by ID order by Wk) as Row_Num from CTE group by Wk, ID having count(*) >= @PerWeek ) -- Final query - group by difference between week and row_number select 100 * cast(count(distinct ID) as float) / @count from CTE2 group by ID, Wk - Row_Num having count(*) >= @Weeks
我创建了 SQL FIDDLE EXAMPLE ,您可以测试查询。
第3部分 很简单
declare @PerWeek int, @StartDate date select @StartDate = '20130101', @PerWeek = 2 -- twice per week select @count = count(distinct A.ID) from Attendance as A 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) ;with CTE as ( -- Week numbers, filter by dates select A.ID, datediff(dd, @StartDate, A.Attendence_date) / 7 as Wk from Attendance as A 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 ( -- Group by week, filter less then @PerWeek per week select distinct ID from CTE group by Wk, ID having count(*) >= @PerWeek ) select 100 * cast(count(*) as float) / @count from CTE2
第4部分 对我来说似乎有点不清楚,您能澄清一下吗?