Only show max date value in results

wilpitz
wilpitz used Ask the Experts™
on
I am mainly a crystal reports user and this is where this code came from but I want to use it in SQL. For this statement 2 records are showing due to 2 diffrent closed dates. In Crystal I would add a group, sort detail by the closed date desedning, and add the closed date to the group and only export the group headers and not the detail.

The result I am getting is :
Acct          Curr Closed   QTR Seq         Closed Date
test      Y      5      2008-02-25 00:00:00.000
test      Y      5      2008-01-30 00:00:00.000

I only want to see the record of 2008-02-25 00:00:00.000 (the highest closed date

SELECT "FDM"."Account Number", "FDM"."Current Month Close Indicator", "Date_List"."Quarter Sequence", "FDM"."Close Date"
 FROM   "FDM"."dbo"."Date List" "Date_List" INNER JOIN "FDM"."dbo"."FDM" "FDM" ON "Date_List"."Date"="FDM"."As of Date"
 WHERE  "FDM"."Current Month Close Indicator"=N'y' AND "FDM"."Account Number"=N'test'
 ORDER BY "Date_List"."Quarter Sequence", "FDM"."Account Number", "FDM"."Close Date" DESC

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
Add this condition

AND FDM."Close Date" = (SELECT MAX( [Close Date]  FROM "FDM"."dbo"."FDM"  fd where fd."Account number" = fdm."Account number" )

Author

Commented:
So I have now

SELECT     FDM.[Account Number], FDM.[Current Month Close Indicator], Date_List.[Quarter Sequence], FDM.[Close Date]
FROM         [Date List] AS Date_List INNER JOIN
                      FDM AS FDM ON Date_List.Date = FDM.[As of Date]
WHERE     (FDM.[Current Month Close Indicator] = N'y') AND (FDM.[Account Number] = N'test')
ORDER BY Date_List.[Quarter Sequence], FDM.[Account Number], FDM.[Close Date] DESC
AND FDM."Close Date" = (SELECT MAX( [Close Date]  FROM "FDM"."dbo"."FDM"  fd where fd."Account number" = fdm."Account number" )

and I am getting this error:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.
Please give no credit for this:

SELECT     FDM.[Account Number], FDM.[Current Month Close Indicator], Date_List.[Quarter Sequence], FDM.[Close Date]
FROM         [Date List] AS Date_List INNER JOIN
                      FDM AS FDM ON Date_List.Date = FDM.[As of Date]
WHERE     (FDM.[Current Month Close Indicator] = N'y') AND (FDM.[Account Number] = N'test')
AND FDM."Close Date" = (SELECT MAX( [Close Date] ) FROM "FDM"."dbo"."FDM"  fd where fd."Account number" = fdm."Account number" )
ORDER BY Date_List.[Quarter Sequence], FDM.[Account Number], FDM.[Close Date] DESC
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

AneeshDatabase Consultant
Top Expert 2009

Commented:
put the ORDER BY as the last line

Commented:
You should use a group by clause

Commented:
sorry meant to add the code for the group by
SELECT
 [FDM].[Account Number]
,[FDM].[Current Month Close Indicator]
,[Date_List].[Quarter Sequence]
,max([FDM].[Close Date])
FROM  [FDM].[dbo].[Date List][Date_List] 
INNER JOIN[FDM].[dbo].[FDM][FDM] 
ON[Date_List].[Date]=]FDM].[As of Date]
WHERE [FDM].[Current Month Close Indicator]=N'y' 
AND[FDM].[Account Number]=N'test'
group by  [FDM].[Account Number]
,[FDM].[Current Month Close Indicator]
,[Date_List].[Quarter Sequence]

Open in new window

Data Engineer
Commented:
try this
select [Account Number], [Current Month Close Indicator], [Quarter Sequence], [Close Date] 
  from (
SELECT FDM.[Account Number], 
       FDM.[Current Month Close Indicator], 
       Date_List.[Quarter Sequence], 
       FDM.[Close Date],
       row_number() over (partition by FDM.[Account Number], FDM.[Current Month Close Indicator], Date_List.[Quarter Sequence] 
                          order by FDM.[Close Date] desc) rn
  FROM FDM.dbo.[Date List] Date_List 
 INNER JOIN FDM.dbo.FDM FDM ON Date_List.Date=FDM.[As of Date]
 WHERE FDM.[Current Month Close Indicator] = N'y' AND FDM.[Account Number]=N'test') as t1 where rn = 1

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial