Solved

Distinct row

Posted on 2013-06-25
4
258 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
Comment Utility
What are you asking for?
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

15 Experts available now in Live!

Get 1:1 Help Now