asked on 7/23/2008

Return Client Id's where the difference between orders is over 100 days

I have a function that returns the number of days between orders. See the code snippet below:
What I need is a function that will return a list of client id's where the datediff between the latest order and the order before it is over 100 days.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION DaysBetweenInvoices
(
	@Client_id int
)
RETURNS int
AS
BEGIN
 
	DECLARE @Records int
	DECLARE @Result int
	DECLARE @FirstDate datetime
        DECLARE @LastDate datetime
 
	SET @LastDate = (SELECT MAX(mactemps.dbo.Order_Invoice.order_invoice_date) 
	FROM mactemps.dbo.Order_Invoice 
	WHERE mactemps.dbo.Order_Invoice.Client_id = @Client_id)
 
        SET @Records = (SELECT COUNT(*) FROM mactemps.dbo.Order_Invoice WHERE 	    			mactemps.dbo.Order_Invoice.Client_id  = @Client_id)
 
	IF @Records = 1
		BEGIN
			Set @FirstDate = @LastDate
			Set @LastDate = GetDate()
        END
	ELSE
	
	SET @FirstDate = (SELECT MAX(mactemps.dbo.Order_Invoice.order_invoice_date)
	FROM mactemps.dbo.Order_Invoice WHERE mactemps.dbo.Order_Invoice.Client_id = @Client_id AND 		mactemps.dbo.Order_Invoice.order_invoice_date < @LastDate)
 
	SET @Result = DATEDIFF(d,@FirstDate,@LastDate)	
 
	-- Return the result of the function
	RETURN @Result
 
END
GO

Open in new window

Microsoft SQL Server 2005

Last Comment

momi_sabag

7/24/2008

momi_sabag

7/23/2008

you don't need a function
you can do it with a simple sql statement

with cte as
(select row_number() over(partition by client_id order by order_invoice_date desc) as rown , order_invoice_date as dt , client_id
from order_invoice)

select client_id
from cte t1
inner join cte t2
on t1.rown = t2.rown + 1
where t1.rown = 1
and t2.rown = 2
and datediff(d, t1.order_invoice_date , t2.order_invoice_date ) > 100

Brian_Sutherland

7/23/2008

ASKER

I do need a function or a view as I am using a reporting tool to bring in the data and I cannot create sql where I am using derived tables or subqueries. With a function or a view at the database level, I am able to call it in the reporting application. How could I create a view with the sql listed above?

ASKER CERTIFIED SOLUTION

momi_sabag

7/24/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial