Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate number of days missed per term Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, April 3, 2013 8:26 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 Does it meet the requirements for Step 1? The accumulation would be Step 2. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1438378
 Posted Wednesday, April 3, 2013 8:58 AM
 SSCrazy Group: General Forum Members Last Login: Saturday, November 19, 2016 10:30 AM Points: 2,142, Visits: 2,220
 ChrisM@Work (4/3/2013)Does it meet the requirements for Step 1? The accumulation would be Step 2.I'm going to say no it doesn't meet the requirements for Step 1 and here's why.If an ID missed 15 periods in Term 1 and missed One period in Term 2. The results of your query would be:Term 1 Missed = 1Term 2 Missed = 0Total(accumulation) = 1when it should be:Term 1 Missed = 1Term 2 Missed = 2 (accumulated days missed from first day of school thru the end of Term 2)Also, ID 717 missed One prior to Term 4 starting and the results of your query is Zero, so an accumulation for 717 would be Zero when it should be One.I would think that instead of calculating a suitable term start date (StartDate in your query), I just pass it the first day of school this would work all in One Step. If the StartDate is the same date for the calculation of a Term then we get the accumulation, correct?Your query produced the following results:`SchoolYR ID LocID TestName Term DaysMissed Score StartDate EndDate Missed2012-2013 414 355 CSA 1-2 1 0 21.00 NULL 2012-10-12 02012-2013 414 355 CSA 2-2 2 0 23.00 2012-10-13 2012-12-20 12012-2013 414 355 CSA 3-1 3 0 16.00 2012-12-21 2013-03-07 12012-2013 414 355 CSA 4-1 4 0 16.00 2013-03-08 2013-05-24 02012-2013 414 355 CSA 4-2 4 0 23.00 2013-03-08 2013-05-24 0`But what if the StartDate for ALL results was 2012-08-14? Wouldn't that get the correct #'s for each Term?Or do I not even come close to understanding your query?
Post #1438412
 Posted Wednesday, April 3, 2013 9:13 AM
 SSCrazy Group: General Forum Members Last Login: Saturday, November 19, 2016 10:30 AM Points: 2,142, Visits: 2,220
 Chris,I substituted this line in your code with this and it worked.`--AND a.DateABS BETWEEN ISNULL(x.StartDate,'19000101') AND t.EndDate AND a.DateABS BETWEEN ISNULL(@FirstDay,'19000101') AND t.EndDate`@FirstDay was set to '2012-08-14'This got me the desired results for the test data I supplied. Now I need to test against Prod data.Thanks again!!Sqlraider
Post #1438425

 Permissions