Link to home
Start Free TrialLog in
Avatar of John Mahoney
John Mahoney

asked on

Cross Apply question using SQL Server 2008 R2

I'm following along with a video by Ben-Gan called Creative Uses of Apply Operator and he shows this code

Select orderiid, orderdate, nextyear
from sales.order
  cross apply(values(year(orderdate))) as a1(orderyear)
    cross apply(values(year(orderyear +))) as a2(nextyear)
where orderyear > 2007

---end code----

He comes up with a result set where the nextyear column is one year later than the orderdate, i.e., if orderdate is 2008-01-01, nextyear is 2009
you can see it video place...

 https://youtu.be/-m426WYclz8?t=1291

However when I try using it with this code on two different databases 2012 AdventureWorks or AdventureworksLT  I use the same code on different column names..


select SalesOrderID,  OrderDate, NextOrderYear
from SalesLT.SalesOrderHeader
      cross apply (values(year(OrderDate))) as a1(orderYear)
            cross apply (values(year(orderYear + 1))) as a2(NextOrderYear)
            where orderYear > 2001

I get the

 NextOrderYear as being 1905

Obviously something is not right. Just not sure what?
Avatar of HainKurt
HainKurt
Flag of Canada image

what is your data and what do you want to get and what do you get from above script?
is this typo?

cross apply(values(year(orderyear +))) as a2(nextyear)
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
question is answered...