Solved

T-SQL. Query help

Posted on 2013-01-26
8
175 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.

839 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