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 Tuesday, April 2, 2013 11:38 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 1,460, Visits: 1,890
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
(
SchoolYR CHAR(9) not null,
ID INT not null,
LocID CHAR(4) not null,
TestName VARCHAR(30) not null,
Term TINYINT not null,
DaysMissed TINYINT not null,
Score decimal(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
(
SchoolYR CHAR(9) not null,
LocID CHAR(4) not null,
Term TINYINT not null,
EndDate DATE not null
)
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
(
ID INT not null,
PeriodNBR Tinyint not null,
DateABS SMALLDATETIME not null

)
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:
 
ID TestName Term Daysmissed EndDate
414 CSA 1-2 1 0 2012-10-12
414 CSA 2-2 2 1 2012-12-20
414 CSA 3-1 3 2 2013-03-07
414 CSA 4-1 4 2 2013-05-24
414 CSA 4-2 4 2 2013-05-24
450 CSA 1-1 1 7 2012-10-12
450 CSA 2-3 2 12 2012-12-20
450 CSA 3-2 3 16 2013-03-07
450 CSA 3-3 3 16 2013-03-07
450 CSA 4-1 4 17 2013-05-24
450 CSA 4-2 4 17 2013-05-24
450 CSA 4-3 4 17 2013-05-24
450 CSA 4-4 4 17 2013-05-24
450 CSA 3-1 3 16 2013-03-07
450 CSA 1-2 1 7 2012-10-12
451 CSA 1-1 1 1 2012-10-12
451 CSA 2-2 2 6 2012-12-20
451 CSA 2-3 2 6 2012-12-20
451 CSA 2-4 2 6 2012-12-20
451 CSA 3-1 3 11 2013-03-07
451 CSA 3-2 3 11 2013-03-07
451 CSA 3-3 3 11 2013-03-07
451 CSA 3-4 3 11 2013-03-07
451 CSA 4-1 4 13 2013-05-24
451 CSA 4-2 4 13 2013-05-24
451 CSA 4-3 4 13 2013-05-24
451 CSA 4-4 4 13 2013-05-24
717 CSA 4-1 4 1 2013-05-24
717 CSA 4-2 4 1 2013-05-24
717 CSA 4-3 4 1 2013-05-24
717 CSA 4-4 4 1 2013-05-24
922 CSA 4-1 4 0 2013-05-24
922 CSA 4-2 4 0 2013-05-24
2735 CSA 4-1 4 0 2013-05-24
2735 CSA 4-2 4 0 2013-05-24
2735 CSA 4-3 4 0 2013-05-24
4343 CSA 4-2 4 0 2013-05-24
4343 CSA 4-3 4 0 2013-05-24
6831 CSA 4-3 4 0 2013-05-24
6831 CSA 4-4 4 0 2013-05-24
8343 CSA 4-1 4 0 2013-05-24
8343 CSA 4-2 4 0 2013-05-24
9831 CSA 4-2 4 0 2013-05-24
9831 CSA 4-3 4 0 2013-05-24


Thanks,
Sqlraider
Post #1438026
Posted Tuesday, April 2, 2013 12:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 30, 2014 12:23 PM
Points: 76, Visits: 292
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
Post #1438058
Posted Tuesday, April 2, 2013 1:08 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 1,460, Visits: 1,890
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.

Post #1438066
Posted Tuesday, April 2, 2013 11:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
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
Post #1438176
Posted Wednesday, April 3, 2013 1:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 24, 2014 2:51 PM
Points: 1,913, Visits: 795
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



Regards,
Rals
.
Post #1438192
Posted Wednesday, April 3, 2013 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1438196
Posted Wednesday, April 3, 2013 5:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 24, 2014 2:51 PM
Points: 1,913, Visits: 795
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



Regards,
Rals
.
Post #1438260
Posted Wednesday, April 3, 2013 7:57 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 1,460, Visits: 1,890
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.
Post #1438350
Posted Wednesday, April 3, 2013 8:00 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 1,460, Visits: 1,890
I'm in the process of testing the queries supplied by Rals and Chris.

I'll post back my findings.

Thanks again everyone,
Sqlraider
Post #1438353
Posted Wednesday, April 3, 2013 8:22 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 1,460, Visits: 1,890
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
Post #1438369
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse