The most efficient way to handle a subquery for a first record

Question

I have a client id table and a client data table. The client can change identifiers, but I want to request their earliest date in some cases and the latest date in others. For example, I have two tables:

tbl_a id active_id a aa b aa c aa d bb e bb tbl_b id date city a 1/1/2012 Dallas b 1/1/2013 Houston c 2/1/2013 Austin d 1/1/2003 San Antonio e 3/3/2013 El Paso tbl_c id value a 50 b 50 b 50 c 50 d 10 e 10 e 10 

How can I request data to return one active_id with date EARLIEST and MOST RECENT, summing data with tbl_c? For example, return the following:

 active_id date city sum(value) aa 1/1/2012 Austin 200 bb 1/1/2003 El Paso 30 

In semantic terms, I'm trying to find the first date and last city for any active_id by summing the transaction values.

Answer 1

If you can use window functions (and not have access to first_value function):

 with cte as ( select row_number() over(partition by a.active_id order by b.date asc) as rn1, row_number() over(partition by a.active_id order by b.date desc) as rn2, a.active_id, b.date, b.city from tbl_a as a inner join tbl_b as b on b.id = a.id ) select c.active_id, c1.date, c2.city from (select distinct active_id from cte) as c left outer join cte as c1 on c1.active_id = c.active_id and c1.rn1 = 1 left outer join cte as c2 on c2.active_id = c.active_id and c2.rn2 = 1 

demo sql

If you can use the first_value function:

 with cte as ( select first_value(b.date) over(partition by a.active_id order by b.date asc) as date, first_value(b.city) over(partition by a.active_id order by b.date desc) as city, a.active_id from tbl_a as a inner join tbl_b as b on b.id = a.id ) select distinct active_id, date, city from cte 

Answer 2

Try something like this:

 ;WITH DATA AS (SELECT T1.ACTIVE_ID, T2.* FROM TABLEA T1 INNER JOIN TABLEB T2 ON T1.ID = T2.ID), DATA2 AS (SELECT T1.ACTIVE_ID, SUM(T2.VALUE) TOT_VALUE FROM TABLEA T1 INNER JOIN TABLEC T2 ON T1.ID = T2.ID GROUP BY ACTIVE_ID) SELECT T3.ACTIVE_ID, DATE, CITY, T5.TOT_VALUE AS [SUM(VALUE)] FROM DATA T3 INNER JOIN (SELECT MIN(DATE) MAXDATE, ACTIVE_ID FROM DATA GROUP BY ACTIVE_ID)T4 ON T3.ACTIVE_ID = T4.ACTIVE_ID AND T3.DATE = T4.MAXDATE INNER JOIN DATA2 T5 ON T3.ACTIVE_ID = T5.ACTIVE_ID 

A working example can be found on SQL Fiddle .

Good luck

Answer 3

This assumes tbl_a <--> tbl_b is 1 - 1

tran_sum can be placed as max(tran_sum) as here or added to the group

 Select active_id, max(case r1 when 1 then date end) date, max(case r2 when 1 then city end) city, max(tran_sum) From ( Select a.active_id, sum(c.value) over (partition by a.active_id) tran_sum, row_number() over (partition by a.active_id order by date) as r1, b.date, row_number() over (partition by a.active_id order by date desc) as r2, b.city From tbl_a a inner join tbl_b b on a.id = b.id inner join tbl_c c on a.id = c.id ) x Group By active_id;