Calculate number of days missed per term

Question

Post reply

Calculate number of days missed per term

Sqlraider

SSCertifiable

Points: 5441
More actions
April 2, 2013 at 11:38 am

#292031

Need to update a new column (DaysMissed).
Trying to calculate the number of days missed per term (accumulative) based on the term end date.
Terms table contains the EndDate of each Term per LocID for each year.
TestScores contains the Test taken with the score and what Term it was taken.
Attend contains the PeriodNBR and the date (DateABS (date absent)). One day missed equals 8 periods, so the calculation is: DaysMissed = COUNT(DateABS)/8. In other words how many sets of 8 periods missed by the Term EndDate. This table is empty at the begining of a school year.
Table creates, ddl, sample data:
CREATE TABLE TestScores
(
SchoolYRCHAR(9)not null,
IDINTnot null,
LocIDCHAR(4)not null,
TestNameVARCHAR(30)not null,
TermTINYINTnot null,
DaysMissedTINYINTnot null,
Scoredecimal(6,2)not null
)
GO
INSERT TestScores
(SchoolYR, ID, LocID, TestName, Term, DaysMissed, Score)
VALUES
('2012-2013', 414, '355', 'CSA 1-2', 1, 0, 21.00),
('2012-2013', 414, '355', 'CSA 2-2', 2, 0, 23.00),
('2012-2013', 414, '355', 'CSA 3-1', 3, 0, 16.00),
('2012-2013', 414, '355', 'CSA 4-1', 4, 0, 16.00),
('2012-2013', 414, '355', 'CSA 4-2', 4, 0, 23.00),
('2012-2013', 450, '355', 'CSA 1-1', 1, 0, 17.00),
('2012-2013', 450, '355', 'CSA 2-3', 2, 0, 16.00),
('2012-2013', 450, '355', 'CSA 3-2', 3, 0, 17.00),
('2012-2013', 450, '355', 'CSA 3-3', 3, 0, 16.00),
('2012-2013', 450, '355', 'CSA 4-1', 4, 0, 15.00),
('2012-2013', 450, '355', 'CSA 4-2', 4, 0, 17.00),
('2012-2013', 450, '355', 'CSA 4-3', 4, 0, 16.00),
('2012-2013', 450, '355', 'CSA 4-4', 4, 0, 23.00),
('2012-2013', 450, '355', 'CSA 3-1', 3, 0, 16.00),
('2012-2013', 450, '355', 'CSA 1-2', 1, 0, 23.00),
('2012-2013', 451, '355', 'CSA 1-1', 1, 0, 15.00),
('2012-2013', 451, '355', 'CSA 2-2', 2, 0, 17.00),
('2012-2013', 451, '355', 'CSA 2-3', 2, 0, 16.00),
('2012-2013', 451, '355', 'CSA 2-4', 2, 0, 23.00),
('2012-2013', 451, '355', 'CSA 3-1', 3, 0, 15.00),
('2012-2013', 451, '355', 'CSA 3-2', 3, 0, 17.00),
('2012-2013', 451, '355', 'CSA 3-3', 3, 0, 16.00),
('2012-2013', 451, '355', 'CSA 3-4', 3, 0, 23.00),
('2012-2013', 451, '355', 'CSA 4-1', 4, 0, 15.00),
('2012-2013', 451, '355', 'CSA 4-2', 4, 0, 17.00),
('2012-2013', 451, '355', 'CSA 4-3', 4, 0, 16.00),
('2012-2013', 451, '355', 'CSA 4-4', 4, 0, 23.00),
('2012-2013', 717, '344', 'CSA 4-1', 4, 0, 15.00),
('2012-2013', 717, '344', 'CSA 4-2', 4, 0, 17.00),
('2012-2013', 717, '344', 'CSA 4-3', 4, 0, 16.00),
('2012-2013', 717, '344', 'CSA 4-4', 4, 0, 23.00),
('2012-2013', 922, '344', 'CSA 4-1', 4, 0, 15.00),
('2012-2013', 922, '344', 'CSA 4-2', 4, 0, 16.00),
('2012-2013', 2735, '344', 'CSA 4-1', 4, 0, 15.00),
('2012-2013', 2735, '344', 'CSA 4-2', 4, 0, 21.00),
('2012-2013', 2735, '344', 'CSA 4-3', 4, 0, 15.00),
('2012-2013', 4343, '355', 'CSA 4-2', 4, 0, 16.00),
('2012-2013', 4343, '355', 'CSA 4-3', 4, 0, 23.00),
('2012-2013', 6831, '344', 'CSA 4-3', 4, 0, 16.00),
('2012-2013', 6831, '344', 'CSA 4-4', 4, 0, 23.00),
('2012-2013', 8343, '355', 'CSA 4-1', 4, 0, 16.00),
('2012-2013', 8343, '355', 'CSA 4-2', 4, 0, 23.00),
('2012-2013', 9831, '344', 'CSA 4-2', 4, 0, 16.00),
('2012-2013', 9831, '344', 'CSA 4-3', 4, 0, 23.00)
GO
CREATE TABLE Terms
(
SchoolYRCHAR(9)not null,
LocIDCHAR(4)not null,
TermTINYINTnot null,
EndDateDATEnotnull
)
GO
INSERT Terms
(SchoolYR, Term, LocID, EndDate)
VALUES
('2012-2013', 1, '355', '2012-10-12'),
('2012-2013', 2, '355', '2012-12-20'),
('2012-2013', 3, '355', '2013-03-07'),
('2012-2013', 4, '355', '2013-05-24'),
('2012-2013', 1, '344', '2012-10-12'),
('2012-2013', 2, '344', '2012-12-20'),
('2012-2013', 3, '344', '2013-03-07'),
('2012-2013', 4, '344', '2013-05-24')
GO
CREATE TABLE Attend
(
IDINTnot null,
PeriodNBRTinyintnot null,
DateABSSMALLDATETIMEnotnull
)
GO
INSERT Attend
(ID, PeriodNBR, DateABS)
VALUES
(410, 8, '2013-01-03 00:00:00'),
(414, 8, '2013-01-03 00:00:00'),
(414, 7, '2013-01-03 00:00:00'),
(414, 6, '2013-01-03 00:00:00'),
(414, 5, '2013-01-03 00:00:00'),
(414, 4, '2013-01-03 00:00:00'),
(414, 3, '2013-01-03 00:00:00'),
(414, 2, '2013-01-03 00:00:00'),
(414, 1, '2013-01-03 00:00:00'),
(414, 6, '2012-12-19 00:00:00'),
(414, 5, '2012-12-19 00:00:00'),
(414, 6, '2012-12-12 00:00:00'),
(414, 5, '2012-12-12 00:00:00'),
(414, 4, '2012-12-12 00:00:00'),
(414, 3, '2012-12-12 00:00:00'),
(414, 2, '2012-12-12 00:00:00'),
(414, 7, '2012-11-14 00:00:00'),
(414, 7, '2012-08-15 00:00:00'),
(450, 8, '2013-03-21 00:00:00'),
(450, 7, '2013-03-21 00:00:00'),
(450, 1, '2013-03-15 00:00:00'),
(450, 2, '2013-03-14 00:00:00'),
(450, 1, '2013-03-14 00:00:00'),
(450, 8, '2013-03-12 00:00:00'),
(450, 7, '2013-03-12 00:00:00'),
(450, 6, '2013-03-12 00:00:00'),
(450, 5, '2013-03-12 00:00:00'),
(450, 4, '2013-03-12 00:00:00'),
(450, 3, '2013-03-12 00:00:00'),
(450, 2, '2013-03-12 00:00:00'),
(450, 1, '2013-03-12 00:00:00'),
(450, 1, '2013-03-04 00:00:00'),
(450, 1, '2013-02-19 00:00:00'),
(450, 1, '2013-02-04 00:00:00'),
(450, 1, '2013-01-31 00:00:00'),
(450, 8, '2013-01-29 00:00:00'),
(450, 7, '2013-01-29 00:00:00'),
(450, 6, '2013-01-29 00:00:00'),
(450, 5, '2013-01-29 00:00:00'),
(450, 4, '2013-01-29 00:00:00'),
(450, 3, '2013-01-29 00:00:00'),
(450, 2, '2013-01-29 00:00:00'),
(450, 1, '2013-01-29 00:00:00'),
(450, 8, '2013-01-28 00:00:00'),
(450, 7, '2013-01-28 00:00:00'),
(450, 6, '2013-01-28 00:00:00'),
(450, 5, '2013-01-28 00:00:00'),
(450, 4, '2013-01-28 00:00:00'),
(450, 3, '2013-01-28 00:00:00'),
(450, 2, '2013-01-28 00:00:00'),
(450, 1, '2013-01-28 00:00:00'),
(450, 8, '2013-01-25 00:00:00'),
(450, 7, '2013-01-25 00:00:00'),
(450, 6, '2013-01-25 00:00:00'),
(450, 5, '2013-01-25 00:00:00'),
(450, 4, '2013-01-25 00:00:00'),
(450, 3, '2013-01-25 00:00:00'),
(450, 2, '2013-01-25 00:00:00'),
(450, 1, '2013-01-25 00:00:00'),
(450, 1, '2013-01-24 00:00:00'),
(450, 1, '2013-01-22 00:00:00'),
(450, 1, '2013-01-14 00:00:00'),
(450, 1, '2012-12-18 00:00:00'),
(450, 8, '2012-12-14 00:00:00'),
(450, 7, '2012-12-14 00:00:00'),
(450, 6, '2012-12-14 00:00:00'),
(450, 5, '2012-12-14 00:00:00'),
(450, 4, '2012-12-14 00:00:00'),
(450, 3, '2012-12-14 00:00:00'),
(450, 2, '2012-12-14 00:00:00'),
(450, 1, '2012-12-14 00:00:00'),
(450, 8, '2012-11-30 00:00:00'),
(450, 7, '2012-11-30 00:00:00'),
(450, 6, '2012-11-30 00:00:00'),
(450, 5, '2012-11-30 00:00:00'),
(450, 2, '2012-11-16 00:00:00'),
(450, 1, '2012-11-16 00:00:00'),
(450, 8, '2012-11-09 00:00:00'),
(450, 7, '2012-11-09 00:00:00'),
(450, 6, '2012-11-09 00:00:00'),
(450, 5, '2012-11-09 00:00:00'),
(450, 4, '2012-11-09 00:00:00'),
(450, 3, '2012-11-09 00:00:00'),
(450, 2, '2012-11-09 00:00:00'),
(450, 1, '2012-11-09 00:00:00'),
(450, 1, '2012-11-01 00:00:00'),
(450, 1, '2012-10-30 00:00:00'),
(450, 7, '2012-10-24 00:00:00'),
(450, 6, '2012-10-24 00:00:00'),
(450, 5, '2012-10-24 00:00:00'),
(450, 1, '2012-10-24 00:00:00'),
(450, 8, '2012-10-16 00:00:00'),
(450, 7, '2012-10-16 00:00:00'),
(450, 6, '2012-10-16 00:00:00'),
(450, 5, '2012-10-16 00:00:00'),
(450, 4, '2012-10-16 00:00:00'),
(450, 3, '2012-10-16 00:00:00'),
(450, 2, '2012-10-16 00:00:00'),
(450, 1, '2012-10-16 00:00:00'),
(450, 7, '2012-10-10 00:00:00'),
(450, 8, '2012-10-09 00:00:00'),
(450, 7, '2012-10-09 00:00:00'),
(450, 6, '2012-10-09 00:00:00'),
(450, 5, '2012-10-09 00:00:00'),
(450, 4, '2012-10-09 00:00:00'),
(450, 3, '2012-10-09 00:00:00'),
(450, 2, '2012-10-09 00:00:00'),
(450, 1, '2012-10-09 00:00:00'),
(450, 8, '2012-10-08 00:00:00'),
(450, 7, '2012-10-08 00:00:00'),
(450, 6, '2012-10-08 00:00:00'),
(450, 5, '2012-10-08 00:00:00'),
(450, 4, '2012-10-08 00:00:00'),
(450, 3, '2012-10-08 00:00:00'),
(450, 2, '2012-10-08 00:00:00'),
(450, 1, '2012-10-08 00:00:00'),
(450, 8, '2012-10-05 00:00:00'),
(450, 7, '2012-10-05 00:00:00'),
(450, 6, '2012-10-05 00:00:00'),
(450, 5, '2012-10-05 00:00:00'),
(450, 4, '2012-10-05 00:00:00'),
(450, 3, '2012-10-05 00:00:00'),
(450, 2, '2012-10-05 00:00:00'),
(450, 1, '2012-10-05 00:00:00'),
(450, 8, '2012-10-04 00:00:00'),
(450, 7, '2012-10-04 00:00:00'),
(450, 6, '2012-10-04 00:00:00'),
(450, 5, '2012-10-04 00:00:00'),
(450, 4, '2012-10-04 00:00:00'),
(450, 3, '2012-10-04 00:00:00'),
(450, 2, '2012-10-04 00:00:00'),
(450, 1, '2012-10-04 00:00:00'),
(450, 8, '2012-10-03 00:00:00'),
(450, 7, '2012-10-03 00:00:00'),
(450, 6, '2012-10-03 00:00:00'),
(450, 5, '2012-10-03 00:00:00'),
(450, 4, '2012-10-03 00:00:00'),
(450, 3, '2012-10-03 00:00:00'),
(450, 2, '2012-10-03 00:00:00'),
(450, 1, '2012-10-03 00:00:00'),
(450, 8, '2012-10-02 00:00:00'),
(450, 7, '2012-10-02 00:00:00'),
(450, 6, '2012-10-02 00:00:00'),
(450, 5, '2012-10-02 00:00:00'),
(450, 4, '2012-10-02 00:00:00'),
(450, 3, '2012-10-02 00:00:00'),
(450, 2, '2012-10-02 00:00:00'),
(450, 1, '2012-10-02 00:00:00'),
(450, 8, '2012-10-01 00:00:00'),
(450, 7, '2012-10-01 00:00:00'),
(450, 6, '2012-10-01 00:00:00'),
(450, 5, '2012-10-01 00:00:00'),
(450, 4, '2012-10-01 00:00:00'),
(450, 3, '2012-10-01 00:00:00'),
(450, 2, '2012-10-01 00:00:00'),
(450, 1, '2012-10-01 00:00:00'),
(450, 1, '2012-09-18 00:00:00'),
(450, 7, '2012-09-05 00:00:00'),
(450, 6, '2012-09-05 00:00:00'),
(450, 8, '2012-08-24 00:00:00'),
(450, 7, '2012-08-24 00:00:00'),
(451, 8, '2013-03-21 00:00:00'),
(451, 7, '2013-03-21 00:00:00'),
(451, 8, '2013-03-18 00:00:00'),
(451, 7, '2013-03-18 00:00:00'),
(451, 6, '2013-03-18 00:00:00'),
(451, 5, '2013-03-18 00:00:00'),
(451, 4, '2013-03-18 00:00:00'),
(451, 3, '2013-03-18 00:00:00'),
(451, 2, '2013-03-18 00:00:00'),
(451, 1, '2013-03-18 00:00:00'),
(451, 1, '2013-03-15 00:00:00'),
(451, 2, '2013-03-14 00:00:00'),
(451, 1, '2013-03-14 00:00:00'),
(451, 1, '2013-02-19 00:00:00'),
(451, 8, '2013-02-11 00:00:00'),
(451, 7, '2013-02-11 00:00:00'),
(451, 6, '2013-02-11 00:00:00'),
(451, 5, '2013-02-11 00:00:00'),
(451, 4, '2013-02-11 00:00:00'),
(451, 3, '2013-02-11 00:00:00'),
(451, 2, '2013-02-11 00:00:00'),
(451, 1, '2013-02-11 00:00:00'),
(451, 8, '2013-02-04 00:00:00'),
(451, 7, '2013-02-04 00:00:00'),
(451, 6, '2013-02-04 00:00:00'),
(451, 5, '2013-02-04 00:00:00'),
(451, 4, '2013-02-04 00:00:00'),
(451, 3, '2013-02-04 00:00:00'),
(451, 2, '2013-02-04 00:00:00'),
(451, 1, '2013-02-04 00:00:00'),
(451, 1, '2013-01-31 00:00:00'),
(451, 2, '2013-01-28 00:00:00'),
(451, 1, '2013-01-28 00:00:00'),
(451, 1, '2013-01-24 00:00:00'),
(451, 1, '2013-01-22 00:00:00'),
(451, 8, '2013-01-16 00:00:00'),
(451, 7, '2013-01-16 00:00:00'),
(451, 6, '2013-01-16 00:00:00'),
(451, 5, '2013-01-16 00:00:00'),
(451, 4, '2013-01-16 00:00:00'),
(451, 3, '2013-01-16 00:00:00'),
(451, 2, '2013-01-16 00:00:00'),
(451, 1, '2013-01-16 00:00:00'),
(451, 8, '2013-01-15 00:00:00'),
(451, 7, '2013-01-15 00:00:00'),
(451, 6, '2013-01-15 00:00:00'),
(451, 5, '2013-01-15 00:00:00'),
(451, 4, '2013-01-15 00:00:00'),
(451, 3, '2013-01-15 00:00:00'),
(451, 2, '2013-01-15 00:00:00'),
(451, 1, '2013-01-15 00:00:00'),
(451, 1, '2013-01-14 00:00:00'),
(451, 8, '2012-12-14 00:00:00'),
(451, 7, '2012-12-14 00:00:00'),
(451, 6, '2012-12-14 00:00:00'),
(451, 5, '2012-12-14 00:00:00'),
(451, 4, '2012-12-14 00:00:00'),
(451, 3, '2012-12-14 00:00:00'),
(451, 2, '2012-12-14 00:00:00'),
(451, 1, '2012-12-14 00:00:00'),
(451, 1, '2012-12-04 00:00:00'),
(451, 8, '2012-11-30 00:00:00'),
(451, 7, '2012-11-30 00:00:00'),
(451, 6, '2012-11-30 00:00:00'),
(451, 5, '2012-11-30 00:00:00'),
(451, 7, '2012-11-28 00:00:00'),
(451, 6, '2012-11-28 00:00:00'),
(451, 5, '2012-11-28 00:00:00'),
(451, 7, '2012-11-27 00:00:00'),
(451, 8, '2012-11-26 00:00:00'),
(451, 7, '2012-11-26 00:00:00'),
(451, 6, '2012-11-26 00:00:00'),
(451, 5, '2012-11-26 00:00:00'),
(451, 4, '2012-11-26 00:00:00'),
(451, 3, '2012-11-26 00:00:00'),
(451, 2, '2012-11-26 00:00:00'),
(451, 1, '2012-11-26 00:00:00'),
(451, 2, '2012-11-16 00:00:00'),
(451, 1, '2012-11-16 00:00:00'),
(451, 8, '2012-11-02 00:00:00'),
(451, 7, '2012-11-02 00:00:00'),
(451, 6, '2012-11-02 00:00:00'),
(451, 5, '2012-11-02 00:00:00'),
(451, 4, '2012-11-02 00:00:00'),
(451, 3, '2012-11-02 00:00:00'),
(451, 2, '2012-11-02 00:00:00'),
(451, 1, '2012-11-02 00:00:00'),
(451, 8, '2012-11-01 00:00:00'),
(451, 7, '2012-11-01 00:00:00'),
(451, 6, '2012-11-01 00:00:00'),
(451, 1, '2012-11-01 00:00:00'),
(451, 1, '2012-10-30 00:00:00'),
(451, 7, '2012-10-26 00:00:00'),
(451, 1, '2012-10-24 00:00:00'),
(451, 7, '2012-10-10 00:00:00'),
(451, 8, '2012-10-09 00:00:00'),
(451, 7, '2012-10-09 00:00:00'),
(451, 6, '2012-10-09 00:00:00'),
(451, 5, '2012-10-09 00:00:00'),
(451, 4, '2012-10-09 00:00:00'),
(451, 3, '2012-10-09 00:00:00'),
(451, 2, '2012-10-09 00:00:00'),
(451, 1, '2012-10-09 00:00:00'),
(451, 8, '2012-10-08 00:00:00'),
(451, 7, '2012-10-08 00:00:00'),
(451, 6, '2012-10-08 00:00:00'),
(451, 5, '2012-10-08 00:00:00'),
(717, 8, '2013-01-03 00:00:00'),
(717, 7, '2013-01-03 00:00:00'),
(717, 6, '2013-01-03 00:00:00'),
(717, 5, '2013-01-03 00:00:00'),
(717, 4, '2013-01-03 00:00:00'),
(717, 3, '2013-01-03 00:00:00'),
(717, 2, '2013-01-03 00:00:00'),
(717, 1, '2013-01-03 00:00:00'),
(717, 8, '2012-12-19 00:00:00')
GO

