Solved

T-SQL. Query help

Posted on 2013-01-26
8
176 Views
Last Modified: 2013-02-09
I've following data set and I required to get the result as answer.

Sample records of my Data set:

Custid.           Activity date time            Contact person

123456.          2001-07-07:07:432.          Anthony
123456.          2003-09-09:09:567.           Kamal
123456.          2009-08-09:06:654.            Ruby

Result required in a single row:

Custid.     First_actvity_date.   Last_activity_date.      First_contact_person.
123456.   2001-07-07:07:432. 2009-08-09:06:654.          Anthony

Last_contact_person
Ruby


Thanks in advance,

Kaplan
0
Comment
Question by:keplan
8 Comments
 
LVL 6

Expert Comment

by:liija
ID: 38821643
This should to the trick:

SELECT
 CustID,
 MIN(Activity_date_time) as First_activity_date,
 MAX(Activity_date_time) as Last_activity_date,
 MIN(Contact_person) AS First_contact_person,
 MAX(Contact_person) AS Last_contact_person
FROM TableX
GROUP BY CustID
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38821793
actually, above query will NOT return the requested data.
see this article for explanations:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

you will need to join the table a second time, once for the min( date), and one for the max(date) value.
0
 

Expert Comment

by:zombie2020
ID: 38821905
solution based window functions (SQL Server 2012)


select distinct
           Custid,
           min([ Activity date time]) over(partition by custid) as First_actvity_date.,
           max([Activity date time])  over(partition by custid) as Last_activity_date.,
           first_value([Contact person]) over(partition by custid order by [Activity date time]
            rows between unbounded preceding and unbounded following) as First_contact_person.
           last_value([Contact person]) over(partition by custid order by [Activity date time]
            rows between unbounded preceding and unbounded following) as
Last_contact_person
from table_name
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:zombie2020
ID: 38821910
Another Solution:

select d.custid,
          t1. [Activity date time] First_actvity_date,
          t1.[Contact person] First_contact_person.,
          t2. [Activity date time] Last_activity_date.,
          t2.[Contact person] Last_contact_person.
from (select custid from table_name group by custid)d
cross apply (select top 1 [Activity date time], [Contact person]
                   from table_name t
                   wherer t.custid = d.custid
                   order by  [Activity date time]) t1
cross apply (select top 1 [Activity date time], [Contact person]
                   from table_name t
                   wherer t.custid = d.custid
                   order by  [Activity date time] desc) t2;
0
 
LVL 6

Expert Comment

by:liija
ID: 38821933
Oops.. of course, didn't think it far enough when wrote the sql a bit too fast...
0
 

Accepted Solution

by:
zombie2020 earned 500 total points
ID: 38821941
Another solution:

select custid,
             max(case when rnk1 = 1 then  [Activity date time] else null end) as First_actvity_date,
             max(case when rnk1 = 1 then [Contact person] else null end) as First_contact_person,
             max(case when rnk2 = 1 then  [Activity date time] else null end) as Last_activity_date,
             max(case when rnk2 = 1 then [Contact person] else null end) as Last_contact_person,
(
select *,
            row_number() over(partition by custid order by [Activity date time] asc) rnk1,
            row_number() over(partition by custid order by [Activity date time] desc) rnk2
from table_name
)t
group by cusid;
0
 

Expert Comment

by:zombie2020
ID: 38821954
Another Solution:

;with cte1 as
(
select tb.*
from table_name tb
inner join
(
select custid, min([Activity date time] ) as mn
from table_name
group by custid
)t
on tb.custid = t.custid
and tb.[Activity date time] = t.mn
),
  cte2 as
(
select tb.*
from table_name tb
inner join
(
select custid,max([Activity date time] ) as mx
from table_name
group by custid
)t
on tb.custid = t.custid
and tb.[Activity date time] = t.mx
)
select *
from cte1, cte2
where cte2.custid = cte1.custid;
0
 

Author Closing Comment

by:keplan
ID: 38872204
good answer
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

685 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