asked on

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

Pawan Kumar

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 Kumar

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

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David D

ASKER

So it will not accept computed fields in DATEDIFF ?

Because this applies to the RealLastOrderDate computed field also in DATEDIFF

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David D

ASKER

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 Kumar

Glad that it work for you. Happy to help :)

Pawan Kumar

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 D

ASKER

Very good help