Page Comparison

...

Download, install, and configure tSQLt to run on the Ed-Fi database as described in their getting started page.

Write a test in T-SQL.

Code Block

language	sql

EXEC tSQLt.NewTestClass 'testStudentAddress';
GO

CREATE PROCEDURE testStudentAddress.[test that every student has a home or physical address]
AS
BEGIN
    DECLARE @NoAddressCount INT;
    DECLARE @NoAddressThreshold INT; SET @NoAddressThreshold = 0;

    SELECT @NoAddressCount = count(distinct c.studentUSI)
	from edfi.student c
	JOIN edfi.StudentSchoolAssociation on c.studentUSI = StudentSchoolAssociation.StudentUSI
	LEFT JOIN 
		(SELECT studentUSI, b.CodeValue
		FROM edfi.studentAddress a
			JOIN edfi.AddressType b	on a.AddressTypeId = b.addressTypeId
		and b.CodeValue in ( 'Physical', 'Home') 
		) d on c.studentUSI = d.studentUSI;

	EXEC tSQLt.AssertEquals @NoAddressCount, @NoAddressThreshold;
END;
GO

Run the test
Code Block
language sql
EXEC tSQLt.Run 'testStudentAddress';

Review the results

Code Block

language	sql

1 row affected)
[testStudentAddress].[test that every student has a home or physical address] failed: (Failure) Expected: <21042> but was: <0>
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                                               |Dur(ms)|Result |
+--+-----------------------------------------------------------------------------+-------+-------+
|1 |[testStudentAddress].[test that every student has a home or physical address]|     36|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 27
Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

...

Versions Compared

Old Version 3

New Version 4

Key