Don't know where to start

Hi There,

Need your help as I am baffled!!

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.

Thanks.

Best Regards,

Steve.

which table has the Exceeded column?

Would you please post the CREATE TABLE statements for both tables?

Hi There,
Thanks for the really quick reply.

None of the tables has the Exceeded column it is one I need to be added by the query.

Best Regards,

Steve.

You can't add a column that way. Add the column first then we can work on the query (after you post the CREATE TABLE statements)

Hi There,

These are the tables:
CREATE TABLE [dbo].[tbTable1](
[AutoID] [bigint] IDENTITY(1,1) NOT NULL,
[Description] varchar NULL,
[CreateBy] [bigint] NULL,
[CreateDate] [datetime] NULL,
[EditBy] [bigint] NULL,
[EditDate] [datetime] NULL

CREATE TABLE [dbo].[tbTable2](
[AutoID] [bigint] IDENTITY(1,1) NOT NULL,
[Table1ID] [bigint] NOT NULL,
[Description] varchar NULL,
[TypeID] [bigint] NULL,
[PComDate] [smalldatetime] NULL,
[CreateBy] [bigint] NULL,
[CreateDate] [datetime] NULL,
[EditBy] [bigint] NULL,
[EditDate] [datetime] NULL

Best Regards,

Steve.

So, try this:

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
1 Like

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.

Best Regards,

Steve.

sure, just add another case expression like the first one

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')

Thanks for any help.

Best Regards,
Steve.