Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL. Query help

Posted on 2013-01-26
8
Medium Priority
?
196 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
[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
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 1500 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 Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

664 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