Query to pull school year based on test date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query to pull school year based on test date

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2012-10-29 : 14:49:29

I'm trying to pull the correct School year based on the test date. If a test date occurs between the 1st day of school and the next years 1st day of school that is the school year for that test. Keep in mind that next years school terms will not be created until late summer so for the current school year next years 1st day of school would be Null.


CREATE TABLE Test
(
	ID				INT				not null,
	LocID			CHAR(4)			not null, 
	TestName		VARCHAR(30)		not null,
	TestDate		SMALLDATETIME	not	null,
	Score			decimal(6,2)	not null	
)
GO


INSERT  Test 
(ID, LocID, TestName, TestDate, Score) 
VALUES
(3377, '355', 'ACT_ENG', '2012-09-27 00:00:00', 16.00),
(3377, '355', 'ACT_SCI', '2012-09-27 00:00:00', 23.00),
(1985, '355', 'ACT_MATH', '2012-07-02 00:00:00', 15.00),
(1985, '355', 'ACT_RDG', '2012-07-02 00:00:00', 17.00),
(1985, '355', 'ACT_ENG', '2012-07-02 00:00:00', 16.00),
(1985, '355', 'ACT_SCI', '2012-07-02 00:00:00', 23.00),
(7798, '355', 'ACT_MATH', '2012-08-13 00:00:00', 15.00),
(7798, '355', 'ACT_RDG', '2012-08-13 00:00:00', 17.00),
(7798, '355', 'ACT_ENG', '2012-08-13 00:00:00', 16.00),
(7798, '355', 'ACT_SCI', '2012-08-13 00:00:00', 23.00),
(5732, '344', 'ACT_MATH', '2012-05-02 00:00:00', 15.00),
(5732, '344', 'ACT_RDG', '2012-05-02 00:00:00', 17.00),
(5732, '344', 'ACT_ENG', '2012-05-02 00:00:00', 16.00),
(5732, '344', 'ACT_SCI', '2012-05-02 00:00:00', 23.00),
(2735, '344', 'ACT_MATH', '2012-08-14 00:00:00', 15.00),
(2735, '344', 'ACT_RDG', '2012-08-14 00:00:00', 16.00),
(2735, '344', 'ACT_ENG', '2012-08-14 00:00:00', 15.00),
(2735, '344', 'ACT_SCI', '2012-08-14 00:00:00', 21.00),
(2735, '344', 'ACT_MATH', '2012-08-14 00:00:00', 15.00),
(4343, '355', 'ACT_ENG', '2011-06-02 00:00:00', 16.00),
(4343, '355', 'ACT_SCI', '2011-06-02 00:00:00', 23.00),
(6831, '344', 'ACT_ENG', '2011-09-12 00:00:00', 16.00),
(6831, '344', 'ACT_SCI', '2011-09-12 00:00:00', 23.00),
(8343, '355', 'ACT_ENG', '2010-08-18 00:00:00', 16.00),
(8343, '355', 'ACT_SCI', '2010-08-18 00:00:00', 23.00),
(9831, '344', 'ACT_ENG', '2010-09-16 00:00:00', 16.00),
(9831, '344', 'ACT_SCI', '2010-09-16 00:00:00', 23.00)

GO

CREATE TABLE SchoolTerm
(
	SchoolYR		CHAR(9)			not null,
	LocID			CHAR(4)			not null, 
	Term			TINYINT			not null,
	StartDate		DATE			not	null
)
GO

INSERT  SchoolTerm 
(SchoolYR, Term, LocID, StartDate) 
VALUES
('2010-2011', 1, '355', '2010-08-17'),
('2010-2011', 2, '355', '2010-10-19'),
('2010-2011', 3, '355', '2011-01-05'),
('2010-2011', 4, '355', '2011-03-21'),
('2010-2011', 1, '344', '2010-08-17'),
('2010-2011', 2, '344', '2010-10-19'),
('2010-2011', 3, '344', '2011-01-05'),
('2010-2011', 4, '344', '2011-03-21'),

