• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

T-SQL. Query help

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
keplan
Asked:
keplan
1 Solution
 
liijaCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
zombie2020Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
zombie2020Commented:
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
 
liijaCommented:
Oops.. of course, didn't think it far enough when wrote the sql a bit too fast...
0
 
zombie2020Commented:
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
 
zombie2020Commented:
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
 
keplanAuthor Commented:
good answer
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now