Calculate number of days missed per term

  • 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

  • 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

  • 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.

  • 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] 😉

  • 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].
  • 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

  • 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].
  • 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.

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

    I'll post back my findings.

    Thanks again everyone,

    Sqlraider

  • 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

  • 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

  • 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?

  • 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

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

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