Post reply

Not getting NULL when compare MAX date

  • August 5, 2009 at 7:34 am

    #385119

    DROP TABLE AT

    DROP TABLE TC

    CREATE TABLE TC (fldID INT PRIMARY KEY, fldCID INT)

    INSERT INTO TC

    SELECT 101,1 UNION ALL

    SELECT 102,1 UNION ALL

    SELECT 103,2 UNION ALL

    SELECT 104,3 UNION ALL

    SELECT 105,3 UNION ALL

    SELECT 106,3 UNION ALL

    SELECT 107,4

    CREATE TABLE AT (fldID INT FOREIGN KEY REFERENCES TC(fldID),fldMID INT, fldDate DATETIME)

    INSERT INTO AT

    SELECT 102,1001,'2007-07-13' UNION ALL

    SELECT 102,1001,'2008-07-13' UNION ALL

    SELECT 104,1009,'2009-07-13' UNION ALL

    SELECT 105,1010,'2008-07-13' UNION ALL

    SELECT 106,1011,'2007-07-13' UNION ALL

    SELECT 107,1008,'2007-07-13'

    SELECT * FROM TC

    SELECT * FROM AT

    Here what i am doing is, First i have to compare two tables (TC,AT) using fldID. Below are the condition for comparing.

    1. If all the fldCID (101,102 for fldCID = 1) are available in AT then i have to get the maximum fldDates fldMID.

    In this example only fldID 102 is available against fldCID = 1. Then in this case the fldMID should be NULL.

    2. If no fldCID in the second table (ie, AT) then the fldMID should be NULL.

    Below are the sample result set. Pl send me script for this result set. Pl let me know if you have any clarifications.

    fldID fldCID fldMID fldDate

    -------------------------------

    101 1 NULL NULL

    102 1 NULL NULL

    103 2 NULL NULL

    104 3 1009 2009-07-13

    105 3 NULL NULL

    106 3 NULL NULL

    107 4 1008 2007-07-13

    Appreciate your help!!!

    ---

  • August 5, 2009 at 7:53 am

    #1034780

    Can you post your code that you are have problems with?

  • August 5, 2009 at 7:58 am

    #1034786

    sqluser (8/5/2009)

    1. If all the fldCID (101,102 for fldCID = 1) are available in AT then i have to get the maximum fldDates fldMID.

    In this example only fldID 102 is available against fldCID = 1. Then in this case the fldMID should be NULL.

    2. If no fldCID in the second table (ie, AT) then the fldMID should be NULL.

    For #1, for fldCID = 1, both fldID 101 and 102 must be in the table AT before you pull the MAX(fldDates) and MAX(fldMID), correct?

  • August 5, 2009 at 8:44 am

    #1034844

    Lynn Pettis (8/5/2009)

    sqluser (8/5/2009)

    1. If all the fldCID (101,102 for fldCID = 1) are available in AT then i have to get the maximum fldDates fldMID.

    In this example only fldID 102 is available against fldCID = 1. Then in this case the fldMID should be NULL.

    2. If no fldCID in the second table (ie, AT) then the fldMID should be NULL.

    For #1, for fldCID = 1, both fldID 101 and 102 must be in the table AT before you pull the MAX(fldDates) and MAX(fldMID), correct?

    This one is not necessary. If both the values are present in the AT[101,102 should be in AT] table then i have to take the MAX(fldDate) otherwise it should return NULL.

    Any one either 101 or 102 is not present in AT it should return NULL.

  • August 5, 2009 at 9:00 am

    #1034864

    Now, how about your code that isn't working? It would be helpful to see what you have done sofar.

  • August 5, 2009 at 9:13 am

    #1034872

    I don't know how the logic is. I am trying the same.

    If you have any idea about the logic let me know.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply