Odtnhj

I'm trying to count the number of orders in a given status for given date. In example data, the order status is a timeline that is only updated when the order moves on to the next status.

Sample Data:

Order_ID Status Status_Date
-------- ------ -----------------------
56845 NEW 2012-09-11 11:52:20.000
56845 SENT 2012-09-12 00:22:13.000
56845 ACK 2012-09-17 08:14:33.000
56845 FILL 2012-09-20 14:32:00.000
36968 NEW 2012-09-08 11:52:20.000
36968 SENT 2012-09-15 00:22:13.000
36968 ACK 2012-09-22 08:14:33.000
48258 NEW 2012-09-14 11:52:20.000
48258 SENT 2012-09-20 00:22:13.000
48258 ACK 2012-09-22 08:14:33.000
48258 FILL 2012-09-28 23:22:46.000
48258 SHIP 2012-09-29 18:54:22.000

My difficulty is I need to select the last row that was entered for each order that is less than a specific date and count it if it is in a certain status.

So if I'm using the status = 'SENT' and a date of 9/12/2018 at midnight, I want to return order-id 56845 and 36968. If I'm using a status = 'SENT' and a date of 9/21/2018 at midnight, I want to return order-id 36968 and 48258 because order-id 56845 is in 'FILL' status and no longer in 'SENT'.

select count(*) from(select order_id,status from table group by order_id having max(status_date) <= '2012-09-16' or max(status_date) = '2012-09-21' and status ='SENT' )
This is what you are looking for this means if at max status date for each individual order if the items status doesnt change or it is sent until max date less than your date you get the correct result.

You can use below one

select count(1) from table_name where status='NEW' group by Status_Date

or you can use this one to get count for all status in all dates

select count(1) as count,Status,left(Status_Date,10) as Status_Date from table_name 
group by Status,left(Status_Date,10)

If you want such a specific counter:

SELECT COUNT(*) AS counter FROM orders WHERE Status = 'SENT' and Status_Date <= '2012-09-16 23:59:59.000'

or

SELECT COUNT(*) AS counter FROM orders WHERE Status = 'SENT' and Status_Date <= Convert(datetime, '2012-09-16 23:59:59.000')

to convert the string literal to date.