Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Calculate number of days missed per term Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Post #1438378
Posted Wednesday, April 3, 2013 8:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 26, 2014 11:00 AM
Points: 1,367, Visits: 1,835
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?
Post #1438412
Posted Wednesday, April 3, 2013 9:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 26, 2014 11:00 AM
Points: 1,367, Visits: 1,835
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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse