0

I am writing a simple Python program to check for expired service contracts in a Firebird database and create a report based on that query which will be used to send automatic e-mails to the customers.

The function is looking for about to be expiring contracts between two dates: date_from = today + 5days and date_to = today + 10days.
My code looks like this:

t = (date_from, date_to)  # Create  tuple with both dates

print()
cur = con.cursor()
cur.execute("SELECT C_CUST_BULSTAT, C_CUST_NAME, C_OBJ_PHONE,C_ECR_NUMB, C_FROMDATE, C_TODATE, C_NUMBER, C_TYPE FROM CONTRACTS "
            "WHERE (C_TYPE = 2) "
            "AND (C_TODATE BETWEEN ? AND ?);", t)  # Make query matching the tuple

The problem I found is that if the customer comes today 22.01 and signs a new contract from 30.01.2022 to 30.01.2023 the system will have 2 contracts - one expiring and one renewed. According to my code - they will be notified about contract expiry which is wrong. So basically from in case of two contract records with two C_TODATE only the higher should be taken into account and checked against dates matching criteria.

So when doing the checks I should take the contract with latest C_TODATE from the list. How I can do that?

Park
  • 2,446
  • 1
  • 16
  • 25
Zhivko Vasilev
  • 1
  • 1
  • 2
    Welcome to stackoverflow. This looks like a SQL question in the Firebird dialect, rather than specifically a python/FDB programming question. I think you are asking, "how can I select the expiring contracts of only the most recent contracts per customer?" Can you please show us your CREATE TABLE definition _with only the relevant fields_, and then show sample rows (e.g., one customer with two contract records, and one or two customers with only one contract record)? – pilcrow Jan 26 '22 at 03:26
  • 1
    Also, what version of firebird? – pilcrow Jan 26 '22 at 03:37
  • please, enter the sample table and data at https://dbfiddle.uk/?rdbms=firebird_3.0 so we all could try different queries and you would explain which of resulting outputs are right and wrong, and WHY you consider them right or wrong – Arioch 'The Jan 26 '22 at 10:40
  • Voting to close as [already answered here](https://stackoverflow.com/questions/37014009/firebird-query-return-first-row-each-group). I think you have a "greatest _n_ per group" sql question, in this case, the "greatest (most recent) _1_ per customer." Once you have that, you can check for C_TODATE values BETWEEN your expiry window. – pilcrow Jan 31 '22 at 17:40

0 Answers0