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
 Pick recent modified date in the table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vignesht50
Yak Posting Veteran

76 Posts

Posted - 02/21/2014 :  06:26:23  Show Profile  Reply with Quote
Hi,

This is my table:

ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/12/14 451
1 Nestle NS Foods Products 01/17/14 495
2 Oracle OR Software Info 01/24/14 279
2 Oracle OR Soft & IT Info 01/26/14 310
2 Oracle ORL Software Info 01/25/14 219
2 Oracle ORL Soft IT 01/28/14 600

MonthlyAmount Funded AppCategory Research
37.5623 Yes NE NA
41.2365 No N NA
23.2568 Yes OR InProgress
25.8333 Yes ORL NA
18.2189 Yes SOF Approved
50.0000 No IT RejectedExpected Output:

ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/17/14 946
2 Oracle OR Soft & IT Info 01/26/14 589
2 Oracle ORL Soft IT 01/28/14 819

MonthlyAmount Funded AppCategory Research
78.7988 No N NA
49.0901 Yes ORL NA
68.2189 No IT RejectedI want to pick the recent modified date for DepCode and sum Yearly and Monthly Amount. I have tried this query and not able to get the output. This is the single table.

select B1.[ID], B1.[AppName], B1.[DepCode], B1.[DepName], B1.[Group],
B2.ModifiedDate, B2.YearlyAmount, B2.MonthlyAmount,
B1.[FuBded], B1.[AppCategory], B1.[Research]
FROM Business B1
INNER JOIN
(select [ID], MAX(ModifiedDate) as ModifiedDate, SUM(YearlyAmount) as YearlyAmount,
SUM(MonthlyAmount) as MonthlyAmount
from Business
Group by ID) B2
ON B1.ID = B2.ID AND B1.ModifiedDate = B2.ModifiedDate


Anyone please correct me or advice how to solve this as I'm a starter in SQL.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/23/2014 :  06:14:41  Show Profile  Reply with Quote

SELECT *
FROM
(
SELECT ID, AppName, DepCode, DepName, [Group], ModifiedDate, SUM(YearlyAmount) OVER (PARTITION BY DepCode) AS YearlyAmount,
SUM(MonthyAmount) OVER (PARTITION BY DepCode) AS MonthyAmount,Funded, AppCategory, Research,
ROW_NUMBER() OVER (PARTITION BY DepCode ORDER BY ModifiedDate DESC) AS Seq
FROM Business
)t
WHERE Seq=1


------------------------------------------------------------------------------------------------------
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.06 seconds. Powered By: Snitz Forums 2000