Here is code I've been trying to combine without any luck:
select ID, DaysMissed = COUNT(DateABS)/8
from Attend
where DateABS <= '2013-05-24' --the Terms.EndDate
group by ID
order by ID
select s.ID, s.TestName, s.Term, s.Daysmissed,
t.EndDate
from TestScores as s
join Terms as t
on s.SchoolYR = t.SchoolYR
and s.Term = t.Term
and s.LocID = t.LocID
where s.Term in (1, 2, 3, 4)
The days missed is accumulative per the school year.
The desired output should look like this:
IDTestNameTermDaysmissedEndDate
414CSA 1-2102012-10-12
414CSA 2-2212012-12-20
414CSA 3-1322013-03-07
414CSA 4-1422013-05-24
414CSA 4-2422013-05-24
450CSA 1-1172012-10-12
450CSA 2-32122012-12-20
450CSA 3-23162013-03-07
450CSA 3-33162013-03-07
450CSA 4-14172013-05-24
450CSA 4-24172013-05-24
450CSA 4-34172013-05-24
450CSA 4-44172013-05-24
450CSA 3-13162013-03-07
450CSA 1-2172012-10-12
451CSA 1-1112012-10-12
451CSA 2-2262012-12-20
451CSA 2-3262012-12-20
451CSA 2-4262012-12-20
451CSA 3-13112013-03-07
451CSA 3-23112013-03-07
451CSA 3-33112013-03-07
451CSA 3-43112013-03-07
451CSA 4-14132013-05-24
451CSA 4-2413 2013-05-24
451CSA 4-34132013-05-24
451CSA 4-44132013-05-24
717CSA 4-1412013-05-24
717CSA 4-2412013-05-24
717CSA 4-3412013-05-24
717CSA 4-4412013-05-24
922CSA 4-1402013-05-24
922CSA 4-2402013-05-24
2735CSA 4-1402013-05-24
2735CSA 4-2402013-05-24
2735CSA 4-3402013-05-24
4343CSA 4-2402013-05-24
4343CSA 4-3402013-05-24
6831CSA 4-3402013-05-24
6831CSA 4-4402013-05-24
8343CSA 4-1402013-05-24
8343CSA 4-2402013-05-24
9831CSA 4-2402013-05-24
9831CSA 4-3402013-05-24
Thanks,
Sqlraider

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLCurious SSCrazy Points: 2339 More actions · Answer 1

