CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.6
KarlPRO
KarlPRO

Reputation: 39

How do I get my records from a specific date in time?

Edit: 15:09 2020-07-08, well I solved it, and while it isn't the optimal solution or the most neat and slim one it does what I need it too. So thanks for those who took their time to comment and answer!

So i have a query that i want to input a date into, and then get the records that was "active" during that date. I am currently only using current date so it is very static. But how to make it more dynamic, so that I can as I said chose any date in time, and get that data? What do you suggest?

I'm using postgresql 9.6 in pgadmin 4

don't now if this is of any help but here is my query.....

SELECT due.contract_id,due.component,due.due_not_paid, due.current_arrears_days,ob.outstandingamount, due.record_valid_to
FROM
    (SELECT a.record_valid_to, a.contract_id,a.component, sum(a.invoice_non_paid_waivedamount) as due_not_paid,max(no_latedays) as Current_arrears_days
    FROM
        (SELECT 
        sub.contract_id,sub.record_valid_to,sub.invoice_generation_date,sub.invoice_duedate,sub.invoice_paiddate,
        sub.invoice_dueamount,sub.invoice_paid_waivedamount,sub.invoice_non_paid_waivedamount,sub.component,
        CASE WHEN sub.invoice_dueamount = 0 THEN 0 ELSE sub.latedays END as no_latedays,
        CASE WHEN sub.invoice_non_paid_waivedamount > 0 AND invoice_duedate < current_date THEN 'DUE' ELSE 'PAID or non-due' END AS Paid_status
        FROM
            (select contract_id,record_valid_from,record_valid_to,invoice_generation_date,invoice_duedate,invoice_paiddate, invoice_dueamount,invoice_paid_waivedamount,
            CASE WHEN invoice_paid_waivedamount>0  AND (invoice_dueamount=invoice_paid_waivedamount) THEN (invoice_paiddate-invoice_duedate) ELSE (current_date-invoice_duedate) END as latedays,
            (invoice_dueamount-invoice_paid_waivedamount) as invoice_non_paid_waivedamount,component
            from dwd_tb_contracts_invoices where record_valid_to='9999-12-31' 
            ORDER BY invoice_duedate) 
         sub) a
    WHERE a.Paid_status = 'DUE'
    GROUP BY a.record_valid_to, a.contract_id, a.component) due
LEFT OUTER JOIN
    (select * from exposure_split('2020-07-06')) ob 
    ON due.contract_id = ob.contract_id
    where due.component = 'INTEREST_INTERMEDIATE'
or due.component = 'PRINCIPAL'
or due.component = 'INTEREST'
or due.component = 'INTEREST_DIFFERENCE'
    order by contract_id

Upvotes: 0

Views: 81

Answers (1)

Project Zero
Project Zero

Reputation: 63

If you are speaking of the time of insertion, then you could create a procedure of function that automatically sets the current date and time as a cell of that table and not allow direct access to it and then directly query it. The same can be done, if accessing a row or table is concerned.

I'm not really into SQL neither do I have the rep. to comment, but if can write a code for the same, please let me know...so that I can safely delete the answer then.

Upvotes: 1

Related Questions