('2011-2012', 1, '355', '2011-08-16'),
('2011-2012', 2, '355', '2011-10-18'),
('2011-2012', 3, '355', '2012-01-03'),
('2011-2012', 4, '355', '2012-03-19'),
('2011-2012', 1, '344', '2011-08-16'),
('2011-2012', 2, '344', '2011-10-18'),
('2011-2012', 3, '344', '2012-01-03'),
('2011-2012', 4, '344', '2012-03-19'),

('2012-2013', 1, '355', '2012-08-14'),
('2012-2013', 2, '355', '2012-10-15'),
('2012-2013', 3, '355', '2013-01-03'),
('2012-2013', 4, '355', '2013-03-11'),
('2012-2013', 1, '344', '2012-08-14'),
('2012-2013', 2, '344', '2012-10-15'),
('2012-2013', 3, '344', '2013-01-03'),
('2012-2013', 4, '344', '2013-03-11')
GO

Desired results:
ID LocID TestName TestDate Score SchoolYR
3377 355 ACT_ENG 2012-09-27 00:00:00 16.00 2012-2013
3377 355 ACT_SCI 2012-09-27 00:00:00 23.00 2012-2013
1985 355 ACT_MATH 2012-07-02 00:00:00 15.00 2011-2012
1985 355 ACT_RDG 2012-07-02 00:00:00 17.00 2011-2012
1985 355 ACT_ENG 2012-07-02 00:00:00 16.00 2011-2012
1985 355 ACT_SCI 2012-07-02 00:00:00 23.00 2011-2012
7798 355 ACT_MATH 2012-08-13 00:00:00 15.00 2011-2012
7798 355 ACT_RDG 2012-08-13 00:00:00 17.00 2011-2012
7798 355 ACT_ENG 2012-08-13 00:00:00 16.00 2011-2012
7798 355 ACT_SCI 2012-08-13 00:00:00 23.00 2011-2012
5732 344 ACT_MATH 2012-05-02 00:00:00 15.00 2011-2012
5732 344 ACT_RDG 2012-05-02 00:00:00 17.00 2011-2012
5732 344 ACT_ENG 2012-05-02 00:00:00 16.00 2011-2012
5732 344 ACT_SCI 2012-05-02 00:00:00 23.00 2011-2012
2735 344 ACT_MATH 2012-08-14 00:00:00 15.00 2012-2013
2735 344 ACT_RDG 2012-08-14 00:00:00 16.00 2012-2013
2735 344 ACT_ENG 2012-08-14 00:00:00 15.00 2012-2013
2735 344 ACT_SCI 2012-08-14 00:00:00 21.00 2012-2013
2735 344 ACT_MATH 2012-08-14 00:00:00 15.00 2012-2013
4343 355 ACT_ENG 2011-06-02 00:00:00 16.00 2010-2011
4343 355 ACT_SCI 2011-06-02 00:00:00 23.00 2010-2011
6831 344 ACT_ENG 2011-09-12 00:00:00 16.00 2011-2012
6831 344 ACT_SCI 2011-09-12 00:00:00 23.00 2011-2012
8343 355 ACT_ENG 2010-08-18 00:00:00 16.00 2010-2011
8343 355 ACT_SCI 2010-08-18 00:00:00 23.00 2010-2011
9831 344 ACT_ENG 2010-09-16 00:00:00 16.00 2010-2011
9831 344 ACT_SCI 2010-09-16 00:00:00 23.00 2010-2011

Not sure how to get the next years 1st day of school because that is not in the SchoolTerm table.
I've tried the following query. It returns the correct number of rows but the SchoolYR column is Null.


(Select 
	t.ID, t.LocID, t.TestName, 
	t.TestDate, t.Score,
	s.SchoolYR
 from
	(Select ID, LocID, TestName, TestDate, Score 
	from Test) AS t
	left join SchoolTerm AS s
	on  s.Term = 1
	and s.LocID = t.LocID
	and (t.TestDate >= dateadd(day,datediff(day,0,s.StartDate)-0,0)
	and t.TestDate <= dateadd(day,datediff(day,0,s.StartDate)-1,0))

	where (t.TestDate >= dateadd(day,datediff(day,0,getdate())-804,0)
	and    t.TestDate <= dateadd(day,datediff(day,0,getdate())-1,0)) 
		 )

