• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

How to Perform Repetitive Processing

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
0
DeniseGoodheart
Asked:
DeniseGoodheart
  • 2
  • 2
3 Solutions
 
Brendt HessSenior DBACommented:
There may be a way to insert the missing information without any looping needed.  However, to do this we would need some additional information.  Specifically, we would need:

1)  Sample / example output from qryStatusMissingDates
2)  Sample / example data to be inserted based on the values from qryStatusMissingDates
0
 
ksaulCommented:
To get the while loop to execute at least once you would need a select or set statement that effects at least one row to immediately preceed the while.   After you do that the loop should execute one time the way you have it (because you are setting rowcount = 0).  I think you want something like:

Select * from qryStatusMissingDates where MonthNumber='6' and YearNumber='2007'
--do this once before the while so if there are any it will start the loop
while @@rowcount<>0
begin
set rowcount=0
Select * from qryStatusMissingDates where MonthNumber='6' and YearNumber='2007'
If StatusStartDate<>’6/1/2007
---Insert Missing Data  
Select * from qryStatusMissingDates where MonthNumber='6' and YearNumber='2007'
--do this again right before the end of the loop so if there are still rows it will repeat
end

0
 
DeniseGoodheartAuthor Commented:
Hi bhess1:

Thanks for your great suggestion.  It would be great if this process could be simplified.  The requested information is as follows:

1)  Sample / example output from qryStatusMissingDates
6      2007      6/1/2007      6/2/2007      Pending      79
6      2007      6/3/2007      6/7/2007      Available      79
6      2007      6/8/2007      6/14/2007      Unavailable      79
6      2007      6/22/2007      6/28/2007      Available      79

qryStatusMissingDates SQL Syntax:
SELECT     TOP 100 PERCENT DATEPART([month], StatusStartDate) AS MonthNumber, DATEPART([year], StatusEndDate) AS YearNumber, StatusStartDate,
                      StatusEndDate, StatusType, PropertyMgtID
FROM         dbo.Status
ORDER BY StatusStartDate

2)  Sample / example data to be inserted based on the values from qryStatusMissingDates
StatusStartDate=6/15/2007
StatusEndDate=6/21/2007
StatusType=Available
PropertyMgtID=79

StatusStartDate=6/29/2007
StatusEndDate=6/30/2007
StatusType=Available
PropertyMgtID=79

I plan to have a stored procedure pass the following values for the missing dates as follows:
StatusStartDate
StatusEndDate

The following values never change:
StatusType=Available
PropertyMgtID=79

Thanks,
Denise
0
 
Brendt HessSenior DBACommented:
Below is a proc that will update one month's missing information.  It makes no assumptions that I can find, and should handle any combination of missing records, record spans overlapping months, &c

CREATE PROCEDURE UpdateOneMonth
      @StartDate DATETIME -- Should be 1st of month, but not assumed to be
AS

DECLARE @MonthNumber SMALLINT
DECLARE @YearNumber SMALLINT

DECLARE @StartOfMonth DATETIME
DECLARE @EOM DATETIME
DECLARE @MinDate DATETIME
DECLARE @MaxDate DATETIME

SET @StartOfMonth = CAST(CAST(@Startdate - DAY(@StartDate) + 1 AS int) AS DATETIME)

SET @EOM = DATEADD(MONTH,1,@StartOfMonth) -1

CREATE TABLE #tmp (
      StatusStartDate SMALLDATETIME,
      StatusEndDate SMALLDATETIME,
      StatusType VARCHAR(16)
      )
INSERT INTO #tmp
SELECT q1.StatusStartDate,
      q1.StatusEndDate,
      q1.StatusType
FROM qryStatusMissingDates q1
WHERE MonthNumber = 6
      AND YearNumber = 2007

SET @MinDate = IsNull((SELECT MIN(StatusStartDate) FROM #tmp), @EOM + 1)
SET @MaxDate = ISNULL((SELECT MAX(StatusEndDate) FROM #tmp),@EOM + 1)
-- Does the first record reach to the start of the month?
-- If not, add a record for that

IF @StartOfMonth < @MinDate
BEGIN
      -- Check to see if there is a record overlapping from the previous month
      IF EXISTS (
            SELECT *
            FROM qryStatusMissingDates
            WHERE StatusEndDate >= @StartOfMonth
                  AND StatusStartDate < @StartOfMonth
            )
      BEGIN
            INSERT INTO #tmp
            SELECT @StartOfMonth,
                  StatusEndDate,
                  StatusType
            FROM qryStatusMissingDates
            WHERE StatusEndDate >= @StartOfMonth
                  AND StatusStartDate < @StartOfMonth

      END
      ELSE
      BEGIN
            INSERT INTO #tmp VALUES (@StartOfMonth, @MinDate-1, 'missing')
      END
END

IF @EOM > @MaxDate
BEGIN
      INSERT INTO #tmp VALUES (@MaxDate + 1, @EOM, 'missing')
END

INSERT INTO #tmp
SELECT
      q1.StatusEndDate + 1,
      Q2.StatusStartDate -1,
      'missing'
FROM #tmp q1,
      #tmp q2
WHERE q2.StatusStartDate = (SELECT MIN(StatusStartDate) FROM #tmp WHERE StatusStartDate > q1.StatusEndDate)
      AND q2.StatusStartDate <> q1.StatusEndDate + 1

INSERT INTO dbo.Status (
      StatusStartDate,
      StatusEndDate,
      StatusType,
      PropertyMgtID
      )
SELECT StatusStartDate,
      StatusEndDate,
      'Available',
      79
FROM #tmp
WHERE statusType = 'missing'

DROP TABLE #tmp

GO
UpdateOneMonth '20070601'
0
 
DeniseGoodheartAuthor Commented:
Many Thanks,
Denise
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now