I have an ASP.NET 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
300 400 250
600 1000 400
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.