Solved

Distinct row

Posted on 2013-06-25
4
272 Views
Last Modified: 2013-06-26
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
0
Comment
Question by:SanPrg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39276939
What are you asking for?
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 total points
ID: 39276999
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
ID: 39277021
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
 

Author Closing Comment

by:SanPrg
ID: 39279012
Excellent Thanks all of you.
PortletPaul,
That's what I need in next step ( 3 date columns).

matthewspatrick ;
The query works fine.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question