Post reply

Accounts that don't have prior transactions

  • May 20, 2010 at 11:00 am

    #224805

    I have an account master table that has a start date, but this start date can be changed at any point in time.

    I want to grab all accounts from the account master table that don't have any transactions in the transaction table prior to the start date.

    Acct Table

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

    Acct Start Date

    1000 2010-01-01

    1001 2010-01-01

    1002 2010-01-01

    Trans Table

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

    Acct TransDesc Date

    1000 Hello there 2009-01-15

    1000 Hi 2009-01-10

    1002 Yo 2009-10-10

    1001 This ONE! 2010-01-02

    So in the above example only acct 1001 would get returned.

  • May 20, 2010 at 11:21 am

    #1170352

    --results

    Acct TransDesc Date Acct StartDate

    1001 This ONE! 2010-01-02 00:00:00.000 1001 2010-01-01 00:00:00.000

    the example:

    CREATE TABLE Acct(Acct int, StartDate datetime )

    INSERT INTO Acct

    SELECT '1000','2010-01-01' UNION ALL

    SELECT '1001','2010-01-01' UNION ALL

    SELECT '1002','2010-01-01'

    CREATE TABLE Trans (Acct int, TransDesc varchar(30), Date datetime)

    INSERT INTO Trans

    SELECT '1000','Hello there','2009-01-15' UNION ALL

    SELECT '1000','Hi','2009-01-10' UNION ALL

    SELECT '1002','Yo','2009-10-10' UNION ALL

    SELECT '1001','This ONE!','2010-01-02'

    SELECT * FROM Trans

    LEFT OUTER JOIN Acct

    ON Trans.Acct = Acct.Acct

    WHERE Trans.Date >= Acct.StartDate

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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