Thanks.

aaronwc
Starting Member

2 Posts

Posted - 2012-10-29 : 16:32:29

This should work though I'm not sure you need the Term=1 logic.

SELECT
t.ID,t.LocID,t.TestName,t.TestDate, t.Score,s.SchoolYR
FROM dbo.Test t
LEFT JOIN dbo.SchoolTerm s
ON (t.LocID = s.LocID)
WHERE s.StartDate = (SELECT MAX(startDate) as Startdate from SchoolTerm where StartDate <= t.TestDate
--and Term = 1
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 16:33:56

[code]

;With CTE
AS
(
SELECT st.*,COALESCE(DATEADD(dd,-1,st1.StartDate),'20200101') AS EndDate
FROM #SchoolTerm st
OUTER APPLY (SELECT TOP 1 StartDate
FROM #SchoolTerm
WHERE LocID = st.LocID
AND StartDate > st.StartDate
ORDER BY StartDAte)st1
)

SELECT t.*,st.SchoolYr
FROM #Test t
INNER JOIN CTE st
ON t.locID = st.LOcID
AND t.TestDate BETWEEN st.StartDate AND st.EndDate

output
-------------------------------------------------------------------
ID LocID TestName TestDate Score SchoolYr
-------------------------------------------------------------------
3377 355 ACT_ENG 2012-09-27 00:00:00 16.00 2012-2013
3377 355 ACT_SCI 2012-09-27 00:00:00 23.00 2012-2013
1985 355 ACT_MATH 2012-07-02 00:00:00 15.00 2011-2012
1985 355 ACT_RDG 2012-07-02 00:00:00 17.00 2011-2012
1985 355 ACT_ENG 2012-07-02 00:00:00 16.00 2011-2012
1985 355 ACT_SCI 2012-07-02 00:00:00 23.00 2011-2012
7798 355 ACT_MATH 2012-08-13 00:00:00 15.00 2011-2012
7798 355 ACT_RDG 2012-08-13 00:00:00 17.00 2011-2012
7798 355 ACT_ENG 2012-08-13 00:00:00 16.00 2011-2012
7798 355 ACT_SCI 2012-08-13 00:00:00 23.00 2011-2012
5732 344 ACT_MATH 2012-05-02 00:00:00 15.00 2011-2012
5732 344 ACT_RDG 2012-05-02 00:00:00 17.00 2011-2012
5732 344 ACT_ENG 2012-05-02 00:00:00 16.00 2011-2012
5732 344 ACT_SCI 2012-05-02 00:00:00 23.00 2011-2012
2735 344 ACT_MATH 2012-08-14 00:00:00 15.00 2012-2013
2735 344 ACT_RDG 2012-08-14 00:00:00 16.00 2012-2013
2735 344 ACT_ENG 2012-08-14 00:00:00 15.00 2012-2013
2735 344 ACT_SCI 2012-08-14 00:00:00 21.00 2012-2013
2735 344 ACT_MATH 2012-08-14 00:00:00 15.00 2012-2013
4343 355 ACT_ENG 2011-06-02 00:00:00 16.00 2010-2011
4343 355 ACT_SCI 2011-06-02 00:00:00 23.00 2010-2011
6831 344 ACT_ENG 2011-09-12 00:00:00 16.00 2011-2012
6831 344 ACT_SCI 2011-09-12 00:00:00 23.00 2011-2012
8343 355 ACT_ENG 2010-08-18 00:00:00 16.00 2010-2011
8343 355 ACT_SCI 2010-08-18 00:00:00 23.00 2010-2011
9831 344 ACT_ENG 2010-09-16 00:00:00 16.00 2010-2011
9831 344 ACT_SCI 2010-09-16 00:00:00 23.00 2010-2011

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2012-10-29 : 16:57:19

Thank you both. this gets me the desired results.

Now I just need to test it against Production data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 17:01:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources