0

I am attempting to flag a record that has gone into "Past Due" status in SQL. I calculate the "Past Due" status using a simple date calculation of Current Date-Due date and once the due date has passed it triggers a past due flag. My issue comes in that once this record has been past due once, my customer wants it to remain showing as past due. (They are able to change the due dates on the front end which when set in the future would make the record no longer past due.) Is there a way I can set up some sort of fixed flag so that once the Due Date > Current date the first time in the workflow that the record shows up as past due until it is closed.

Example for past due flag:

Select
    current_date as report_date
    ,issue_id
    ,issue_due_date
    ,case when report_date > issue_due_date then 'Current'
    when report_date < issue_due_date then 'Past Due'
From Table

However throughout the issue workflow, users are able to change the due date setting it to a date in the future, which would render the above query to 'Current'. I would like to figure out a way, that once an issue goes into 'Past Due' regardless of what happens to the record moving forward, it is always stay as 'Past Due'.

SQL: Redshift

CR7SMS
  • 2,520
  • 1
  • 5
  • 13
ashley
  • 1
  • 2
  • Database you use is ? Also some data as example to explain the situation - it would be a good start to help me understand the problem and try to help you. But ok, that is me.... – VBoka Feb 28 '20 at 19:42
  • Hi - using Redshift – ashley Feb 28 '20 at 19:45
  • Is this a query or a calculated column or something else? Can you provide any sample data or code? – Eliot K Feb 28 '20 at 19:55
  • @ashley, it would be easier for us to help you if you would [edit](https://stackoverflow.com/posts/60458270/edit) your post and add more detail. Take a look at this [well-structured question](https://stackoverflow.com/questions/60453346/use-or-conditions-in-where-clause-as-column-names-in-result), then add the details needed to create [a Minimal, Complete, and Verifiable Example for database-related questions](https://dba.stackexchange.com/help/minimal-reproducible-example). – Eric Brandt Feb 28 '20 at 19:56
  • Sample data and desired results would help. – Gordon Linoff Feb 28 '20 at 21:40

2 Answers2

0

Usually in tables like this, you would have a closed or completion date. If that's the case you can use that to determine, if it went past due before completion. In SQL Server, something like the below would work:

CASE WHEN COALESCE(Closed_date,GETDATE())>DUE_DATE then 'Yes'
ELSE 'No'
END as Past_due_flag

Hope this helps.

Edit: The case mentioned in the comment is not ideal. You could do this by saving a snapshot of either the data or the past due status of the ticket. If the data is real-time update, then there is not much which can be done, because if a ticket goes past due today and the due date is changed today, you won't know unless you have a snapshot of the previous day's data. I would suggest using a change log, tracking all these changes to the table. Hope that makes sense.

CR7SMS
  • 2,520
  • 1
  • 5
  • 13
  • Thanks! thats exactly what I use to calculate if the record is *currently* past due. the problem is users are able to change the due dates and push them further into the future, which would change the past due flag. I am trying to figure out how once Past_Due_flag = 'Yes' then its always 'Past Due' until the issue status = 'Closed' (sorry if this isnt super clear...) – ashley Feb 28 '20 at 19:50
0

Revised Answer:

As you are using Redshift, there is not an ideal way to implement this. You would need some sort of cron/agent job or a trigger (which isn't supported by Redshift). If you use another service of some type for a schedule job, you could run the following query to update a PastDue (bit-type) column to 0 or 1. 

UPDATE table_name
SET PastDue = 1
WHERE DueDate < CurrentDate AND PastDue=0

Now, once the PastDue flag has been set, even if they change the Due Date on the record, the PastDue flag will remain true (1).

D.R.
  • 1,199
  • 5
  • 19
  • 42
  • By "keep it to SQL", I meant the language. There is still some type of cron/agent required for something of this variety. I've updated my answer according to the latest comment which stated she's using Redshift. – D.R. Feb 28 '20 at 20:00
  • The revision clears things up. There should be a "thumbs up" emoji. But you take my meaning. – Eric Brandt Feb 28 '20 at 20:09