help with a mulitiple date query

stevec6832 · January 25th, 2013, 03:38 AM

Hi this is my first post so would like to say hi to all.
I have a question which I have tried to resolve and am not getting anywhere fast.
Overview I work for a business where we send members of our team out to meet students, they have to meet the students on a regular basis and every 8 weeks provide a report. I have a table that takes the student start date and then calculates that date plus 56 days (8 weeks) to indicate when a report is due. When the actual report is done it is entered into a col and then the next report due date is calculated again as +56 days. We have our students for one year so a total of 15 cols for actual reports and the a virtual col for report due dates.
My problem is that not all our staff do reports on time if at all in some cases and the boss wants to clamp down on this. I need to generate a query that reports back when a report has not been done. Now this may sound simple but I have lost the plot here in that the initial start dates are different and I need to query 12 different cols to get an answer. For example student 1 may be half way through their course so on report 6 but student 2 has just started so only need to look at first report.
Is there a way I can build a query to look at the table and take the last report date that was populated and then use my calculation to see if there is a report missing. Problem being it needs to query through any of the 12 report fields as some may be populated and some are not.
To put it another way I want to perform a query where it looks at the last column entry made for each student which could be anyone of the 12 date columns and then check the date of that most recent column entry.
I can query each individual column but wondered is there a way to actually do it as just one query.
Many thanks

sduncan · May 10th, 2013, 02:18 PM

If I have what you are saying right, you could try something like this... And if not this may give you an idea of where to start looking at this. This also assumes the column is null, or has a date of '1900-1-1' until filled. You can change the criteria to you liking.

Code:

SELECT
    COLUMN1,
    COLUMN2,
    Q2.LAST_REPORT_DATE,
    CASE
       WHEN DATEADD(DAY, 56, Q2.LAST_REPORT_DATE) < GETDATE() THEN 'OVERDUE'
       WHEN DATEADD(DAY, 56, Q2.LAST_REPORT_DATE) = GETDATE() THEN 'DUE'
       WHEN DATEADD(DAY, 56, Q2.LAST_REPORT_DATE) > GETDATE() THEN 'NOT DUE'
       ELSE 'UNKNOWN PLEASE CHECK'
    END AS REPORT_STATUS
FROM TABLE1 Q1
    JOIN (
       SELECT
           CASE WHEN COLUMN12 IS NOT NULL AND COLUMN12 >= '1900-1-1' THEN COLUMN12
             WHEN COLUMN11 IS NOT NULL AND COLUMN11 >= '1900-1-1' THEN COLUMN11
             WHEN COLUMN10 IS NOT NULL AND COLUMN10 >= '1900-1-1' THEN COLUMN10
             WHEN COLUMN9 IS NOT NULL AND COLUMN9 >= '1900-1-1' THEN COLUMN9
             WHEN COLUMN8 IS NOT NULL AND COLUMN8 >= '1900-1-1' THEN COLUMN8
             WHEN COLUMN7 IS NOT NULL AND COLUMN7 >= '1900-1-1' THEN COLUMN7
             WHEN COLUMN6 IS NOT NULL AND COLUMN6 >= '1900-1-1' THEN COLUMN6
             WHEN COLUMN5 IS NOT NULL AND COLUMN5 >= '1900-1-1' THEN COLUMN5
             WHEN COLUMN4 IS NOT NULL AND COLUMN4 >= '1900-1-1' THEN COLUMN4
             WHEN COLUMN3 IS NOT NULL AND COLUMN3 >= '1900-1-1' THEN COLUMN3
             WHEN COLUMN2 IS NOT NULL AND COLUMN2 >= '1900-1-1' THEN COLUMN2
             WHEN COLUMN1 IS NOT NULL AND COLUMN1 >= '1900-1-1' THEN COLUMN1
             ELSE NULL
           END AS LAST_REPORT_ID,
           TABLE_KEY
      FROM TABLE2 T2       
    ) AS Q2 ON Q1.TABLE_KEY = Q2.TABLE_KEY

stevec6832 · May 11th, 2013, 03:10 AM

Many thanks for taking the time to reply, I di end up using a cross tab query to get around this but as I am sure many will remind me that they are not always stable especially when you are then combining a couple of queries.
Having read this it does make sense to me and will be trying it out in my code later this week. Many thanks for the reply and very much appreciated