0

I have an SQL Server table of the following structure:

id     TransDate            PartType
======================================
1     2016-06-29 10:23:00   A1
2     2016-06-29 10:30:00   A1
3     2016-06-29 10:32:00   A2
4     2016-06-29 10:33:00   A2
5     2016-06-29 10:35:00   A2
6     2016-06-29 10:39:00   A3
7     2016-06-29 10:41:00   A4

I need a SELECT statement that will output a table that finds the changes in PartType, and that looks like this (for SSRS purposes):

PartType   StartTime             EndTime       
=======================================================
A1         2016-06-29 10:23:00   2016-06-29 10:32:00
A2         2016-06-29 10:32:00   2016-06-29 10:39:00
A3         2016-06-29 10:39:00   2016-06-29 10:41:00
A4         2016-06-29 10:41:00   NULL

Note that the StartTime always picks up from the last EndTime, unless it's the first record in the table.

What should my SELECT statement be? I can't seem to get the intended results.

EDIT: I'm using SQL Server 2008 R2; I should've specified that.

controller
  • 185
  • 1
  • 2
  • 11

2 Answers2

2

Hmmm, here is one method using outer apply and group by:

select t1.PartType, min(t1.TransDate) as StartTime, t2.TransDate
from t t1
outer apply
     (select top 1 t2.*
      from t t2
      where t2.PartType <> t1.PartType and t2.TransDate > t1.TransDate
      order by t2.TransDate asc
     ) t2
group by t1.PartType, t2.TransDate;
Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It is rare to see outer apply examples! – BIDeveloper Jun 29 '16 at 13:03
  • That is a lost `t` table alias for sure. Have a look. – Ivan Starostin Jun 29 '16 at 15:42
  • @controller dude, there are **two** tables in this query! one alias for one table, one - for another. Can't you fix couple of typos yourself? – Ivan Starostin Jun 29 '16 at 15:53
  • Have another look. – Ivan Starostin Jun 29 '16 at 15:54
  • Unfortunately the output that this revised answer gives does not meet the requirements outlined in the OP. There is no EndTime – controller Jun 29 '16 at 15:57
  • @controller I guess you have to hire someone who'll write everything in sql for you. SO is not a "do it for me" resource. – Ivan Starostin Jun 29 '16 at 16:33
  • I'm sorry but this just isn't a solution. I'm no SQL expert, but I've tried altering this code in every which way and still can't get it to run. – controller Jun 29 '16 at 18:16
  • @controller update your question with your current code (with alterations you have tried and failed). Try to describe _why_ it's not working: error message or description of why output is wrong. – Ivan Starostin Jun 29 '16 at 19:42
  • Okay so the query runs and outputs correctly, but takes over 5 minutes to do so. I am trying to use a datetime parameter to pick only a certain range of dates from my table, but doing so doesn't seem to speed up the query. Is it the outer apply clause that slows things down? – controller Jul 04 '16 at 17:26
  • @controller . . . You should probably ask another question about performance, with more information about the size of the tables. – Gordon Linoff Jul 05 '16 at 02:45
1

With SQL Server 2012 and later, you can use this:

declare @t table (id int, transdate datetime2(0), parttype char(2))

insert @t
values
(1,     '2016-06-29 10:23:00',   'A1'),
(2,     '2016-06-29 10:30:00',   'A1'),
(3,     '2016-06-29 10:32:00',   'A2'),
(4,     '2016-06-29 10:33:00',   'A2'),
(5,     '2016-06-29 10:35:00',   'A2'),
(6,     '2016-06-29 10:39:00',   'A3'),
(7,     '2016-06-29 10:41:00',   'A4')

;with x as (
select *, row_number() over(partition by parttype order by transdate) rn
from @t
)
select parttype, transdate starttime, lead(transdate) over (order by transdate) from x where rn = 1
dean
  • 9,960
  • 2
  • 25
  • 26