Finding Max Date and populate new column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding Max Date and populate new column

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-01-07 : 08:16:49

Hey All

I need some help
Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"

Current results
Fdmsaccountno Parentid Last_Post_Date
878020121884 878233342889 20140105
878020127881 878233342889 20140103
878020128889 878233342889 20131231
878701809880 878233342889 20131217
878020120886 878233342889 20131204
878233342889 878233342889 20131201
878701808882 878233342889 20131130
878020115886 878233342889 20131120
878757050884 878233342889 20131011

Desired results
Fdmsaccountno Parentid Last_Post_Date Parent Last Post Date
878020121884 878233342889 20140105 20140105
878020127881 878233342889 20140103 20140105
878020128889 878233342889 20131231 20140105
878701809880 878233342889 20131217 20140105
878020120886 878233342889 20131204 20140105
878233342889 878233342889 20131201 20140105
878701808882 878233342889 20131130 20140105
878020115886 878233342889 20131120 20140105
878757050884 878233342889 20131011 20140105

My query is

select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc

Looking forward to any help

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-01-07 : 08:53:40

i Think i solved it guys

it be something along the lines or

SELECT * FROM
(
SELECT
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
ROW_NUMBER() OVER (PARTITION BY #Account.Parentid ORDER BY #Account.Last_Post_Date DESC) AS N
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
) s WHERE N=1
and Parentid = '878233342889'

order by [Last_Post_Date] desc

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-07 : 19:44:21

hi, i think you may need outer apply with max...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-08 : 05:46:01

[code]
select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
MAX(#Account.[Last_Post_Date]) OVER (PARTITION BY #Account.Parentid) AS ParentLastPostDate
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources