Solved

Distinct row

Posted on 2013-06-25
4
262 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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now