SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Finding Max Date and populate new column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 01/07/2014 :  08:16:49  Show Profile  Reply with Quote
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 - 01/07/2014 :  08:53:40  Show Profile  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 01/07/2014 :  19:44:21  Show Profile  Reply with Quote
hi, i think you may need outer apply with max...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/08/2014 :  05:46:01  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000