I Need Help from SQL

In the screen shot I don't see any Order Id column. Confused. You also didn't mention which column is to be watched against (10 minutes, 20 minutes in relation to which column?) because the only date time column is created_at I assume that is base column.

You didn't mention table name also. You also didn't mention dbms system, MS SQL Server, MySQL, Oracle, etc. Very important while asking for sql code.

Also ask question under correct category. Category ASP.NET & Web Development is not quite relevant here.

Please give more / all info as asked above.

If MS SQL Server try following:

select [store_id]
,[increment_id]
,datediff(mm, p.[created_at], pending.[created_at]) [pending to processing minutes]
,datediff(mm, o.[created_at], p.[created_at]) [processing to out delivery minutes]
,datediff(mm, c.[created_at], o.[created_at]) [out delivery to compete minutes]
from [dbo].[your_table] pending
left outer join [dbo].[your_table] p on p.[store_id] = pending.[store_id] and p.[increment_id] = pending.[increment_id] and p.[status] = 'processing'
left outer join [dbo].[your_table] o on o.[store_id] = pending.[store_id] and o.[increment_id] = pending.[increment_id] and o.[status] = 'out delivery'
left outer join [dbo].[your_table] c on c.[store_id] = pending.[store_id] and c.[increment_id] = pending.[increment_id] and c.[status] = 'complete'
where 1 = 1
and pending.[status] = 'pending'
and (
datediff(mm, p.[created_at], pending.[created_at]) > 10 or
datediff(mm, o.[created_at], p.[created_at]) > 20 or
datediff(mm, c.[created_at], o.[created_at]) > 30
)

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
In the screen shot I don't see any Order Id column. Confused. You also didn't mention which column is to be watched against (10 minutes, 20 minutes in relation to which column?) because the only date time column is created_at I assume that is base column.

You didn't mention table name also. You also didn't mention dbms system, MS SQL Server, MySQL, Oracle, etc. Very important while asking for sql code.
Also ask question under correct category. Category ASP.NET & Web Development is not quite relevant here.

Please give more / all info as asked above.

If MS SQL Server try following:
select [store_id]
  ,[increment_id]
  ,datediff(mm, p.[created_at], pending.[created_at]) [pending to processing minutes]
  ,datediff(mm, o.[created_at], p.[created_at]) [processing to out delivery minutes]
  ,datediff(mm, c.[created_at], o.[created_at]) [out delivery to compete minutes]
from [dbo].[your_table] pending
left outer join [dbo].[your_table] p on p.[store_id] = pending.[store_id] and p.[increment_id] = pending.[increment_id] and p.[status] = 'processing'
left outer join [dbo].[your_table] o on o.[store_id] = pending.[store_id] and o.[increment_id] = pending.[increment_id] and o.[status] = 'out delivery'
left outer join [dbo].[your_table] c on c.[store_id] = pending.[store_id] and c.[increment_id] = pending.[increment_id] and c.[status] = 'complete'
where 1 = 1
and pending.[status] = 'pending'
  and (
  datediff(mm, p.[created_at], pending.[created_at]) > 10 or
  datediff(mm, o.[created_at], p.[created_at]) > 20 or
  datediff(mm, c.[created_at], o.[created_at]) > 30
  )
+2