Query Question - HELP

[OU18]

Here is my situation

I have a table with the following fields

Zip
Create Date
End Date
Count
Peices

The Zip code field can have duplicate values in it.

No problem right, now if Record 1 (Zip) equals Record 2 (zip), i need to take the create date of record 2 subtract 1 from it and populate the end date of record 1. I need to do it in a query. I have no clue of where to even begin to do this. So be gentle with me.

Does that make sense. If not I will try to clarify it.

 

[dodgychris]

I think that this might give you an idea of where to head, it's a bit ugly and makes use of aliasing your tables. I've given your table the name of 'test'.

SELECT a.zip, a.create, b.create, (b.create - a.create) as no_days
(this gives you your fields)
from test a, test b
(by aliasing your table name you can select from the same table with more than one context)
where a.zip = b.zip
(this ensures that your zip codes match)
and a.create <> b.create
and a.create < b.create;
(the two lines above ensure that you don't get duplicate data).

I'm sure that there must be more elegant solutions out there, but I think this is the best I can do...

Hope it helps,

All the best,

DodgyChris
Dodgy Chris
-----------------------------------

confucious say : better to save a mans life than to build 7 storey pagoda

 

[dodgychris]

come to think of it, and having reread your mail, it doesn't help that much... sorry. I'll have another stab at it and get back to you Dodgy Chris
-----------------------------------

confucious say : better to save a mans life than to build 7 storey pagoda

 

[dodgychris]

Okay, i think I've got it this time...
I'm afraid you would have to run it after every record was created, as if there are 3 records with the same zip it takes the maximum date each time... it seems to work though.


UPDATE test AS a, test AS b SET a.end = (b.create-1)
WHERE (((a.zip)=.[zip]) AND ((a.create)<>.[create] And (a.create)<.[create]) AND ((a.end) Is Null));

Pass the ibuprofen... my brain hurts.

Hope this helps,

Dodgy Chris
-----------------------------------

confucious say : better to save a mans life than to build 7 storey pagoda

 

[dodgychris]

that last message didn't go through my server well... hopefully this'll be the last version of this...

UPDATE test AS a, test AS b SET a.end = (b.create-1)
WHERE ((a.zip=b.zip)
AND (a.create<>b.create) And (a.create<b.create) AND (a.end Is Null));
Dodgy Chris
-----------------------------------

confucious say : better to save a mans life than to build 7 storey pagoda

 

[OU18]

This was perfect did exactly what i needed it to do, Thank you very much, heres your Advil or do you need Excedrin Migraine.