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

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

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

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Because this applies to the RealLastOrderDate computed field also in DATEDIFF
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.
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

Pawan KumarDatabase ExpertCommented:
Glad that it work for you. Happy to help :)
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

David DAuthor Commented:
Very good help
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.