Solved

How to Perform Repetitive Processing

Posted on 2007-04-03
5
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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