Selecting the latest months data from a file

[partridge80]

I have a file that contains a number of lines for each month. New data gets added to it each month. The month is in the format YYYYMM and I wish to create a smaller table that only contains the latest month's infomation. I hoped it was as easy as

Select name, address
from clientinfo

...where Max(Date)

but I received an error message, can any body help

From novice SQL man

 

[MeanGreen]

Is your date field defined as datetime or varchar? What error message are you getting? Can you post the query exactly as you have it typed? Here is one possible solution provided your date field is a datetime:
[B}
Select name, address
from clientinfo
where datediff(mm,[datefield],getdate()] = 1

This will get you all rows where the difference in months between your datefield and the current date is one month.
[/B]
Hope this helps.

 

[SQLBill]

If you want to get the latest month's data and you don't know what the latest month is, then you'll need to do something along this line


WHERE datepart(mm,mydate) = (select datepart(mm,max(mydate)))

I don't guarantee that will work, I haven't tested it. But what you need to do (and what I tried to do above) is:
create a where that will compare your data's month to whatever the most recent month is in your data.

-SQLBill

 

[SQLBill]

OOPPPSSS, just tested it and found a BIG MISTAKE...


WHERE datepart(mm,[date]) = (select datepart(mm,max([date])) from clientinfo)

I forgot the FROM and this time I used your names.

-SQLBill

 

[MrFurious]

Why not just do this? (assuming it is a character data type it should work just fine)

select name, address
from clientinfo
where [Date] = (select max([Date]) from clientinfo)

Wouldn't the following return data from multiple years since it is only using the date's month?

WHERE datepart(mm,[date]) = (select datepart(mm,max([date])) from clientinfo)

 

[partridge80]

This is the SQL I was using

SELECT Name, Address, Date
FROM dbo.client_info
WHERE MAX(date)

Here is the error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 5:Incorrect syntax near ')'.


Note: date is in decimal format

Just to clarify, I only want the latest month's info and I want it fully automtic.(So if the latest month is 200211, I don't want to have to put where date = 200211. I want it to pick it automatically.)

Many thanks for your replies

 

[partridge80]

Thank you mr Furious. IT WORKED HOORAY. YOU ARE THE MAN, you have saved my a@*!

 

[SQLBill]

The error you were getting was due to the use of the word DATE. This is one of the many RESERVED words in SQL Server syntax. If you use a RESERVED word for your own table/column whatever name, you must include it in square brackets []. So, this was necessary:

SELECT Name, Address, [Date]
FROM dbo.client_info
WHERE MAX([date])

-SQLBill