Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Distinct row

Posted on 2013-06-25
4
Medium Priority
?
279 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
4 Comments
 
LVL 34

Expert Comment

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

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 600 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 49

Accepted Solution

by:
PortletPaul earned 1400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

971 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