Compare two date fields in same table

[sanctified]

Hi Group,
I will have 2 sets of 'matched' records in a table with a start date field of type datetime. I want to update a field called flag in the table with 1 for the earlier date and 2 for the later date.

example

Start Date Flag
12/07/2006 1
17/07/2006 2

regards

 

[bborissov]

UPDATE MyTable SET Flag = CASE WHEN DateField = Tbl1.DateFiels THEN 1 ELSE 2 END
FROM MyTable
LEFT JOIN (SELECT PK, MIN(DateField) AS DateField
                  FROM MyTable
                  GROUP BY PK) Tbl1
     ON MyTable.PK = Tbl1.PK

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

Thanks again Borislav, but I'm confused with your table definitions. There is only ONE table called sitedata with the above structure. The pk is suspID.

Thanks

 

[bborissov]

I join table to itself to get min(date) for every primary key.
Let say you have table with this structure

MyTable
SiteId int -- Primary key
DateField DateTime
Flag int

and you have following data:

SiteId     DateField     Flag
------------------------------
1          01/01/2006     0
1          01/02/2006     0
1          01/03/2006     0
2          04/01/2006     0
2          04/04/2006     0
3          05/04/2006     0
7          04/05/2006     0

If I undestood your question right you want after update to have:

SiteId     DateField     Flag
------------------------------
1          01/01/2006     1
1          01/02/2006     2
1          01/03/2006     2
2          04/01/2006     1
2          04/04/2006     2
3          05/04/2006     1
7          04/05/2006     1

So try this: (I changed only name of PK field)

UPDATE MyTable SET Flag = CASE WHEN DateField = Tbl1.DateFiels THEN 1 ELSE 2 END
FROM MyTable
LEFT JOIN (SELECT SiteId, MIN(DateField) AS DateField
                  FROM MyTable
                  GROUP BY SiteId) Tbl1
     ON MyTable.SiteId = Tbl1.SiteId

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

Table structure as follows:

siteid SuspensionStart MergeID MergeStatus(Flag)
225 17/07/2006 258 1
258 18/07/2006 225 2

Borislav this is overriding the previous issue you so kindly responded to.
The sp passes in the following parameter @myid which can be compared against the siteid or the mergeid field to get the records to update.
There will only ever be TWO records matching (either on the siteid or mergeid fields) for the @myID passed in.

Kind Regards

 

[bborissov]

Is this any relation where is the min(MergeStatus) resides?
In your example min(MergeStatus) is there where SiteId = 225, so my question is can you have this type of data:

siteid   SuspensionStart     MergeID     MergeStatus(Flag)
  225      19/07/2006          258             1
  258      18/07/2006          225             2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[bborissov]

try this:

UPDATE MyTable SET MergeStatus = 
               CASE WHEN ISNULL(Tbl1.SuspensionStart)
                    THEN 2 ELSE 1 END
FROM MyTable
LEFT JOIN (SELECT MIN(SuspensionStart) AS SuspensionStart
               FROM MyTable
               WHERE SiteId = @myId OR MergeID = @myId) Tbl1
     ON MyTable.SuspensionStart = Tbl1.SuspensionStart
WHERE SiteId = @myId OR MergeID = @myId

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

The mergestatus field is what I will be updating. In answer to your question, no you can't have that data. 1 will always be used for the earlier date and 2 for the later date of the 2 records.

Why are you checking for a null suspensionstart?

 

[sanctified]

Just to recap,
Compare the two dates
If date1 < date2 update mergestatus to 1 for date1 record
Update mergestatus to 2 for date2 record
otherwise do the opposite.

 

[bborissov]

That exacmple must do exactly what you want

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

OK Will give it a go soon.