Seems quite interesting. Could you provide the following additional info?

What is the from date for a term?

How do you account for fractions? In other words, if a student missed a total 15 periods in a term, how many days would be calculated as?

- Rex

Sqlraider SSCertifiable Points: 5441 More actions · Answer 2

RexHelios (4/2/2013)
Seems quite interesting. Could you provide the following additional info?
What is the from date for a term?

Not sure what you are asking here. You get the EndDate (Date datatype) from the Terms table based on the SchoolYR, LocID, Term from the TestScores.

How do you account for fractions? In other words, if a student missed a total 15 periods in a term, how many days would be calculated as?
- Rex

Fractions are dropped off, you must have 8 periods for it to be a day missed. A toal of 15 periods in a term would = 1 day (COUNT(DateABS)/8) would give you 1.

vinu512 SSCoach Points: 15729 More actions · Answer 3

Is there a relationship between the "Attend" table with the "Terms" table??.....

By what you have shown in the Expected Output it sure seems there is but its not visible in the sample that you have provided.

In the sample data you have provided, there is no column in the "Attend" Table other than "ID" column which links it to the Terms Column............then how do you know, just on the basis of ID, which data belongs to what Term or what Test??

How did you get to the expected result that you posted??

From what I understood, the "Days Missed" column in your expected results data is according to Term and/or Test, so to get to that there should be some relationship between the "Attend" and the "Terms" tables.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

