Solved

How to Perform Repetitive Processing

Posted on 2007-04-03
5
212 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now