Solved

T-SQL. Query help

Posted on 2013-01-26
8
174 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 142

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
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.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
date diff with Fiscal Calendar 4 33
Query group by data in SQL Server - cursor? 3 34
Text file into sql server 5 28
SQL server vNext 18 31
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 and will be exposed to the many uses the SELECT statement has.

831 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