x

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

more ▼

asked Dec 30, 2010 at 04:18 AM in Default

avatar image

Prasanna
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Dec 30, 2010 at 04:40 AM

avatar image

Magnus Ahlkvist
22k 20 41 42

@Magnus Ahlkvist Thanks a lot , it worked like a charm.. Thanks for the needful.

Dec 30, 2010 at 05:06 AM Prasanna
(comments are locked)
10|1200 characters needed characters left
 select *
 from  (
           select *, row_no = row_number() over(partition by ProjectId order by yearMonth desc)
           from   yourtable
       ) t
 where  t.row_no = 1
more ▼

answered Jan 04, 2011 at 03:54 AM

avatar image

Squirrel
2.7k 1 4 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2031
x451
x76
x57
x53

asked: Dec 30, 2010 at 04:18 AM

Seen: 1380 times

Last Updated: Dec 30, 2010 at 04:18 AM

Copyright 2017 Redgate Software. Privacy Policy