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.
Author |
Topic |
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2014-02-21 : 06:26:23
|
Hi,This is my table:ID AppName DepCode DepName Group ModifiedDate YearlyAmount1 Nestle NS Foods Products 01/12/14 4511 Nestle NS Foods Products 01/17/14 4952 Oracle OR Software Info 01/24/14 2792 Oracle OR Soft & IT Info 01/26/14 3102 Oracle ORL Software Info 01/25/14 2192 Oracle ORL Soft IT 01/28/14 600MonthlyAmount Funded AppCategory Research37.5623 Yes NE NA41.2365 No N NA23.2568 Yes OR InProgress25.8333 Yes ORL NA18.2189 Yes SOF Approved50.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 819MonthlyAmount Funded AppCategory Research 78.7988 No N NA49.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 B1INNER JOIN(select [ID], MAX(ModifiedDate) as ModifiedDate, SUM(YearlyAmount) as YearlyAmount,SUM(MonthlyAmount) as MonthlyAmount from BusinessGroup by ID) B2ON B1.ID = B2.ID AND B1.ModifiedDate = B2.ModifiedDateAnyone please correct me or advice how to solve this as I'm a starter in SQL. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-23 : 06:14:41
|
[code]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 SeqFROM Business)tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|