ralm Hall of Fame Points: 3156 More actions · Answer 4

Yes as Vinu said we need a relationship between Term and Attend.

I have tried the below query and it is not exactly matching your requirement but it might help you...

Select A.ID, S.TestName,T.Term,SUM(A.PeriodNBR)/8

from dbo.TestScores S

join dbo.Attend A

on A.ID = S.ID

join dbo.Terms T

on S.SchoolYR = T.SchoolYR

and S.Term = T.Term

and S.LocID = T.LocID

left join dbo.Terms T1

on S.SchoolYR = T1.SchoolYR

and S.Term = T1.Term+1

and S.LocID = T1.LocID

Where A.DateABS between case T.term when 1 then '2012-06-01' else T1.EndDate End and T.EndDate

group by A.ID,T.Term,S.TestName

^{[font="Verdana"]Regards,
Rals[/font].}

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Thanks for the ddl and dml, it makes a real difference. I'd recommend you split this into two steps. It will make the end result easier to understand, test and maintain than writing it as one query. Step 1 would be calculating the missing days, step 2 would be performing a running totals of the missing days and updating the new column. Here's Step 1. Check that the output is what you are expecting to see;

-- Step 1 Calculate missing days

SELECT s.*,

x.StartDate,

t.EndDate,

y.Missed

