Distinct row

Hi,
I have query gives me the result in xl  attachment, I need the first row for each (Bug
Proccess,Resolved) I highlighted in the spreadsheet. Please see the attachment.
Thanks
q1.xlsx
SanPrgAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
1. your existing query? - this will help you as much as it will help us as we can use correct tables/fields etc.

2. what version of sql server? (it can make a big difference to solution choices)

3. I think you are after this:
for an ID (e.g.1534)
a. the datetime it starts
b. the Proc value at point a.
c. the datetime it enters Proc value 'Proccess' -- please check spelling :)
d. the earliest datetime it gets a Proc value of 'Resolved'
like this:

--ID,EntersAt,EntersWith,CommenceProcess,ResolvedAt
--1534,2012-05-05 20:40,Bug,2012-05-05 20:46,2012-06-05 18:12

with cte as (
              select
                      id
                    , changing
                    , resolved
                    , [proc]
                    , row_number() over (partition by id order by changing) as change_at_no
                    , row_number() over (partition by id order by 
                                           (case when [proc] = 'Proccess' then 1 else 2 end)
                                         , changing
                                        ) as enterwith_no
                    , row_number() over (partition by id order by 
                                           (case when resolved is not null then 1 else 2 end)
                                         , resolved
                                        ) as resolved_row
              from yourtable
              )
select
      id
    , min( case when change_at_no = 1 then changing end ) as EntersAt
    , min( case when change_at_no = 1 then [Proc] end )   as EntersWith
    , min( case when enterwith_no = 1 then changing end ) as CommenceProcess
    , min( case when resolved_row = 1 then Resolved end ) as ResolvedAt
from cte
group by
      id
;

-- without cte
select
      id
    , min( case when change_at_no = 1 then changing end ) as EntersAt
    , min( case when change_at_no = 1 then [Proc] end )   as EntersWith
    , min( case when enterwith_no = 1 then changing end ) as CommenceProcess
    , min( case when resolved_row = 1 then Resolved end ) as ResolvedAt
from (
        select
                id
              , changing
              , resolved
              , [proc]
              , row_number() over (partition by id order by changing) as change_at_no
              , row_number() over (partition by id order by 
                                   (case when [proc] = 'Proccess' then 1 else 2 end)
                                   , changing
                                  ) as enterwith_no
              , row_number() over (partition by id order by 
                                   (case when resolved is not null then 1 else 2 end)
                                   , resolved
                                  ) as resolved_row
        from yourtable
     ) as derived
group by
      id
;

Open in new window

see these working at: http://sqlfiddle.com/#!3/d8474/8
0
 
Brian CroweDatabase AdministratorCommented:
What are you asking for?
0
 
Patrick MatthewsCommented:
SELECT t1.ID, t1.Changing, t1.Resolved, t1.Proc
FROM SomeTable t1 INNER JOIN
    (SELECT t2.ID, MIN(t2.Changing) AS Changing, t2.Proc
    FROM SomeTable t2
    GROUP BY t2.ID, t2.Proc) AS x ON t1.ID = x.ID AND t1.Changing = x.Changing AND t1.Proc = x.Proc
GROUP BY t1.ID, t1.Changing, t1.Resolved, t1.Proc
ORDER BY t1.ID, t1.Changing, t1.Proc

Open in new window

0
 
SanPrgAuthor Commented:
Excellent Thanks all of you.
PortletPaul,
That's what I need in next step ( 3 date columns).

matthewspatrick ;
The query works fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.