Don't know where to start

Transact-SQL
1

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.

2

which table has the Exceeded column?

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

3

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.

4

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)

5

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.

6

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
7

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.

8

sure, just add another case expression like the first one

9

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.