FROM TestScores s

INNER JOIN Terms t -- collect term end date

ON t.SchoolYR = s.SchoolYR

AND t.LocID = s.LocID

AND t.Term = s.Term

OUTER APPLY ( -- calculate a suitable term start date

SELECT StartDate = DATEADD(day,1,i.EndDate)

FROM Terms i

WHERE i.SchoolYR = t.SchoolYr

AND i.LocID = t.LocID

AND i.Term = t.Term-1

) x

CROSS APPLY ( -- aggregate missed days per ID / term

SELECT Missed = COUNT(*)/8

FROM Attend a

WHERE a.ID = s.ID

AND a.DateABS BETWEEN ISNULL(x.StartDate,'19000101') AND t.EndDate

) y

ORDER BY s.SchoolYR, s.ID, s.Term

^{“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

ralm Hall of Fame Points: 3156 More actions · Answer 6

Nice one Chris..

I Understood (Hope) the requirement after seeing the query from Chris... Still not able to bring all the rows.. Not able to get the ID's doesn't have any records in Attend table

Updated Query:

Select A.ID, S.TestName,T.Term, T.EndDate,COUNT(A.PeriodNBR)/8 MissingDays

from dbo.TestScores S

join dbo.Attend A

on A.ID = S.ID

join dbo.Terms T

on S.SchoolYR = T.SchoolYR

and S.Term = T.Term

and S.LocID = T.LocID

left join dbo.Terms T1

on S.SchoolYR = T1.SchoolYR

and S.Term = T1.Term+1

and S.LocID = T1.LocID

Where A.DateABS between ISNULL (Dateadd(day,1,T1.EndDate),'2012-06-01') and T.EndDate

group by A.ID,T.Term,S.TestName,T.EndDate

^{[font="Verdana"]Regards,
Rals[/font].}

Sqlraider SSCertifiable Points: 5441 More actions · Answer 7

vinu512 (4/2/2013)
Is there a relationship between the "Attend" table with the "Terms" table??.....
By what you have shown in the Expected Output it sure seems there is but its not visible in the sample that you have provided.
In the sample data you have provided, there is no column in the "Attend" Table other than "ID" column which links it to the Terms Column............then how do you know, just on the basis of ID, which data belongs to what Term or what Test??
How did you get to the expected result that you posted??
From what I understood, the "Days Missed" column in your expected results data is according to Term and/or Test, so to get to that there should be some relationship between the "Attend" and the "Terms" tables.

Sorry I forgot to state the relationships of the tables. In setting up test data I pared these tables down to only have columns needed to calculate the days missed.

Remember that Attend contains data for the current school year and is reset (emptied) at the beginning of each year.

TestScores is the 'driver' table. I'll be selecting from this table "where TestName like 'CSA%' ". From the TestScores you link to Terms via TestScores.SchoolYR, LocID, Term to get the EndDate. Also from the TestScores you link to Attend via the ID (on TestScore.ID = Attend.ID).

Based on the Term the test was taken you get the end date (EndDate) of that term and use it in the calculation.

I can do this in two seperate queries using a temp table for each term but want to do this in one query if possible.

Here's the two query method that may help you understand the relationship better:

select ID, Missed = COUNT(dateabs)/8

from Attend

where dateabs <= '2012-10-12' --term 1 end date

group by ID

order by ID

I'd load this into a temp table and then run the following query to Update the DaysMissed.

update TestScores

set DaysMissed = Missed

from TestScores as s

join #Temp as t

on s.ID = t.ID

where s.TestName like 'CSA%'

and s.Term = 1

Then do this process for each Term.

Sqlraider SSCertifiable Points: 5441 More actions · Answer 8

I'm in the process of testing the queries supplied by Rals and Chris.

I'll post back my findings.

Thanks again everyone,

Sqlraider

Sqlraider SSCertifiable Points: 5441 More actions · Answer 9

ChrisM@Work (4/3/2013)
Thanks for the ddl and dml, it makes a real difference. I'd recommend you split this into two steps. It will make the end result easier to understand, test and maintain than writing it as one query. Step 1 would be calculating the missing days, step 2 would be performing a running totals of the missing days and updating the new column. Here's Step 1. Check that the output is what you are expecting to see;
-- Step 1 Calculate missing days
SELECT s.*,
x.StartDate,
t.EndDate,
y.Missed
FROM TestScores s
INNER JOIN Terms t -- collect term end date
ON t.SchoolYR = s.SchoolYR
AND t.LocID = s.LocID
AND t.Term = s.Term
OUTER APPLY ( -- calculate a suitable term start date
SELECT StartDate = DATEADD(day,1,i.EndDate)
FROM Terms i
WHERE i.SchoolYR = t.SchoolYr
AND i.LocID = t.LocID
AND i.Term = t.Term-1
) x
CROSS APPLY ( -- aggregate missed days per ID / term
SELECT Missed = COUNT(*)/8
FROM Attend a
WHERE a.ID = s.ID
AND a.DateABS BETWEEN ISNULL(x.StartDate,'19000101') AND t.EndDate
) y
ORDER BY s.SchoolYR, s.ID, s.Term

Chris,

This is close. But you are calculating the days missed per term and I need it to be accumulative for the year.

I can get the first day of school and pass it to your query, but I'm not sure how to incorporate it into the query.

Here is how I would pass it:

Declare @FirstDayDate Date

Set @FirstDayDate = '2012-08-14'

Thanks,

Sqlraider

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

Sqlraider SSCertifiable Points: 5441 More actions · Answer 11

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:

SchoolYRIDLocIDTestNameTermDaysMissedScoreStartDateEndDateMissed

2012-2013414355 CSA 1-21021.00NULL2012-10-120

2012-2013414355 CSA 2-22023.002012-10-132012-12-201

2012-2013414355 CSA 3-13016.002012-12-212013-03-071

2012-2013414355 CSA 4-14016.002013-03-082013-05-240

2012-2013414355 CSA 4-24023.002013-03-082013-05-240

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 SSCertifiable Points: 5441 More actions · Answer 12

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