Problem with DATEDIFF and computed columns

Hi

I have the following code on MS SQL Server 2012:

ALTER VIEW [dbo].[ExtendedOrderHeaderView]
AS
SELECT        H.*
	,dbo.PreviousOrderDate(H.custID) as [PreviousOrderDate] 
	,RealOrderCount = (SELECT count(o.orderno) FROM OrderHeaders O WHERE (O.custid=H.CustID) and (OrderType=0))
	,RealLastOrderDate = (SELECT max(o.createdat) from OrderHeaders O WHERE (o.custid=H.custid) and (OrderType=0))
	,DaysSincePreviousOrder = DATEDIFF(DAY,h.[CreatedAt],[PreviousOrderDate])

FROM            dbo.OrderHeaders as H
GO

Open in new window


It gives an Invalid column name 'PreviousOrderDate' on the DATEDIFF when trying to compile. It accepts the table field, but not the computed field name
David DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
You have to use like below -

Use Cross apply to get the [PreviousOrderDate] and the use in another function.

ALTER VIEW [dbo].[ExtendedOrderHeaderView]
SELECT        
	 H.*
	,r.[PreviousOrderDate] 
	,RealOrderCount = (SELECT count(o.orderno) FROM OrderHeaders O WHERE (O.custid=H.CustID) and (OrderType=0))
	,RealLastOrderDate = (SELECT max(o.createdat) from OrderHeaders O WHERE (o.custid=H.custid) and (OrderType=0))
	,DaysSincePreviousOrder = DATEDIFF(DAY,h.[CreatedAt],r.[PreviousOrderDate])
FROM dbo.OrderHeaders as H
CROSS APPLY
( 
	SELECT [PreviousOrderDate] FROM dbo.PreviousOrderDate(H.custID) 
)r

Open in new window

0
Pawan KumarDatabase ExpertCommented:
or you can also use like below -

ALTER VIEW [dbo].[ExtendedOrderHeaderView]
AS
SELECT        
	 H.*
	,dbo.PreviousOrderDate(H.custID) as [PreviousOrderDate] 
	,RealOrderCount = (SELECT count(o.orderno) FROM OrderHeaders O WHERE (O.custid=H.CustID) and (OrderType=0))
	,RealLastOrderDate = (SELECT max(o.createdat) from OrderHeaders O WHERE (o.custid=H.custid) and (OrderType=0))
	,DaysSincePreviousOrder = DATEDIFF(DAY,h.[CreatedAt],dbo.PreviousOrderDate(H.custID))

FROM            
	dbo.OrderHeaders as H
GO

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Updated for 1st comment
ALTER VIEW [dbo].[ExtendedOrderHeaderView]
SELECT        
	 H.*
	,r.[PreviousOrderDate] 
	,RealOrderCount = (SELECT count(o.orderno) FROM OrderHeaders O WHERE (O.custid=H.CustID) and (OrderType=0))
	,RealLastOrderDate = (SELECT max(o.createdat) from OrderHeaders O WHERE (o.custid=H.custid) and (OrderType=0))
	,DaysSincePreviousOrder = DATEDIFF(DAY,h.[CreatedAt],r.[PreviousOrderDate])
FROM dbo.OrderHeaders as H
CROSS APPLY
( 
	SELECT dbo.PreviousOrderDate(H.custID) as [PreviousOrderDate]
)r

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David DAuthor Commented:
So it will not accept computed fields in DATEDIFF ?

Because this applies to the RealLastOrderDate computed field also in DATEDIFF
0
Pawan KumarDatabase ExpertCommented:
So it will not accept computed fields in DATEDIFF ?

No because we are computing the field there only. With only APPLY ( Cross and Outer ) operators it works.

Pls try my last 2 comments - they should work.
0
David DAuthor Commented:
Thank you so much :-)

I ended up with this

ALTER VIEW [dbo].[ExtendedOrderHeaderView]
AS
SELECT        H.*
	,r.PreviousOrderDate
	,RealOrderCount = (SELECT count(o.orderno) FROM OrderHeaders O WHERE (O.custid=H.CustID) and (OrderType=0))
	,r.RealLastOrderDate
	,DaysSincePreviousOrder = DATEDIFF(DAY,r.RealLastOrderDate,r.[PreviousOrderDate])

FROM            dbo.OrderHeaders as H
CROSS APPLY
(
select dbo.PreviousOrderDate(H.custID) as [PreviousOrderDate]
,RealLastOrderDate = (SELECT max(o.createdat) from OrderHeaders O WHERE (o.custid=H.custid) and (OrderType=0)) 
) r
GO

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Glad that it work for you. Happy to help :)
0
Pawan KumarDatabase ExpertCommented:
This would be more good ..

Pls try

ALTER VIEW [dbo].[ExtendedOrderHeaderView]
SELECT        
	 H.*
	,r1.[PreviousOrderDate] 
	,r1.RealOrderCount 
	,r1.RealLastOrderDate
	,r1.DaysSincePreviousOrder
FROM dbo.OrderHeaders as H
CROSS APPLY
(
	SELECT 
	  DATEDIFF(DAY,h.[CreatedAt],dbo.PreviousOrderDate(H.custID)) DaysSincePreviousOrder
	, dbo.PreviousOrderDate(H.custID) as [PreviousOrderDate]
	count(o.orderno) RealOrderCount , max(o.createdat) RealLastOrderDate 
	FROM OrderHeaders O WHERE O.custid=H.CustID and OrderType=0
)r1

Open in new window

0
David DAuthor Commented:
Very good help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.