I need to fetch records which have passed tentative dates and which are about to expire in next 3 days.
how do i write a sql query for this.I am new to erpnext. help
sql_query = frappe.db.sql(sql_query_str,as_dict=True) in python
More over study this page Frappe Database API
regards
@Mohammad_Ahmad_Zulfi i have to write a query report for this. how can it be done??
This is a sample of dates for which records are to be fetched. So i want to fetch all the past records as well as which will be coming in next 3 days. how do I write a query report for this?
Below is my query report
i have used d.tentative_date_of_return <= now() .This line gives me all the old records but how do i fetch records for next 3 days.
I even tried d.tentative_date_of_return <= now() && d.tentative_date_of_return >= now()
but no output.
SELECT DISTINCT
d.name AS “ID:Link/Demo Request:120”,
d.requester_name AS “Requester Name:Data:190”,
d.customer_name AS “Customer Name:Data:250”,
d.date_of_request AS “Date Of Request:Date:120”,
d.tentative_date_of_return AS “Tentative Date Of Return:Date:170”,
d.material_sent_from_location AS “Material Sent From Location:Data:190”,
d.delivery_note AS “Delivery Note:Data:100”,
d.material_status AS “Material Status:Data:120”,
d.product_supplied_condition AS “Product Supplied Condition:Data:150”,
d.courier_service AS “Courier Service:Data:120”,
d.shipped_date_from_rahi AS “Shipped Date From Rahi:Date:120”,
d.tracking_no AS “Tracking No:Data:120”,
DATEDIFF(now(),d.tentative_date_of_return) AS “Delayed By:Data:100”
FROM
tabDemo Request as d
WHERE
d.requester_name REGEXP %(requester_name)s
AND
d.docstatus like “1”
AND
d.material_status not like “Recieved From Customer”
AND
d.tentative_date_of_return <= now()
ORDER BY d.tentative_date_of_return ASC
Please explain more about your requirements. Your screen shot shows tentative date with same title. What I understand is that you have a range of date
And you want data between those range and 3 next date from “to date”
In this it is assume that next 3 days is only valid if the record already exists.
Also tell about the doctype and the data you want to extract
Doctype = Demo Request
I want to fetch the old data records from current date as well the records for next 3 days from current date. So I want all the data before current date and also after current date which is about to come in next 3 days.
Instead of now() at
d.tentative_date_of_return <= now()
replace it with date by using frappe date utils.
date= now+3days in future
Replaced d.tentative_date_of_return <= now() by DATEDIFF(d.tentative_date_of_return,now()) < 4
Issue solved. Thanks @Mohammad_Ahmad_Zulfi