July 10, 2014 at 11:24 am
Extreme beginner here.
Table 1 and 2 both have an Account Column and a Date Column
I need the max Date for distinct accounts into one column. How is this done?
July 10, 2014 at 11:30 am
SELECT
COALESCE(t1.Account, t2.Account) AS Account,
MAX(Date) AS Date
FROM (
SELECT Account, MAX(Date) AS Date
FROM dbo.table1
GROUP BY Account
) AS t1
FULL OUTER JOIN (
SELECT Account, MAX(Date) AS Date
FROM dbo.table2
GROUP BY Account
) AS t2 ON
t2.Account = t1.Account
GROUP BY
COALESCE(t1.Account, t2.Account)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 11, 2014 at 9:14 am
Hopefully I understood the question correctly. Here is another option. First, here is some sample data:
-- Set up sample tables
if object_id('tempdb..#Table1', 'u') is not null
drop table #Table1
if object_id('tempdb..#Table2', 'u') is not null
drop table #Table2
create table #Table1
(AccountID int,
SomeDate datetime)
insert into #Table1 values (1, '1/20/2014')
insert into #Table1 values (1, '2/01/2014')
insert into #Table1 values (1, '5/13/2014')
insert into #Table1 values (2, '4/10/2014')
insert into #Table1 values (2, '4/30/2014')
insert into #Table1 values (3, '6/05/2014')
create table #Table2
(AccountID int,
SomeDate datetime)
insert into #Table2 values (1, '6/23/2014')
insert into #Table2 values (1, '7/02/2014')
insert into #Table2 values (2, '5/15/2014')
insert into #Table2 values (2, '6/09/2014')
insert into #Table2 values (3, '7/01/2014')
insert into #Table2 values (3, '7/10/2014')
To work our way into the solution, first, a simple union will combine the data in both tables:
select AccountID, SomeDate
from #Table1
union
select AccountID, SomeDate
from #Table2
order by AccountID, SomeDate desc
This gives me:
AccountID SomeDate
----------- -----------------------
1 2014-07-02 00:00:00.000
1 2014-06-23 00:00:00.000
1 2014-05-13 00:00:00.000
1 2014-02-01 00:00:00.000
1 2014-01-20 00:00:00.000
2 2014-06-09 00:00:00.000
2 2014-05-15 00:00:00.000
2 2014-04-30 00:00:00.000
2 2014-04-10 00:00:00.000
3 2014-07-10 00:00:00.000
3 2014-07-01 00:00:00.000
3 2014-06-05 00:00:00.000
Now let's take that a step further and treat that entire union query as the table we are querying (we call this a subquery) and we can simply do a group by and a max:
select AccountID, max(SomeDate) as 'MaxDate'
from (select AccountID, SomeDate
from #Table1
union
select AccountID, SomeDate
from #Table2) a
group by AccountID
order by AccountID
Which gives me:
AccountID MaxDate
----------- -----------------------
1 2014-07-02 00:00:00.000
2 2014-06-09 00:00:00.000
3 2014-07-10 00:00:00.000
Two things to call out... 1) Notice that in the union query by itself, I included an order by. But when using the union as a subquery you cannot include an order by. 2) A subquery must always have an alias. In this case, I named it "a". But since it's the only thing I'm querying against, I did not need to preface the columns in the select, group by or order by with "a".
Hope that helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply