I need to list all records from a table but I need to do some work based on a calculation of records in another table.
I.E.
Read a record from table1, this comes back with an autoid = 4. Add AutoId & Decscription columns from Table1.
select records from Table2 where the related field = 4, lets assume 4 records were returned.
each of these records has a field which tells the type of record it is. Type can be 1 ,2 ,3, 4
If there is a record type of 4 find out if the date of this record is less than the date of any of the other records where type = 1
If yes add a 'Y' in a field called 'Exceeded'
If No leave the field 'Exceeded' blank
I cannot even begin to think how to go about this so your help would be really appreciated.
SELECT t2.*
, CASE
WHEN t2.TypeID = 4
AND t2.CreateDate < ALL (
SELECT t2.CreateDate
FROM tbTable2 t2
WHERE t2.Table1ID = t1.AutoID
AND t2.TypeID = 1
AND t2.CreateDate IS NOT NULL
)
THEN 'Y'
END AS Exceeded
FROM tbTable1 t1
JOIN tbTable2 t2 ON t1.AutoID = t2.Table1ID
That's amazing. Well done.
Looks like what I need but I'm thinking there may be another / different table involved.
Also, that gives me results for Type = 4 and all other recs with type = 1 but I may need another part where
Type = 3 and all other recs have type = 2. Can that be done?
Thanks for your help. Don't know where you learn this stuff but wish I knew.
Hi There,
I have spent today looking at what is needed and the only way I can explain is to give some sort of psudo text.
I hope you understand it because it baffles me and I wrote it. Here it is:
Get all records from Table1
for each record in Table1 get the associated record from Table2 and with the value in Table2.field7
get all records from Table3 where Table3.field2 = Table2.field7
for each of these records, find the record where Table3.Type = 2. (#1 We will use this again)
With this record find the associated records where Table4.field1 = Table3.Field4 (This can return 1+ records)
With the date field Table4.pComp check the other records Table4.pComp(type2) > Table4.pComp
If it's less leave blank, if not = 'Y' add to New Column "Exceeded1"
for each of these records, find the record where Table3.Type = 4. (#2 We will use this again)
With this record find the associated records where Table5.field1 = Table3.Field4 (This can return 1+ records)
With the date field Table5.pComp check the other records Table5.pComp(type4) > Table5.pComp
If it's less leave blank, if not = 'Y' add to New Column "Exceeded2"
(#1) for each of these records, find the record where Table3.Type = 2
With this record find the associated record where Table4.field1 = Table3.Field4
With this record check
if Table4.field5 > Table4.pComp (If yes New Column "Result" = 'Late')
if Table4.field5 < Table4.pComp (If yes New Column "Result" = 'Early' )
if Table4.field5 = Table4.pComp (If yes New Column "Result" = 'On Time')
(#2) for each of these records, find the record where Table3.Type =4
With this record find the associated record where Table5.field1 = Table3.Field4
With this record check
if Table5.field5 > Table5.pComp (If yes New Column "Result" = 'Late')
if Table5.field5 < Table5.pComp (If yes New Column "Result" = 'Early' )
if Table5.field5 = Table5.pComp (If yes New Column "Result" = 'On Time')