Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Question Unanswered: Help on self join

    Hello,

    I am working on an Access 2007 database. I have a table (Transactions) that contain the following fields:
    • Account Number (Text)
    • Product ID (Text)
    • Activity Date (Date/Time)
    • Activity Time (Date/Time)
    • Quantity (Number)
    • Price (Currency)
    • Transaction Type (Text)


    I'm trying to generate a list of transactions that occurred on the same date (Activity Date), with the same product (Product ID), having the same quantity (Quantity), within X hours of each other (Activity Time). From that result set, I want to generate a markup percentage for each transaction.

    For example, I have the following transactions in the table:

    Account Number, Product ID, Activity Date, Activity Time, Quantity, Price, Transaction Type
    123,XYZ,1/1/2011,10:30AM,100,5.5,"Buy"
    456,XYZ,1/1/2011,10:45AM,200,5.9,"Sell"
    789,XYZ,1/1/2011,11:05AM,100,6.0,"Sell"
    111,XYZ,1/1/2011,02:00PM,100,6.5,"Sell"
    321,ABC,1/1/2011,09:15AM,500,3.0,"Sell"
    654,ABC,1/1/2011,09:45AM,300,3.2,"Buy"
    987,ABC,1/1/2011,10:00AM,500,3.5,"Buy"
    222,ABC,1/1/2011,12:30PM,500,3.7,"Buy"

    My result set should be the following, assuming I wanted to return transactions within 1 hour of each other (Activity Time):

    Account Number, Product ID, Activity Date, Activity Time, Quantity, Price, Transaction Type, Markup
    123,XYZ,1/1/2011,10:30AM,100,5.5,"Buy",9%
    789,XYZ,1/1/2011,11:05AM,100,6.0,"Sell",9%
    321,ABC,1/1/2011,09:15AM,500,3.0,"Sell",17%
    987,ABC,1/1/2011,10:00AM,500,3.5,"Buy",17%

    The markup calculation would be: Abs((5.5-6.0)/5.5) and Abs((3.0-3.5)/3.0), respectively. Notice you can have a Sell before a Buy and that would be work for my purposes.

    I tried various ways by doing a self join on the table; however, I was not able to figure out the correct syntax for the sql statement. Can anyone more smarter than I (which is everyone on this forum ) help me figure out how to put together the sql syntax?

    Thank you in advance!!

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Markup would be something like price less cost. Why are you comparing the 10:30 with the 11:05, but you are not comparing the 10:00 with the 10:30? If this is more like a stock price, then perhaps a graph would be a more useful way of analyzing the data.

    Usually when I have to do odd things like this, I end up creating a temporary table and using VB to march through the record set, so I can compare the current record to the previous record.
    John M Reynolds

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    Quote Originally Posted by jmrSudbury View Post
    Markup would be something like price less cost. Why are you comparing the 10:30 with the 11:05, but you are not comparing the 10:00 with the 10:30? If this is more like a stock price, then perhaps a graph would be a more useful way of analyzing the data.

    Usually when I have to do odd things like this, I end up creating a temporary table and using VB to march through the record set, so I can compare the current record to the previous record.
    Hello jmrSudbury,

    You can think of it as buying and selling fixed income products where the purchase price becomes the cost and the selling price becomes the sales price.

    For the 10:30 and 11:05 records, it is because they both have the same quantity, 100, the same product id, XYZ, the same date, 1/1/2011, and are within 1 hour of each other.

    I would like to do it via self join; although I am open to creating a temp table and VB. Is there a way to do it via sql?

    Thanks,

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Generally speaking, a self join is performed using aliases for the same table, like this:
    Code:
    SELECT <column list> FROM <Table> AS A
    INNER JOIN <Table> AS B ON A.<Join Column> = B.<Join Column>
    WHERE <Conditions>
    In your case, it should be something like (not tested):
    Code:
    SELECT * FROM Tbl_Transactions AS A
    INNER JOIN Tbl_Transactions AS B ON A.[Product ID] = B.[Product ID]
    WHERE A.Quantity = B.Quantity AND A.[Activity Time] < B.[Activity Time]
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •