Hello we have a detail table in our erp systm that generates timestamp and message info per order. We are trying to create syntax so we can show the end user summarized data. For example this below should be the output we generate through the syntax based on the picture of the detail table included.
Order_No TimeStamp Message
67009 2013-10-02 08.43 Order not set for auto-invoice in SVKECH
67023 2013-10-02 08.43 Order in-use
67023 2013-10-02 08.43 Order not set for auto-invoice in SVKECH
109592 2013-09-06 10.27 Order in-use
117578 2013-09-06 10.27 Failed Logistics Check
117578 2013-12-12 13.18 Called SNM505C.PC00117578
119171 2013-07-05 12.59 Order not set for auto-invoice in SVKECH
This is the syntax we have so far,
SELECT order_number, MAX(TIMESTAMP) AS Recent, Message FROM LOG Table
group by order_number
Any suggestions would be greatly appreciated! Thank you for your time!
Hi and welcome to the forums. It would greatly help your posts if you provide readily consumable ddl and sample data.
Something like this:
create table #Log
select 67009, '2013-10-02 08:43', 'Order not set for auto-invoice in SVKECH' union all
select 67023, '2013-10-02 08:43', 'Order in-use' union all
select 67023, '2013-10-02 08:43', 'Order not set for auto-invoice in SVKECH' union all
select 109592, '2013-09-06 10:27', 'Order in-use' union all
select 117578, '2013-09-06 10:27', 'Failed Logistics Check' union all
select 117578, '2013-12-12 13:18', 'Called SNM505C.PC00117578' union all
select 119171, '2013-07-05 12:59', 'Order not set for auto-invoice in SVKECH'
I changed a couple of your column names because using reserved words as column names is painful to work with.
The issue you are facing is you want to have a single row for each order but you also have LogMessage in the output. Which message do you want?
This is the proper syntax but it will not return what you are looking for.
SELECT order_number, MAX(LogDate) AS Recent, LogMessage
group by order_number, LogMessage
What is the expected output based on the sample data?
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)