question

Prasanna avatar image
Prasanna asked

Need outPut in a particular format

Hi i have a table which is as below
ProjectId    yearMonth   FiscalYear   Status
--------------    --------------   ---------------  -------------
3622         2009-11-01       2009     Completed
3622         2008-10-01       2008     Started
73944        2007-01-01      2007     Started
73944        2008-01-01      2008     Started
73944        2010-01-01      2010     Completed
73944        2010-01-02      2010     Completed i need the following output from the above table ( Status of the max(FiscalYear) for all the projectId's
Resultant out put should be ProjectId    yearMonth    FiscalYear    Status
-------------    ---------------   ------------------  -----------
3622         2009-11-01       2009         Completed
73944        2010-01-02      2010         Completed

Can any one help me on this..
Regards
Prasanna
sql-server-2005queryaggregatesquery-resultssub-query
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Assuming your table is named **t**. WITH CTE AS( SELECT ProjectID, Max(FiscalYear) as MaxFiscalYear, Max(yearMonth) as MaxYearMonth FROM t GROUP BY ProjectID) SELECT t.ProjectID, t.FiscalYear, t.yearMonth, t.Status FROM t INNER JOIN CTE ON t.FiscalYear=CTE.MaxFiscalYear and t.ProjectID = CTE.ProjectID And t.yearMonth=CTE.MaxYearMonth
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Prasanna avatar image Prasanna commented ·
@Magnus Ahlkvist Thanks a lot , it worked like a charm.. Thanks for the needful.
0 Likes 0 ·
Squirrel avatar image
Squirrel answered
select * from ( select *, row_no = row_number() over(partition by ProjectId order by yearMonth desc) from yourtable ) t where t.row_no = 1
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.