如何比较同一列中的两个日期并获得最早的日期？

I am new with SQL and i recived a good task.
The task is:
I have two tables, one called TICKETS end other called LOGS.

我是SQL的新手,我收到了一个很好的任务。任务是:我有两个表,一个名为TICKETS,另一个名为LOGS。

Tickets:
ID | Ticket | Open_Date
10 | 40000 | 2015-03-01 11:00:00
11 | 40001 | 2015-03-01 12:00:00
12 | 40002 | 2015-03-01 13:00:00
13 | 40003 | 2015-03-01 14:00:00
14 | 40004 | 2015-03-01 15:00:00

门票:ID |门票| Open_Date 10 | 40000 | 2015-03-01 11:00:00 11 | 40001 | 2015-03-01 12:00:00 12 | 40002 | 2015-03-01 13:00:00 13 | 40003 | 2015-03-01 14:00:00 14 | 40004 | 2015-03-01 15:00:00

Logs:
ID | Ticket_id | Time_Stamp | Description
01 | 10 | 2015-03-01 11:10:00| In Progress
02 | 10 | 2015-03-01 11:15:00| In Progress
03 | 10 | 2015-03-01 11:20:00| Transfered
04 | 11 | 2015-03-01 12:10:00| In Progress
05 | 11 | 2015-03-01 12:15:00| Transfered
06 | 11 | 2015-03-01 12:20:00| In Progress
07 | 12 | 2015-03-01 13:05:00| Transfered
08 | 12 | 2015-03-01 13:10:00| In Progress
09 | 12 | 2015-03-01 13:15:00| In Progress
10 | 13 | 2015-03-01 14:05:00| Transfered
11 | 13 | 2015-03-01 14:10:00| Transfered
12 | 13 | 2015-03-01 14:15:00| In Progress
13 | 14 | 2015-03-01 15:10:00| Transfered
14 | 14 | 2015-03-01 15:15:00| In Progress
15 | 14 | 2015-03-01 15:20:00| Transfered

日志:ID | Ticket_id | Time_Stamp |描述01 | 10 | 2015-03-01 11:10:00 |进行中02 | 10 | 2015-03-01 11:15:00 |进展03 | 10 | 2015-03-01 11:20:00 |转移04 | 11 | 2015-03-01 12:10:00 |进行中05 | 11 | 2015-03-01 12:15:00 |转移06 | 11 | 2015-03-01 12:20:00 |进展07 | 12 | 2015-03-01 13:05:00 |转移08 | 12 | 2015-03-01 13:10:00 |进展09 | 12 | 2015-03-01 13:15:00 |进行中10 | 13 | 2015-03-01 14:05:00 |转移11 | 13 | 2015-03-01 14:10:00 |转移12 | 13 | 2015-03-01 14:15:00 |进展中13 | 14 | 2015-03-01 15:10:00 |转移14 | 14 | 2015-03-01 15:15:00 |进行中15 | 14 | 2015-03-01 15:20:00 |转移

As showed in the tables, i have many logs to a single ticket. What i need to do is find the first log with description "In Progress" and get its date(the oldest). If it comes after a log of "Transfered" get the date of the log "Transfered" as "Open_Date" of the ticket and then get the date of the first log "In Progress".

如表中所示,我有很多日志到一张票。我需要做的是找到描述为“进行中”的第一个日志并获取其日期(最早的)。如果它出现在“已转移”日志之后,将日志“已转移”作为票证的“Open_Date”,然后获取第一个日志“正在进行”的日期。

The query that was give to me brings just the first "In Progress" log:

给我的查询只带来第一个“进行中”日志:

`Select 
Tickets.ticket, 
Tickets.Open_Date,
Logs.Description,
Logs.Time_Stamp
From
Tickets LEFT JOIN Logs ON Tickets.ID = Logs.Ticket_id
WHERE
Logs.Time_Stamp = (Select MIN(Logs_2.Time_Stamp)
                   From Logs AS Logs_2
                   Where Logs_2.Description = 'In Progress')
And Logs.Time_Stamp < '2015-03-01 14:30:00'
And Logs.Time_Stamp > '2015-03-01 10:00:00'
Group By
Tickets.ticket, 
Tickets.Open_Date,
Logs.Description,
Logs.Time_Stamp`

I hope I have been clear on my problem :)
Could someone help me?
Thx.

我希望我已经清楚我的问题:)有人可以帮助我吗?谢谢。

1 个解决方案

#1

i guess you dont actually want value from preceding / next row? (http://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/ )

我猜你真的不想要前/下一行的价值? (http://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/)

but query ID / Ticket_ID and sort by date? ( sql query to get earliest date )

但查询ID / Ticket_ID并按日期排序? (sql查询获取最早的日期)

#1