Select calculation issue

[ItHurtsWhenIThink]

I'm trying to do a calculation within my select.

strDate=DateAdd("m",-6,date)

SELECT
PPE_Manufacturers.Manufacturer, PPE_Inspection_Elements.Element,
PPE_MFR_Models.Model, PPE_Items.MFR_Serial,
PPE_Items.MONTH_YR_MFR,
PPE_Items.MONTH_YR_MFR + PPE_Inspection_Elements.LifeYrs AS PLife,
Users.LName,
Users.FName,
PPE_Items.DeptID

FROM

PPE_Items INNER JOIN Users ON PPE_Items.UserID = Users.UserID
INNER JOIN PPE_Inspection_Elements INNER JOIN PPE_MFR_Models INNER JOIN
PPE_Manufacturers ON PPE_MFR_Models.MFRID = PPE_Manufacturers.MFRID
ON PPE_Inspection_Elements.ElementID = PPE_MFR_Models.InspElemID
ON PPE_Items.PMIID = PPE_MFR_Models.ModelID

WHERE

PPE_Items.DeptID= '1' AND
PLife < strDate

Problem child is:
PPE_Items.MONTH_YR_MFR + PPE_Inspection_Elements.LifeYrs AS PLife

Just begining my venture into these types of calculations.

This is an ASP project so I cleaned up the statement, so it may not be 100% accurate. I get an invalid column name 'PLife' error

Thanks...

 

[bborissov]

Your PLife column is not ready yet, so it can't be put in WHERE clause. Use the same calculation there:

SELECT ....
....
WHERE PPE_Items.DeptID= '1' AND
      (PPE_Items.MONTH_YR_MFR + PPE_Inspection_Elements.LifeYrs) < strDate

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ItHurtsWhenIThink]

Thanks,
As I was getting ready to modify the statement I realized that the two fields are not of the same type. The "MONTH_YR_MFR" is a date type and the LifeYrs is numeric.
I wanted to add "yyyy"'s to the date and then compare it to strDate.

here is what I now have, but it still does not work.

Sorry its not cleaned up. Thought i might be best so that you can see the application/intent.

SQLText = "SELECT PPE_Manufacturers.Manufacturer, PPE_Inspection_Elements.Element,"
SQLText =SQLText & " PPE_MFR_Models.Model, PPE_Items.MFR_Serial, PPE_Items.MONTH_YR_MFR,"
SQLText =SQLText & " Users.LName, Users.FName, PPE_Items.DeptID"
SQLText =SQLText & " FROM PPE_Items INNER JOIN Users ON PPE_Items.UserID = Users.UserID"
SQLText =SQLText & " INNER JOIN PPE_Inspection_Elements INNER JOIN PPE_MFR_Models INNER JOIN"
SQLText =SQLText & " PPE_Manufacturers ON PPE_MFR_Models.MFRID = PPE_Manufacturers.MFRID"
SQLText =SQLText & " ON PPE_Inspection_Elements.ElementID = PPE_MFR_Models.InspElemID"
SQLText =SQLText & " ON PPE_Items.PMIID = PPE_MFR_Models.ModelID "
SQLText =SQLText & "WHERE PPE_Items.DeptID='1' AND "


'##### HERE IS THE CRUX OF THE ISSUE #####

SQLText =SQLText & DateAdd("yyyy",PPE_Inspection_Elements.LifeYrs,PPE_Items.MONTH_YR_MFR) & " <'" & strDate & "'"

I'm trying to add LifeYrs to manufacture date (Monmth_YR_Mfr).

 

[r937]

the DateAdd function requires datepart parameters that are keywords, not strings

SELECT PPE_Manufacturers.Manufacturer
     , PPE_Inspection_Elements.Element
     , PPE_MFR_Models.Model
     , PPE_Items.MFR_Serial
     , PPE_Items.MONTH_YR_MFR
     , Users.LName
     , Users.FName
     , PPE_Items.DeptID
  FROM PPE_Items 
INNER 
  JOIN Users 
    ON Users.UserID = PPE_Items.UserID 
INNER 
  JOIN PPE_MFR_Models 
    ON PPE_MFR_Models.ModelID = PPE_Items.PMIID
INNER 
  JOIN PPE_Inspection_Elements 
    ON PPE_Inspection_Elements.ElementID = PPE_MFR_Models.InspElemID
INNER 
  JOIN PPE_Manufacturers 
    ON PPE_Manufacturers.MFRID = PPE_MFR_Models.MFRID
 WHERE PPE_Items.DeptID = 1
   AND DateAdd([blue]year[/blue],PPE_Inspection_Elements.LifeYrs,PPE_Items.MONTH_YR_MFR) 
        < DateAdd([blue],month[/blue],-6,GetDate())

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon