confused on syntax-combining queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

confused on syntax-combining queries

Author

Topic

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! Jena

select
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.PositionCodeDescription

from
(

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

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-03 : 09:24:16

Great, now I have to go pick my eyebalss of the floor

Do this

Give us sample data

and then tell us what the result set should look like

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jenam
Starting Member

13 Posts

Posted - 2008-04-03 : 09:55:05

vpi table ...
LastName FirstName FromEffectDate ToEffectDate PosStartDate PosReason PosCode
Doe John 2001-10-15 3000-01-01 10-15-2001 Transfer OperPack
Smith Tom 1994-11-28 2001-10-14 1994-11-28 New Hire OperDC

vpc table ...
PosCode PosDescription
OperPack Pack Line Operator
OperDC Descaler Operator

Results ...
Doe John 2001-10-15 3000-01-01 10-15-2001 Transfer OperPack Pack Line Operator 6 Years 11 Months 16 Days
Doe John 1994-11-28 2001-10-14 1994-11-28 New Hire OperDC Descaler Operator 6 Years 6 Months 19 Days

I know the date calculation piece adds 5 additional fields to the end, but they are not needed for the final report.

Thank you. Jena

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources