Closed

MS SQL 2008 Recursive Query

This project was awarded to wizziewizard for $30 USD.

Get free quotes for a project like this
Employer working
Awarded to:
Skills Required
Project Budget
$30 - $250 USD
Total Bids
13
Project Description

I have an [url removed, login to view] 4.0 application that uses an SQL 2008 database with a table called PERMIT. This Permit table has several fields but the fields of concern are

MCDNUM (FLOAT) (The permit number)

REPLACED (NVARCHAR) (Replaced this permit by another permit#)

OLD (NVARCHAR) (What the old permit number was before this one replaced it)

DATEISSUED (DATE) The date permit was issued

I prefer that you construct and test your own database rather than try to populate with my data. My data has under 5000 records but is sensitive.

The MCDNUM is a permit number. When the permit is renewed it is given a new number (A new record in the database) and then it REPLACED field gets entered this new MCDNUM. The new permit record gets the OLD the the MCDNUM from the "old" permit. It is possible that a permit is renewed and there are multiple new permits from it. When this happens the old permit number can only hold one number but the new MCDNUMS can have the same "OLD" MCDNUM So for example:

MCDNUM REPLACED OLD

---------------------------------------

100 200

200 100

250 300

300 400 250

400 300

500 400

600 1000 400

700 400

1000 600

What i need is a query or mixture of a stored procedure that will allow me to enter an MCDNUM that will recursively look up ALL the associated record's MCDNUM and the DATEISSUED in the order of DATEISSUED

So if I were to search for MCDNUM 100 It would find 100 and 200

It has to search up and down in both the REPLACED and OLD fields and all of those records (REPLACED and OLD) fields. So if I searched 1000, I would get 600, 400, 300, 250

I think the results need to go into an array maybe? so that the same MCDNUMS are not duplicated as the final results will be a record set with the following:

MCDNUM, REPLACED, OLD, DATEISSUED order by the DateIssued field

I want error correction in case one of the fields is null or has an invalid data. Ultimately I will need the results of this query in a Gridview that will look at a URL parameter called MCDNUM which will plug in the search requirement number. So I am open to any suggestions or workflow.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online