• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL Help please

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    #31
    OK Smarty pantses

    how about this:

    table "customer_snapshot"

    fields customer_surname, customer_dob, snapshot_date, customer_email(other fields...)

    for the purposes of this exercise customer_surname, customer_dob and snapshot_date are a composite PK.

    What I want to know is:
    1) which customers exist today that didn't exist yesterday and
    2) which customers existed yesterday that don't exist today and
    3) which customers have changed their email addresses since yesterday.

    DB: Sybase, Number of records in table - about 2.5 million.
    "Experience hath shewn, that even under the best forms of government those entrusted with power have, in time, and by slow operations, perverted it into tyranny. "


    Thomas Jefferson

    Comment


      #32
      use derived tables

      Can't be arsed writing out the full SQL, you can do that yourself.
      Heres a starter for 10 though
      select <stuff>
      from (select * from customer_snapshot where date = <today>) as Today
      full outer join
      (select * from customer_snapshot where date = <yesterday>) as Yesterday
      on today.<primary key fields> = yesterday.<primary key fields>
      where
      <add your where clauses here>
      Coffee's for closers

      Comment


        #33
        Thanks I'll give it a go
        "Experience hath shewn, that even under the best forms of government those entrusted with power have, in time, and by slow operations, perverted it into tyranny. "


        Thomas Jefferson

        Comment


          #34
          One last qn - without a full outer join available to me in sybase is there any way to do this in one query or do I just have to do 2 queries?

          ta.
          "Experience hath shewn, that even under the best forms of government those entrusted with power have, in time, and by slow operations, perverted it into tyranny. "


          Thomas Jefferson

          Comment


            #35
            (select distinct <primary key fields> from customer_snapshot where date in (<today>, <tomorrow>) as clist
            left outer join
            (select * from customer_snapshot where date = <today>) as today
            on clist.<pkey fields> = today.<pkey fields>
            left outer join
            (select * from customer_snapshot where date = <yesterday>) as yesterday
            on clist.<pkey fields> = yesterday.<pkey fields>

            it will hamper performance though.. there's probably a better way but with only 1 table to look at its difficult to tell
            Last edited by Spacecadet; 2 October 2008, 15:03.
            Coffee's for closers

            Comment

            Working...
            X