I have two tables, Orders & Order History, the Orders table has the Order Number and the Order Date which is the date that the order was actually placed.
This is demonstrated in the below schema and demonstration data;
CREATE TABLE #Orders
(
OrderNumber INT,
OrderDate DATETIME
)
INSERT INTO #Orders (OrderNumber,OrderDate)
VALUES
(001,'2019-04-16 07:08:08.567'),
(002,'2019-03-22 07:08:08.567'),
(003,'2019-06-30 07:08:08.567'),
(004,'2019-01-05 07:08:08.567'),
(005,'2019-02-19 07:08:08.567')
The Order audit table also contains the Order Number and the Event Date which is the date that the order status changed.
This is demonstrated in the below schema and demonstration data.
CREATE TABLE #Order_Audit
(
OrderNumber INT,
EventDate DATETIME,
Status INT
)
INSERT INTO #Order_Audit (OrderNumber,EventDate,Status)
VALUES
(001,'2019-04-16 07:08:08.567',1),
(001,'2019-04-19 07:08:08.567',2),
(001,'2019-04-22 07:08:08.567',3),
(001,'2019-04-28 07:08:08.567',4),
(001,'2019-04-30 07:08:08.567',5),
(002,'2019-03-22 07:08:08.567',1),
(002,'2019-03-24 07:08:08.567',2),
(002,'2019-03-26 07:08:08.567',3),
(002,'2019-04-01 07:08:08.567',4),
(002,'2019-04-10 07:08:08.567',5),
(003,'2019-06-30 07:08:08.567',1),
(003,'2019-07-15 07:08:08.567',2),
(003,'2019-07-19 07:08:08.567',3),
(003,'2019-07-20 07:08:08.567',4),
(003,'2019-07-21 07:08:08.567',5),
(004,'2019-01-05 07:08:08.567',1),
(004,'2019-01-06 07:08:08.567',2),
(004,'2019-01-07 07:08:08.567',3),
(004,'2019-01-08 07:08:08.567',4),
(004,'2019-01-09 07:08:08.567',5),
(005,'2019-02-19 07:08:08.567',1),
(005,'2019-03-19 07:08:08.567',2),
(005,'2019-03-21 07:08:08.567',3),
(005,'2019-03-22 07:08:08.567',4),
(005,'2019-03-23 07:08:08.567',5)
Below is the query that I have at the moment, it will give me the difference between the Event Date and the Order Date that the order was placed.
The query has been simplified however the key columns are included. This is being executed on SQL Server 2012 SP4
SELECT
O.OrderNumber,
DATEDIFF(DAY,O.OrderDate,OA.EventDate) AS [Day-Diff]
FROM #Orders O
INNER JOIN #Order_Audit OA ON OA.OrderNumber = O.OrderNumber
The above query outputs like this
|---------------------|------------------|
| OrderNumber | DayDiff |
|---------------------|------------------|
| 001 | 0 |
|---------------------|------------------|
| 001 | 3 |
|---------------------|------------------|
| 001 | 6 |
|---------------------|------------------|
| 001 | 12 |
|---------------------|------------------|
| 001 | 14 |
|---------------------|------------------|
| 002 | 0 |
|---------------------|------------------|
| 002 | 2 |
|---------------------|------------------|
| 002 | 4 |
|---------------------|------------------|
| 002 | 10 |
|---------------------|------------------|
| 002 | 19 |
|---------------------|------------------|
What I actually need is a query that will output more like this
|---------------------|------------------|
| OrderNumber | DayDiff |
|---------------------|------------------|
| 001 | |
|---------------------|------------------|
| 001 | |
|---------------------|------------------|
| 001 | |
|---------------------|------------------|
| 001 | |
|---------------------|------------------|
| 001 | |
|---------------------|------------------|
| Total | 14 |
|---------------------|------------------|
| 002 | |
|---------------------|------------------|
| 002 | |
|---------------------|------------------|
| 002 | |
|---------------------|------------------|
| 002 | |
|---------------------|------------------|
| 002 | |
|---------------------|------------------|
| Total | 19 |
|---------------------|------------------|
However I can't figure out how to get the difference between the Order Date and the most recent Event Date for each order and add it below that group of order events (as shown above) - I am not even sure it is possible in T-SQL and should possibly be handled at the application level.
You can try this below. I have created the Total label as OrderNumber + Total for ordering.
SELECT
CAST(O.OrderNumber AS VARCHAR) + ' Total' OrderNumber,
MAX(DATEDIFF(DAY,O.OrderDate,OA.EventDate)) AS [Day-Diff]
FROM #Orders O
INNER JOIN #Order_Audit OA ON OA.OrderNumber = O.OrderNumber
GROUP BY CAST(O.OrderNumber AS VARCHAR) + ' Total'
UNION ALL
SELECT
CAST(O.OrderNumber AS VARCHAR) OrderNumber,
NULL AS [Day-Diff]
FROM #Orders O
INNER JOIN #Order_Audit OA ON OA.OrderNumber = O.OrderNumber
ORDER BY 1