Scalar function using recursion 3

[iamkovaks]

Hello people,
Im new, please be nice

I have a bit of an issue with trying to write my first scalar function. So let me start at the beginning..

I am using a view to link a table back to itself on 3 fields. The depot number, an item code and the date of a record (only the date not the time hence the conversion in the code). I am linking each record back to the similar record from the day before so I can do a sum on the item quantities that subtracts the quantity from today, to the quantity from the previous day. This is an update that will run every night to populate a field within the table.

Unfortunatly this only works when there is a record everyday, for some reason there are days that are missed. Whether the job didnt complete correctly or there is another reason the data hasnt been populated I'm unsure, but I have to deal with this.

I have been told to create the function to check if the link exists to yesterday and if so use the data it finds, otherwise I need it to check the previous day to see if there is a matching record. What I have so far is...

CREATE FUNCTION [dbo].[prev_qty ]
(
-- Add the parameters for the function here
@date datetime,
@code int,
@depot int

)
RETURNS int
AS
BEGIN
DECLARE @prev_qty int

IF EXISTS (SELECT qty from table WHERE @code = code AND @depot = depot AND @date=(CONVERT(CHAR(10), DATEADD(d, - 1, table1.tDate), 103)))

SET @prev_qty = (SELECT qty from table WHERE @code = code AND @depot = depot AND @date=(CONVERT(CHAR(10), DATEADD(d, - 1, table1.Date), 103)))

ELSE
IF EXISTS (SELECT qty from table WHERE @code = code AND @depot=depot and @date=(CONVERT(CHAR(10), DATEADD(d, - 2, table1.date), 103)))

SET @prev_qty = (SELECT qty from table WHERE @code = code AND @depot=depot and @date=(CONVERT(CHAR(10), DATEADD(d, - 2, table1.date), 103)))

END

-- Return the result of the function
RETURN @prev_qty

END

Im getting syntax errors for a start, which probably means I have done somthing silly and simple to correct. But also, is this code correct? I was told to use a recursive call but as im still learning I a, have never used recursion before and b, have never written a function like this before!

Any help appreciated, and I hope I have explained myself well enough that you arent all confused just trying to work out what I am doing!

 

[imex]

Hi,

Try something like that:

BEGIN
DECLARE @prev_qty int;
DECLARE @qty_days int;

SET @prev_qty = null;
SET @qty_days = 0;

while @prev_qty is null and @qty_days > -2
  begin
    SET @qty_days = @qty_days - 1;
    SELECT @prev_qty = qty from table 
    WHERE @code = code AND @depot = depot AND DATEADD(D, @qty_days, @date) = CONVERT(CHAR(10), tDate, 102);
  end
END;

-- Return the result of the function
RETURN @prev_qty;
END

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[gmmastros]

You worded your question very well. However, I think it would help a lot if you posted some sample data and expected results. This will make it a lot easier for us to help you. The sample data does not need to be "real", but it should be representative of the problem you are trying to solve.

By the way, recursion in SQL Server is quite slow, so it may be better to solve this problem another way. Are you required to use recursion, or would another approach work (as long as it generates the correct results)?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[iamkovaks]

I was told by a more senior developer to use recursion for this. I had originally tried the below to go through the records and find the next instance but the way I have done it obviously doesnt work as it didnt give me any results.

(
@theDate datetime,
@yesterday_date datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @prev_date datetime
SELECT @prev_date =
CASE
WHEN ISNULL(@yesterday_date, 0) = 0 THEN
(SELECT MAX(@yesterday_date) FROM vw_JN_onhire_daily WHERE yesterday_date < theDate)
ELSE @yesterday_date
END
RETURN @prev_date
END


What my end result is trying to acheive is below. The Daily Qty is the calculation I am doing to get the total. This is all in an update query (I have shown below the SELECT which I then use for the update). Which so far works when there is a previous day record, but we need some way to find the previous existing record when it doesnt. Hence the function which can then be called in the query.

SELECT pu_today.theDate, pu_today.depot, pu_today.code, pu_today.qty, ISNULL(pu_yesterday.ons, 0) AS yesterday_qty,
ISNULL(pu_yesterday.theDate, 0) AS yesterday_date, pu_today.qty- ISNULL(pu_yesterday.ons, 0) AS today_qty

FROM dbo.utilprodgrp AS pu_today LEFT OUTER JOIN
dbo.utilprodgrp AS pu_yesterday ON pu_today.depot = pu_yesterday.depot AND pu_today.code = pu_yesterday.code AND CONVERT(CHAR(10), DATEADD(d, - 1,
pu_today.theDate), 103) = CONVERT(CHAR(10), pu_yesterday.theDate, 103)

Today Depot Code Today Qty yesterday Qty Yesterday date Daily Qty
04/08/11 02 02         192 192 03/08/11 0
05/08/11 02 02         192 192 04/08/11 0
06/08/11 02 02         192 192 05/08/11 0
07/08/11 02 02         192 192 06/08/11 0
08/08/11 02 02         191 192 07/08/11 -1
09/08/11 02 02         191 191 08/08/11 0
10/08/11 02 02         190 191 09/08/11 -1
11/08/11 02 02         190 190 10/08/11 0
(im being innept with pasting results so have made everyother column bold!)

If you can think of a quicker solution that recursion that would be awesome! Meanwhile I shall try what IMEX has suggested.
Thanks

 

[gmmastros]

Here's the way I see it. You are trying to join the table to itself. Your current code joins based on depot, code, and date, where one table joins to the other on date-1 (to get yesterday and today to line up in the same row).

The problem is, there may be dates missing, so the join doesn't work so well.

My idea is this... Instead of joining on date, let's use the row_number() function to get a simple integer. We can then self-join on this integer. This will not depend on there being a row for every date.

First, let's build a table variable that represents the data in your original table.

Declare @Original
Table	(
		theDate DateTime,
		Depot VarChar(10),
		Code  VarChar(10),
		Qty Int)

Insert Into @Original Values('04/08/11','02','02',192)
Insert Into @Original Values('05/08/11','02','02',192)
Insert Into @Original Values('06/08/11','02','02',192)
Insert Into @Original Values('07/08/11','02','02',192)
--Insert Into @Original Values('08/08/11','02','02',191)
Insert Into @Original Values('09/08/11','02','02',191)
Insert Into @Original Values('10/08/11','02','02',190)
Insert Into @Original Values('11/08/11','02','02',190)

Notice that I commented a row so there would be a missing date (August 8).

Now, let's build a query that attaches a row number to the data, like this:

Declare @Original
Table	(
		theDate DateTime,
		Depot VarChar(10),
		Code  VarChar(10),
		Qty Int)

Insert Into @Original Values('04/08/11','02','02',192)
Insert Into @Original Values('05/08/11','02','02',192)
Insert Into @Original Values('06/08/11','02','02',192)
Insert Into @Original Values('07/08/11','02','02',192)
--Insert Into @Original Values('08/08/11','02','02',191)
Insert Into @Original Values('09/08/11','02','02',191)
Insert Into @Original Values('10/08/11','02','02',190)
Insert Into @Original Values('11/08/11','02','02',190)

Select theDate,
       Depot,
       Code,
       Qty,
       Row_Number() Over(Partition By Depot, Code Order By theDate) As RowId
From   @Original

Notice that the RowId column is just a sequential value from 1 to 7. Also take note that August 8th is still missing because it is commented out. Go ahead and un-comment this row and run it again. You'll see that the RowId is now numbered 1 through 8 just as you would expect.

Now we can use this RowId column for the self join, like this:

Declare @Original
Table	(
		theDate DateTime,
		Depot VarChar(10),
		Code  VarChar(10),
		Qty Int)

Insert Into @Original Values('04/08/11','02','02',192)
Insert Into @Original Values('05/08/11','02','02',192)
Insert Into @Original Values('06/08/11','02','02',192)
Insert Into @Original Values('07/08/11','02','02',192)
--Insert Into @Original Values('08/08/11','02','02',191)
Insert Into @Original Values('09/08/11','02','02',191)
Insert Into @Original Values('10/08/11','02','02',190)
Insert Into @Original Values('11/08/11','02','02',190)

; With Data As
(
    Select theDate,
           Depot,
           Code,
           Qty,
           Row_Number() Over(Partition By Depot, Code Order By theDate) As RowId
    From   @Original
)
Select *,
       Today.Qty - Yesterday.Qty
From   Data As Today
       Inner Join Data As Yesterday
          On Today.Depot = Yesterday.Depot
          And Today.Code = Yesterday.Code
          And Today.RowId = Yesterday.RowId + 1

The code above takes the original query and makes a common table expression from it. The query below that shows how you can join the common table expression (named data) to itself to get the data you were looking for.

If you are satisfied that the query I show above generates the correct output, then simply modify it to use your actual table instead.

Of course, if you have any questions about this query, let me know.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom