Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate number of days missed per term Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, April 2, 2013 11:38 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 6, 2016 10:16 AM Points: 2,142, Visits: 2,222
 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 )GOINSERT 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)GOCREATE TABLE Terms( SchoolYR CHAR(9) not null, LocID CHAR(4) not null, Term TINYINT not null, EndDate DATE not null)GOINSERT 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')GOCREATE TABLE Attend( ID INT not null, PeriodNBR Tinyint not null, DateABS SMALLDATETIME not null)GOINSERT 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)/8from Attend where DateABS <= '2013-05-24' --the Terms.EndDategroup by IDorder by IDselect s.ID, s.TestName, s.Term, s.Daysmissed,t.EndDatefrom TestScores as sjoin Terms as ton s.SchoolYR = t.SchoolYRand s.Term = t.Termand s.LocID = t.LocIDwhere 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 EndDate414 CSA 1-2 1 0 2012-10-12414 CSA 2-2 2 1 2012-12-20414 CSA 3-1 3 2 2013-03-07414 CSA 4-1 4 2 2013-05-24414 CSA 4-2 4 2 2013-05-24450 CSA 1-1 1 7 2012-10-12450 CSA 2-3 2 12 2012-12-20450 CSA 3-2 3 16 2013-03-07450 CSA 3-3 3 16 2013-03-07450 CSA 4-1 4 17 2013-05-24450 CSA 4-2 4 17 2013-05-24450 CSA 4-3 4 17 2013-05-24450 CSA 4-4 4 17 2013-05-24450 CSA 3-1 3 16 2013-03-07450 CSA 1-2 1 7 2012-10-12451 CSA 1-1 1 1 2012-10-12451 CSA 2-2 2 6 2012-12-20451 CSA 2-3 2 6 2012-12-20451 CSA 2-4 2 6 2012-12-20451 CSA 3-1 3 11 2013-03-07451 CSA 3-2 3 11 2013-03-07451 CSA 3-3 3 11 2013-03-07451 CSA 3-4 3 11 2013-03-07451 CSA 4-1 4 13 2013-05-24451 CSA 4-2 4 13 2013-05-24451 CSA 4-3 4 13 2013-05-24451 CSA 4-4 4 13 2013-05-24717 CSA 4-1 4 1 2013-05-24717 CSA 4-2 4 1 2013-05-24717 CSA 4-3 4 1 2013-05-24717 CSA 4-4 4 1 2013-05-24922 CSA 4-1 4 0 2013-05-24922 CSA 4-2 4 0 2013-05-242735 CSA 4-1 4 0 2013-05-242735 CSA 4-2 4 0 2013-05-242735 CSA 4-3 4 0 2013-05-244343 CSA 4-2 4 0 2013-05-244343 CSA 4-3 4 0 2013-05-246831 CSA 4-3 4 0 2013-05-246831 CSA 4-4 4 0 2013-05-248343 CSA 4-1 4 0 2013-05-248343 CSA 4-2 4 0 2013-05-249831 CSA 4-2 4 0 2013-05-249831 CSA 4-3 4 0 2013-05-24Thanks,Sqlraider
Post #1438026
 Posted Tuesday, April 2, 2013 12:48 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: 2 days ago @ 11:58 AM Points: 105, Visits: 436
 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
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 6, 2016 10:16 AM Points: 2,142, Visits: 2,222
 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?- RexFractions 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 Group: General Forum Members Last Login: Thursday, February 18, 2016 6:33 AM Points: 1,127, Visits: 1,616
 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 VijayanFor 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
 SSCrazy Group: General Forum Members Last Login: Friday, March 4, 2016 7:14 AM Points: 2,130, Visits: 834
 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)/8from dbo.TestScores Sjoin dbo.Attend A on A.ID = S.IDjoin dbo.Terms T on S.SchoolYR = T.SchoolYR and S.Term = T.Term and S.LocID = T.LocIDleft join dbo.Terms T1 on S.SchoolYR = T1.SchoolYR and S.Term = T1.Term+1 and S.LocID = T1.LocIDWhere A.DateABS between case T.term when 1 then '2012-06-01' else T1.EndDate End and T.EndDategroup by A.ID,T.Term,S.TestName Regards,Rals.
Post #1438192
 Posted Wednesday, April 3, 2013 1:43 AM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 9:41 AM Points: 8,605, Visits: 18,775
 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 daysSELECT s.*, x.StartDate, t.EndDate, y.Missed FROM TestScores sINNER 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) xCROSS 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) yORDER 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1438196
 Posted Wednesday, April 3, 2013 5:17 AM
 SSCrazy Group: General Forum Members Last Login: Friday, March 4, 2016 7:14 AM Points: 2,130, Visits: 834
 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 tableUpdated Query:Select A.ID, S.TestName,T.Term, T.EndDate,COUNT(A.PeriodNBR)/8 MissingDaysfrom dbo.TestScores Sjoin dbo.Attend A on A.ID = S.IDjoin dbo.Terms T on S.SchoolYR = T.SchoolYR and S.Term = T.Term and S.LocID = T.LocIDleft join dbo.Terms T1 on S.SchoolYR = T1.SchoolYR and S.Term = T1.Term+1 and S.LocID = T1.LocIDWhere A.DateABS between ISNULL (Dateadd(day,1,T1.EndDate),'2012-06-01') and T.EndDategroup by A.ID,T.Term,S.TestName,T.EndDate Regards,Rals.
Post #1438260
 Posted Wednesday, April 3, 2013 7:57 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 6, 2016 10:16 AM Points: 2,142, Visits: 2,222
 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)/8from Attend where dateabs <= '2012-10-12' --term 1 end dategroup by IDorder by ID I'd load this into a temp table and then run the following query to Update the DaysMissed.update TestScoresset DaysMissed = Missedfrom TestScores as sjoin #Temp as ton s.ID = t.IDwhere 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
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 6, 2016 10:16 AM Points: 2,142, Visits: 2,222
 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
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 6, 2016 10:16 AM Points: 2,142, Visits: 2,222
 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 daysSELECT s.*, x.StartDate, t.EndDate, y.Missed FROM TestScores sINNER 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) xCROSS 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) yORDER BY s.SchoolYR, s.ID, s.TermChris,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 DateSet @FirstDayDate = '2012-08-14' Thanks,Sqlraider
Post #1438369

 Permissions