Time difference / and count

Transact-SQL
1

hiya

i have a order table

Order:

CustomerID OrderSection Target DateTime
12345 1 35 2014-05-01 01:01:00.000
12345 2 100 2014-05-02 01:35:00.000
12345 3 45 2014-05-03 02:04:00.000
12345 4 100 2014-05-04 02:06:00.000

i wish to create a transaction like this , and have an output of no of minutes (timediff) as MinutesTaken column from order section 1 and last order section ...in this case 4...but this section can go up to 6 or even 7. i also want to create a column as TargetDropCount that counts how many times the order drops below its 100 target. in this table above for the customer shown this would equal "2"..... but i don't know how to do it please help?

SELECT t1.OrderNo,TIMEDIFF(day,t1.DateTime,t2.DateTime)
FROM Order t1
INNER JOIN
Order t2
on
t1.CustomerID = t2.CustomerID
WHERE t1.Min OrderSection
t2.Max OrderSection

xx

2 
SELECT
	CustomerId,
	DATEDIFF(MINUTE,MIN([DateTime]), MAX([DateTime])) AS MinutesTaken,
	SUM(CASE WHEN [Target] < 100 THEN 1 ELSE 0 END ) AS DropsBelow100Count
FROM
	OrdersTable
GROUP BY
	CustomerId;

Usually it is better to avoid using reserved words for column names - for example Datetime

1 Like
3

Hiya

that's awesome thanks...got another related to this question, i wrote a script too that tried to create a column as TargetReached on how long in minutes it took for a customer to reach its first target of 100. in case above that would be 34 minutes (and that's just the answer i need for a different section of my project) but unfortunately my code seems like in example above because the target drops the time count keeps going and shows 1:06 minutes , how can i prevent this please?

xx

4

You can add a column like shown below, or have it as a separate query. If the target was never reached, the new column would return null.

SELECT
	CustomerId,
	DATEDIFF(MINUTE,MIN([DateTime]), MAX([DateTime])) AS MinutesTaken,
	SUM(CASE WHEN [Target] < 100 THEN 1 ELSE 0 END ) AS DropsBelow100Count,
	DATEDIFF(MINUTE, MIN([DateTime]), MIN(CASE WHEN [Target] >= 100 THEN [DateTime] END)) AS TimeToFirst100
FROM
	OrdersTable
GROUP BY
	CustomerId;