Posted: 29 Jul 2013 09:19 AM PDT

I am a school teacher very new to MS SQL server. Everyone is suggesting to try this site out. Here goes! I am trying to write queries to test different types of outcome measures for the participation in a academic program. There are several different ways to calculate this outcome measurement I would like to try. The outcome which I am trying to Calculate is: What is the % of participants that are retained during six months of the program? I am testing different ways to define participant and different time ranges. There are 4 queries I am trying to produce. Unfortunately, I have to use for different tables: [b]Attendance, Status, Deerolled, Inactive[/b]. I have included sample data from each below. [b]Queries[/b]1.A participant is defined as everyone that attended a class at least twice a week for 6 months (181 days total) starting at July 1st 2012 and Ending June 30th 2013, so the length of the fiscal year. If a participant is deenrolled or inactive they are dropped. 2.A participant is defined as everyone that attended a class at least twice a week for 6 months (181 days total) starting at January 1st 2013. If a participant is deenrolled or becomes inactive they are dropped.3.A participant is defined as everyone that attended a class at least twice a week starting at January 1st 2013 until today4.A participant is defined as a student's enrollment start date until they are deenrolled or become inactive. Participant (Numerator) participant / all students which were served (Denominator) The 4 query outputs I am looking for are different versions of this: Example:[b]Participants [/b] [b]Served[/b] [b]Percent_Served [/b]75 100 75% I have been messing arouns with different versions of the queries below. As you can see I am very new to SQL server. :) [code="sql"]SELECT Count (distinct ID) as Count, Count ( DATEADD( dd, -181, DATEADD(wk, DATEDIFF(wk,0,Date), 0)) > 2 as Participants ,FROM Attendancewhere 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 Denenrolled) GROUP BY ID[/code][code="sql"]SELECT Count (distinct ID) as Count, Count ( DATEADD( dd, -181, DATEADD(wk, DATEDIFF(wk,0,Date), 0)) - Enrolled_Date as Participants ,FROM Attendancewhere 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 Denenrolled) GROUP BY ID [code="sql"]Any programming assistance for these queries is greatly appreciated. Below are the sample/exmaple datasets. Attendence_date is the date a student participated in one class. [code="sql"]CREATE TABLE Attendance ( ID int, Attendence_date datetime, )INSERT INTO ID Attendence_date 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') [/code][code="sql"]CREATE TABLE Status ( ID int, Intake_Date datetime , Engaged_Date datetime ), Enrolled_Date datetime)INSERT INTO ID Intake_Date Engaged_Date Enrolled_Date 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')[/code][code="sql"]CREATE TABLE Deenrolled ( ID int, Deenrolled_Date datetime)INSERT INTO ID 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')[/code][code="sql"]CREATE TABLE Inactive ( ID int, Effect_Date datetime)INSERT INTO ID Effect_Date 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')[/code]

Posted: 29 Jul 2013 08:57 AM PDT

Hi everyone,Does anybody know any cases/example that we need to use UNIQUE KEY instead of PRIMARY KEY? I understand that UNIQUE KEY can allow one NULL value in the data, but I don't know the reason for that. Why do we need that extra NULL VALUE? And, When do we need it?Thanks!Kaz

Eager Spool

Posted: 13 Apr 2013 08:45 PM PDT

Dear AllI am deleting rows from huge table. I am doing it in the batch of 1000 rows. This tables has got cluster index, and many indexes. In the execution plan its shows deletion on the cluster index is feeling up the Eager spool and this eager spool table is used for deletion of other indexes.Cost of feeling the eager spool is shown Zero% but for other idexex is 8% each.Becasuse of eager spool it is increasing tempdb_log file also and perfomance of the query is slow.Any suggestion to improve the perfromance.

Sql samples, sql tips

Monday, July 29, 2013

[SQL Server] % of participants that are retained

[SQL Server] % of participants that are retained

No comments:

Post a Comment

Search This Blog