tsql help - get change or created line item

[rodchar]

Hi All,

I have an order history file.
It has an Action column to store action performed on the order.

Order Id, Action, Date
1, Created, 8/1/10
1, Changed, 8/15/10
1, Changed, 9/1/10

What is the best t-sql to say
Give me the most recent changed date for all orders And if there is no
Changed status just give me the Created date.

Now I can imagine doing this by stringing the sql together and then
executing the sql string but is that the best way?

Thanks,

rod.

[Mariano Gomez]

CREATE TABLE FooBar(OrderId INT, ActionType VARCHAR(10), ActionDate DATETIME);
INSERT FooBar(OrderId, ActionType, ActionDate) VALUES (1, 'Created',
'20100801');
INSERT FooBar(OrderId, ActionType, ActionDate) VALUES (1, 'Changed',
'20100815');
INSERT FooBar(OrderId, ActionType, ActionDate) VALUES (1, 'Changed',
'20100901');
INSERT FooBar(OrderId, ActionType, ActionDate) VALUES (2, 'Created',
'20100805');

; WITH OrderStatuses (OrderId, ActionType, ActionDate, LastActionRank) AS (
SELECT OrderId, ActionType, ActionDate, RANK() OVER (PARTITION BY OrderId
ORDER BY OrderId, ActionDate Desc) As LastActionRank
FROM FooBar
)
SELECT OrderId, ActionType, ActionDate FROM OrderStatuses WHERE
LastActionRank = 1

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com

[rodchar]

I humbly apologize but I left out 1 detail to include that there can be more
than just those 2 actions (i.e. reviewed, approved, etc).

[Erland Sommarskog]

rodchar (rod...@discussions.microsoft.com) writes:
> I humbly apologize but I left out 1 detail to include that there can be
> more than just those 2 actions (i.e. reviewed, approved, etc).

I can't see that this would be any problem with Mariano's solution?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Iain Sharp]

select order_id,coalesce(max(case when Action = 'Changed' then date
else null end), max(case when Action = 'Created' then date else null
end ))
from orderhistory
group by order_id

Might work

[Mariano Gomez]

If you are looking to get the first occurrence of each action that's a
different story from what you initially told us.

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com

"Erland Sommarskog" wrote:

> .
>

[--CELKO--]

Technically, this is an even table, not a history table. A history
table would have durations and not single times. It is just good
Netiquette to always post as DDL skeleton:

CREATE TABLE Order_Events
(order_nbr INTEGER NOT NULL,
event_date DATE NOT NULL,
order_action CHAR(6) NOT NULL
CHECK (order_action IN ('create', 'change', ..)),
PRIMARY KEY (order_nbr, event_date),
..);

We can assume that an order is created before it is changed, so:

SELECT order_nbr, MAX(event_date) AS last_change_date
FROM Order_Events
WHERE order_action IN ('create', 'change')
GROUP BY order_nbr;