Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 25th, 2013, 03:38 AM
Registered User
 
Join Date: Jan 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default help with a mulitiple date query

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
 
Old May 10th, 2013, 02:18 PM
Registered User
 
Join Date: May 2013
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old May 11th, 2013, 03:10 AM
Registered User
 
Join Date: Jan 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Mulitiple/Nested IIF statements rohit_ghosh Access VBA 3 June 1st, 2007 10:44 AM
Passing Mulitiple pameters to a form query c_olinrb Access 3 January 19th, 2007 03:25 PM
mulitiple field query PLUS Durwood Edwards Classic ASP Databases 2 July 14th, 2005 10:54 AM
Date based query when date is nvarchar MichaelTJ SQL Language 4 January 12th, 2004 09:57 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.