How to Perform Repetitive Processing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to Perform Repetitive Processing

Author

Topic

DeniseGoodheart
Starting Member

12 Posts

Posted - 2007-04-04 : 21:28:59

Hi,

I’m using SQL Server 2000. I need to iterate through a view based on parameters and insert missing date ranges. My question is I need help getting started. This is what I have so far and it does not work. I'm hard coding values for simplicity.

while @@rowcount<>0
begin
set rowcount=0
Select * from qryStatusMissingDates where MonthNumber='6' and YearNumber='2007'
If StatusStartDate<>’6/1/2007’
---Insert Missing Data

Thanks,
Denise

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 00:57:15

You can simulate this logic
http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 01:58:01

Or use MVJ's F_TABLE_DATE function.

SELECT d.DATE FROM F_TABLE_DATE('20070601', '20070630') AS d
LEFT JOIN qryStatusMissingDates AS q ON q.StatusStartDate = d.Date
WHERE q.StatusStartDate IS NULL

This will give you all missing dates for June 2007 at once.

Peter Larsson
Helsingborg, Sweden

DeniseGoodheart
Starting Member

12 Posts

Posted - 2007-04-05 : 13:03:45

Thanks All,
Denise

DeniseGoodheart
Starting Member

12 Posts

Posted - 2007-04-07 : 15:12:15

Hi All,

This question has been resolved, but I do not know what is the procedure here for closing a resolved questioned. My syntax that works is as follows:

select date_start_missing, min(date_end_missing) as date_end_missing
from (
select d_begin.StatusEndDate + 1 as date_start_missing, d_end.StatusStartDate as date_end_missing
from dbo.status d_begin
join (select min(StatusStartDate)-1 as StatusStartDate, StatusEndDate from dbo.status group by StatusEndDate) d_end on (d_begin.StatusEndDate + 1 < d_end.StatusStartDate)
where d_begin.StatusEndDate + 1 not in (select StatusStartDate from dbo.status)
) a
group by date_start_missing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources