Results 1 to 2 of 2

Thread: Sql Query

  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: Sql Query

    hi,

    Data in the table Purchase:

    Code Purdate ItmValue
    ---- ------- --------
    1 2005-04-15 08:00:01 100
    2 2005-04-16 05:20:00 150
    1 2005-04-15 08:30:00 120
    3 2005-04-17 08:00:20 160
    1 2005-04-13 09:01:00 109
    2 2005-04-14 05:00:01 390
    3 2005-04-12 06:00:01 290
    2 2005-04-15 07:45:01 360
    2 2005-04-15 08:40:00 200




    In my Analysis screen when the users selects code 1 and code 2, I have
    to compare both the values of code 2 with the value of code 1 and display data.
    The user is given the option to select two codes, and start and the end date.

    So now if the user selects Code 1 and Code 2 and startDt:2005/04/15
    End Date: 2005/04/13. It has to display as follows on the screen.
    Code Purdate ItmValue
    ---- -------
    1 2005-04-15 08:00:01 360
    1 2005-04-15 08:30:00 200
    1 2005-04-13 09:01:00 390

    Data displayed
    --------------
    Purdate is the date of Code 1. ItmValue is of
    code 2.

    It has to display all the records of code 1 and then for each record of
    code 1, compare the Purdate of Code1 to the PurDate of Code2. It has to
    pick the closest matching purdate of Code 2 to the purdate of Code 1.

    I would appreciate if anybody could help me how to build the
    sql for this.

    Thanx in advance.

    cheers
    Sam
    Last edited by khroner; 10-17-05 at 09:09.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    This should work, I tested it:

    I don't know how you handle user's prompts (parameters) but let's say they are some kind of variables:

    v_date_start DATE;
    v_date_end DATE; -- v_date_end > v_date_start allways
    v_code1 INT;
    v_code2 INT;

    Code:
    SELECT 
    t1.code, 
    t1.PURDATE,
    (SELECT itm_value FROM purchase t2 
     WHERE t2.code = v_code2
     AND	 ABS(t2.PURDATE - t1.purdate) = (SELECT MIN(ABS(t2.purdate - t3.purdate))
    							FROM purchase t3
    							WHERE t3.code = v_code1
    							AND TO_DATE(t3.purdate) BETWEEN TO_DATE(v_date_start)
    											AND TO_DATE(v_date_end)
    						  )
     AND TO_DATE(t2.purdate) BETWEEN TO_DATE(v_date_start)
    				 AND TO_DATE(v_date_end)
    ) ItmValue
    FROM purchase t1
    WHERE TO_DATE(t1.purdate) BETWEEN TO_DATE(v_date_start)
    				  AND TO_DATE(v_date_end) 
    AND   t1.code = v_code1

Posting Permissions

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