SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate number of days missed per term


Calculate number of days missed per term

Author
Message
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16174 Visits: 19543
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 Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
Sqlraider
Sqlraider
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2401 Visits: 2295
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 = 1
Term 2 Missed = 0
Total(accumulation) = 1

when it should be:
Term 1 Missed = 1
Term 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 Missed
2012-2013 414 355 CSA 1-2 1 0 21.00 NULL 2012-10-12 0
2012-2013 414 355 CSA 2-2 2 0 23.00 2012-10-13 2012-12-20 1
2012-2013 414 355 CSA 3-1 3 0 16.00 2012-12-21 2013-03-07 1
2012-2013 414 355 CSA 4-1 4 0 16.00 2013-03-08 2013-05-24 0
2012-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?
Sqlraider
Sqlraider
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2401 Visits: 2295
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search