Repository: frappe/erpnext
Pull request: feat: new column 'Time taken to Deliver' in sales order analysis
### Current Report
_'Sales Order Analysis'_ helps in quickly identifying billed amount, pending amount, delay in days and more information. Along with this a new column is added

### New Column - 'Time taken to Deliver'
This denotes the no of days taken to completely deliver an item. This helps in identifying bottlenecks and get more efficient.

### Image
<img width="1552" alt="time_taken_to_deliver" src="https://user-images.githubusercontent.com/3272205/145781927-0f7b4b9e-621d-4f35-8e13-d60a1ef4d6de.png">

**no-docs**

Problem statement:
Include column for 'Days taken to deliver' in Sales Order Analysis Report
Currently, the Sales Order Analysis reflects the Delay in Days took them to deliver the Ordered Items, they want one more column to reflect how many days they take to completely deliver a Sales Order in order to increase the efficiency and get more productive accordingly.

Hints from issue discussion:

Commit message: feat: new column 'Time taken to Deliver' in sales order analysis

Patch to review:
diff --git a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
index 82e5d0ce57d9..f1edca4cef39 100644
--- a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
+++ b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
@@ -61,6 +61,7 @@ def get_data(conditions, filters):
 			IF(so.status in ('Completed','To Bill'), 0, (SELECT delay_days)) as delay,
 			soi.qty, soi.delivered_qty,
 			(soi.qty - soi.delivered_qty) AS pending_qty,
+			IF((SELECT pending_qty) = 0, DATEDIFF(Max(dn.posting_date), so.transaction_date), 0) as time_taken_to_deliver,
 			IFNULL(SUM(sii.qty), 0) as billed_qty,
 			soi.base_amount as amount,
 			(soi.delivered_qty * soi.base_rate) as delivered_qty_amount,
@@ -70,9 +71,13 @@ def get_data(conditions, filters):
 			so.company, soi.name
 		FROM
 			`tabSales Order` so,
-			`tabSales Order Item` soi
+			(`tabSales Order Item` soi
 		LEFT JOIN `tabSales Invoice Item` sii
-			ON sii.so_detail = soi.name and sii.docstatus = 1
+			ON sii.so_detail = soi.name and sii.docstatus = 1)
+		LEFT JOIN `tabDelivery Note Item` dni
+			on dni.so_detail = soi.name
+		RIGHT JOIN `tabDelivery Note` dn
+			on dni.parent = dn.name and dn.docstatus = 1
 		WHERE
 			soi.parent = so.name
 			and so.status not in ('Stopped', 'Closed', 'On Hold')
@@ -259,6 +264,12 @@ def get_columns(filters):
 			"fieldname": "delay",
 			"fieldtype": "Data",
 			"width": 100
+		},
+		{
+			"label": _("Time Taken to Deliver"),
+			"fieldname": "time_taken_to_deliver",
+			"fieldtype": "Data",
+			"width": 100
 		}
 	])
 	if not filters.get("group_by_so"):


Code review comments:
diff_hunk: @@ -259,6 +264,12 @@ def get_columns(filters):
 			"fieldname": "delay",
 			"fieldtype": "Data",
 			"width": 100
+		},
+		{
+			"label": _("Time Taken to Deliver"),
+			"fieldname": "time_taken_to_deliver",
+			"fieldtype": "Data",
path: erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
text: @user1:
Can we make this a Duration field? And store the seconds difference between `posting_date` & `transaction_date`?

@author:
Changed to Duration