I m Trying to Fetch Data by using Two Date column in PostgreSQL. It is giving me the correct the output according to the query. But it is not giving me my expected result.
In this Output i fetched data by using document_submission_date and received_date of February month but it is giving me some output of the month of march which I indicated with the blue sign. yap, for sure it is giving me the correct result according to the query. but i only want to return all data except the blue indicator (image above). this is my query given below..
SELECT Sum(pd.line_total) AS lc_receive, rbo.rbo_name, pipay.status, rbo.rbo_id, pipay.document_submission_date, pipay.received_date FROM proforma_invoice pi JOIN pi_payment_information pipay ON pi.proforma_invoice_id = pipay.pi_id JOIN proforma_invoice_details pd ON pd.proforma_invoice_id = pi.proforma_invoice_id JOIN product_category pc ON pc.product_category_id = pd.product_category_id JOIN rbo ON pc.rbo_id = rbo.rbo_id JOIN users usr ON pi.created_by = usr.user_id JOIN team_mapping tm ON usr.employee_id = tm.emp_id WHERE tm.team_id = '5' AND ( pipay.status = 'L/C Received' OR pipay.status = 'TT Received' OR pipay.status = 'Bill Received' OR pipay.status = 'FDD Received' OR pipay.status = 'Doc Submitted' OR pipay.status = 'Acceptance Received' ) AND ( To_date(pipay.received_date, 'DD/MM/YYYY') BETWEEN To_date('01/02/2020', 'DD/MM/YYYY') AND To_date('29/02/2020', 'DD/MM/YYYY') OR ( To_date(pipay.document_submission_date, 'DD/MM/YYYY') BETWEEN To_date( '01/02/2020', 'DD/MM/YYYY') AND To_date('29/02/2020', 'DD/MM/YYYY') ) ) AND pipay.received_date != 'N/A' GROUP BY rbo.rbo_id, rbo.rbo_name, pipay.status, pipay.document_submission_date, pipay.received_date
I have been trying to solve this problem since yesterday but i could’t find any solution of this. It will be very helpful to me to if any one help me to solve this problem. If anyone help to solve this by Linq Query, that will be helpful too.
Advertisement
Answer
This seems to work as expected. Consider these conditions in your WHERE
clause:
AND ( To_date(pipay.received_date, 'DD/MM/YYYY') BETWEEN To_date('01/02/2020', 'DD/MM/YYYY') AND To_date('29/02/2020', 'DD/MM/YYYY') OR ( To_date(pipay.document_submission_date, 'DD/MM/YYYY') BETWEEN To_date('01/02/2020', 'DD/MM/YYYY') AND To_date('29/02/2020', 'DD/MM/YYYY') ) )
This predicates filters on rows where either received_date
or document_submission_date
belong to the month of February. What you are showing in your current results satisfies these conditions.
If you want both columns in February, use AND
instead of OR
. I would also recommend using date literals and half-open intervals as these make the query shorter and easier to read:
AND to_date(pipay.received_date, 'DD/MM/YYYY') >= date '2020-02-01' AND to_date(pipay.received_date, 'DD/MM/YYYY') < date '2020-03-01' AND to_date(pipay.document_submission_date, 'DD/MM/YYYY') >= date '2020-02-01' AND to_date(pipay.document_submission_date, 'DD/MM/YYYY') < date '2020-03-01'
Side note: you should really fix your schema and store date values as date
datatypes. Storing dates as strings is a bad practice that makes things inefficient and unnecessarily complicated.
If you want to all null
s in document_submission_date
, then:
AND to_date(pipay.received_date, 'DD/MM/YYYY') >= date '2020-02-01' AND to_date(pipay.received_date, 'DD/MM/YYYY') < date '2020-03-01' AND ( pipay.document_submission_date IS NULL OR ( to_date(pipay.document_submission_date, 'DD/MM/YYYY') >= date '2020-02-01' AND to_date(pipay.document_submission_date, 'DD/MM/YYYY') < date '2020-03-01' ) )