July 7, 2009 at 8:56 am
Hi Experts!
I am fairly new to SQL but not new to programming (10+ years exp. in RDBMS's).
I have one table, partrev, that stores revision levels for part numbers. Each revision level has an effective date. There may be many records for each part number, due to expired effective dates.
I want to display ONE record for each part number; the most recent revision with an effective date <= today.
SELECT partnum, revisionnum
FROM dbo.partrev
WHERE (partrev.partnum IN
(SELECT MAX(effectivedate) AS Expr1
FROM dbo.partrev AS partrev_1
WHERE (effectivedate <= GETDATE())
GROUP BY partnum))
When I run this as an SQL query, it gives me the conversion error listed in the subject of this post. What am I doing wrong???
Thanks,
M.Taft
July 7, 2009 at 9:08 am
Im guessing that partrev.effectivedate is a varchar(x) and at least one row contains data that is not a date.
Try
Select * from PartRev where ISDATE(effectivedate) = 0
To isolate the 'corrupt' row(s)
July 7, 2009 at 9:11 am
Hi Dave, and thanks for taking the time to reply!
I ran that query and it returned 0 rows. Argh!
July 7, 2009 at 9:17 am
Sorry , no your query is wrong ,
its attempting to convert Partnum to a dateTime due to this
partrev.partnum IN
(SELECT MAX(effectivedate)
From what you have described you query should be more like this
Select Partnum,revisionnum,max(effectivedate)
from partrev
group by Partnum,revisionnum
Post DDL , Sample data and expect output if not
July 7, 2009 at 9:45 am
Hi Dave, and thanks again...
I tried this but since it groups by revisionnum, it simply gave me one row for each partnum/revisionnum/effectivedate. I'm actually looking for the very LAST revisionnum by effectivedate.
For example, if there are 3 records for part number 1234, they might look like this:
partnum revisionnum effectivedate
---------- ----------- -------------
1234 A 01/23/2007
1234 44-D 02/20/2008
1234 3 03/31/2009
I only want to display the very last record, the one with an effective date of 03/31/2009.
I ended up using this query and it works exactly as I wanted:
SELECT partrev.partnum, partrev.revisionnum, partrev.effectivedate
FROM dbo.partrev AS partrev INNER JOIN
(SELECT partnum, MAX(effectivedate) AS effectivedate
FROM dbo.partrev AS partrev_1
WHERE (effectivedate <= GETDATE())
GROUP BY partnum) AS SelectTbl ON partrev.partnum = SelectTbl.partnum AND partrev.effectivedate = SelectTbl.effectivedate
Thanks again for taking the time to reply!
July 7, 2009 at 11:21 am
Glad you worked it out , if you are using 2005+ there is a smarter way
with cteData(partnum, revisionnum, effectivedate,rownum)
as(
SELECT partrev.partnum, partrev.revisionnum, partrev.effectivedate, row_number() over (partition by partrev.partnum, partrev.revisionnum order by partrev.effectivedate)
FROM dbo.partrev
where effectivedate <= GETDATE()
)
select * from cteData where rownum =1
I think thats should have the same effect
July 8, 2009 at 3:08 pm
be careful with your "MAX" statement since getting that conversion error means that table is storing dates as STRINGS
you shown this in one of your replies
partnum revisionnum effectivedate
---------- ----------- -------------
1234 A 01/23/2007
1234 44-D 02/20/2008
1234 3 03/31/2009
the problem is your date formats of MM/DD/YYYY
The MAX aggregate does string comparisons from left to right. 12/31/1900 is greater than 01/01/2010 because the first characters are 1 and 0 and the 1 is greater than the 0 on the ascii table
instead do this
MAX(convert(datetime,MyStringDate))
this way it converts the string to a datetime before comparing and will correctly compare the dates.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply