Cursor comparing multiple records 1

[dswitzer]

If there is an easier way to do this -- I am all ears...Right now, my one-track mind screams "CURSOR!"....

I have a table, sorted by SORT_ID:

SORT_ID ID START_DT END_DT PENDING COMPLETED
1 X 1/15/03 1/21/03
2 X 1/17/03 2/17/03
3 Y 1/05/03 1/11/03
4 Y 3/10/03 3/16/03
5 Y 4/1/03 4/31/03
6 Y 4/28/03 4/31/03
7 Y 5/1/03 5/7/03


I want to take that table and define where each record is chronologically -- for example -- sort_id=2 has ID=X, on 1/17/03 when this record ocurred -- how many pending/good records existed (assume if time expires it moves from pending to good) -- so when I see record 2 on 1/17/03, I know I have 1 Pending (sort_id=1 is not yet expired), 0 Completed records.

Example 2 -- when I hit sort_id=6, I want to know I have 1 pending record (sort_id=5 is still pending) and 2 completed (sort_id 3,4 are completed by 4/28 (the start_date of sort_id=6).

SORT_ID ID START_DT END_DT PENDING COMPLETED
1 X 1/15/03 1/21/03 0 0
2 X 1/17/03 2/17/03 1 0
3 Y 1/05/03 1/11/03 0 0
4 Y 3/10/03 3/16/03 0 1
5 Y 4/1/03 4/31/03 0 2
6 Y 4/28/03 4/31/03 1 2
7 Y 5/1/03 5/7/03 0 3

In order to do this -- I need to keep each date as I scroll down through the cursor -- so I can calculate at each record with the same ID.....

Unfortunately, my cursor skills in SQL Server are not too great - I can scroll through a dataset and compare records -- but can't find a good example of how to "keep" a variable list of dates for these calculations....

Ideas appreciated -- and, if you have a better way -- I am all ears...

Thanks.

 

[dswitzer]

Now that I think about it -- I could make 2 copies of the table -- tbl1 I cursor through -- at each record I query tbl2 to see which records match the date periods I need (based on the cursor) and update tbl1 accordingly....this avoids an overly complex cursor -- the tradeoff being that I will run several hundred thousand queries against tbl2....

Any other thoughts?

 

[smin]

dswitzer,

Try this UDF.

create function counting (
@id char(1),
@startDate datetime,
@parm3 char(1) )
returns int
as
begin
declare @pending int
declare @completed int
declare @outvalue int

-- count pending ids
select @pending = count(id)
from theTable
where startDate < @startDate and
endDate > @startDate and
id = @id

-- count completed ids
select @completed = count(id)
from theTable
where startDate < @startDate and
endDate < @startDate and
id = @id

-- pending or completed
select @outvalue
= case @parm3 when 'p' then @pending
when 'c' then @completed
end

return(@outvalue)
end

Call the function like this;

select *, dbo.counting(id,startdate,'p') as pending,
dbo.counting(id,startdate,'c') as completed
from theTable

For the sort id 7, completed is 4, isn't it?

 

[dswitzer]

smin - thanks for the response.(Correct on Sort_id=7, completed should be 4)

I think I see where you are going with this and it looks like it will be much faster than the &quot;double-nested&quot; cursors I have running....(ugh).

I am not familiar with UDF and got a whole series of errors:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Server: Msg 137, Level 15, State 1, Line 15
Must declare the variable '@startDate'.
Server: Msg 137, Level 15, State 1, Line 22
Must declare the variable '@startDate'.
Server: Msg 137, Level 15, State 1, Line 28
Must declare the variable '@parm3'.
Server: Msg 178, Level 15, State 1, Line 32
A RETURN statement with a return status can only be used in a stored procedure.

I'll start trying to figure it out. My Query Analyzer version is 7.0 -- but I think our servers are 2000 (if there is such a version).

Thanks again.

 

[JamesLean]

Are you sure your server is 2000? It looks very much like you're running 7. User functions were new to SQL 2000 - you can't create them in SQL 7 (which is why you get the first syntax error near 'function').

If they are SQL 2000 then check they are not running at compatibility level 70.

--James

 

[iker3000]

Lets say, your table is named Table_1É

Try this:

SELECT
Table_1.Sort_ID,
SUM(CASE
WHEN Table_1.START_DT >= Table_2.END_DT THEN 1
ELSE 0
END) AS Completed,
SUM(CASE
WHEN Table_1.START_DT >= Table_2.END_DT THEN 0
ELSE 1
END) AS Pending
FROM
Table_1
LEFT OUTER JOIN Table_1 AS Table_2
ON Table_1.ID = Table_2.ID
AND Table_1.START_DT > Table_2.Start_DT

This is easy, if your table has realy many rows, you should consider using a cursor - it is not that hard.

 

[dswitzer]

Golden code iker3000....golden code.....

Much faster than my cursor.

Thanks.