Solved

How to Perform Repetitive Processing

Posted on 2007-04-03
5
217 Views
Last Modified: 2010-03-20
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
Comment
Question by:DeniseGoodheart
  • 2
  • 2
5 Comments
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 480 total points
ID: 18845008
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
 
LVL 10

Assisted Solution

by:ksaul
ksaul earned 20 total points
ID: 18845020
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
 

Author Comment

by:DeniseGoodheart
ID: 18845286
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 480 total points
ID: 18847678
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
 

Author Comment

by:DeniseGoodheart
ID: 18854368
Many Thanks,
Denise
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question