jenam
Starting Member
13 Posts |
Posted - 2008-04-03 : 08:56:29
|
I have two queries that I'm trying to combine, but can't figure out how to combine them ... successfully!?! The first query is pretty simple in that I'm looking at several fields from two different tables, no big deal. The second query calculates the years, months, days between two dates that are used in the first query. I'm stumped on how to combine the queries so that they place nice with each other and return results.I will post my feable attempt at merging them. No matter how I order the code, I continue to get the same error, pertaining to the last line of code ... Line 73: Incorrect syntax near 'vpi'.Any help would be greatly appreciated! Thank you! Jenaselect RTRIM(RTRIM(vpi.LastName) + ', ' + RTRIM(ISNULL vpi.FirstName,''))) Employee, convert(varchar,vpi.FromEffectiveDate,101) PositionStart, convert(varchar,vpi.ToEffectiveDate,101) PositionChange, convert(varchar,vpi.PositionStartDate,101) PositionStartDate, datediff(hour,vpi.FromEffectiveDate,vpi.ToEffectiveDate) as time_diff, vpi.PositionReason, vpi.PositionCode, vpc.PositionCodeDescriptionfrom (select [Age] = convert(varchar, [Years]) + ' Years ' + convert(varchar, [Months]) + ' Months ' + convert(varchar, [Days]) + ' Days', *from( select [Years] = case when BirthDayThisYear <= Today then datediff(year, BirthYearStart, CurrYearStart) else datediff(year, BirthYearStart, CurrYearStart) - 1 end, [Months]= case when BirthDayThisYear <= Today then datediff(month, BirthDayThisYear, Today) else datediff(month, BirthDayThisYear, Today) + 12 end, [Days]= case when BirthDayThisMonth <= Today then datediff(day, BirthDayThisMonth, Today) else datediff(day, dateadd(month, -1, BirthDayThisMonth), Today) end, Birth = convert(varchar(10) ,Birth, 121), Today = convert(varchar(10), Today, 121) from ( select BirthDayThisYear = case when day(dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)) <> day(Birth) then dateadd(day, 1, dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)) else dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth) end, BirthDayThisMonth = case when day(dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) <> day(Birth) then dateadd(day, 1, dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) else dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth) end, * from ( select BirthYearStart = dateadd(year, datediff(year, 0, Birth), 0), CurrYearStart = dateadd(year, datediff(year, 0, Today), 0), BirthMonthStart = dateadd(month, datediff(month, 0, Birth), 0), CurrMonthStart = dateadd(month, datediff(month, 0, Today), 0), * from ( select birth = convert(datetime, fromeffectivedate) , Today = case when convert(datetime, toeffectivedate) = '3000-01-01' THEN convert(datetime, convert(int,getdate())) else toeffectivedate end, * from vHRL_PositionInfo ) aaaa ) aaa ) aa)a) vHRL_PositionInfo vpi inner join position_codes vpc on vpi.PositionCode = vpc.PositionCode |
|