#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Posts
    75
    Rep Power
    12

    select between a period 12-13


    Hi, I have 2 tables staff table and post table. Have included some data samples
    Staff table would be the following:
    Person_reference, Surname, Forname, date_started, date_left
    G0584, Bloggs , Joe, 10-Nov-97, null
    G570, Davies, Harry, 25-Sep-07, null
    G0625, Carthy, Ryan, 17-Jan-11, null


    Post table would contain the following data:
    Personnel_No,Actual_start_date, Actual_End_date
    G0584, 01-OCT-13, 31-AUG-15
    G0584, 01-JAN-04, 31-SEP-13
    G0584, 10-NOV-97, 31-DEC-03
    G570, 27-SEP-10, 24-JAN-14
    G570, 27-SEP-07, 26-SEP-10
    G570, 25-JAN-14, 30-APR-14
    G0625, 17-JAN-11, 22-AUG-11
    G0625, 23-AUG-11, 31-AUG-11
    G0625, 01-SEP-11, 31-AUG-13

    I only want to select data that had posts between 01-SEP-12 to the 31-AUG-13
    So my script is as follows

    SELECT ppd.PERSON_REFERENCE,ppd.FORENAME,ppd.SURNAME,
    ppmd.actual_start_date, ppmd.actual_end_date, ppd.date_started, ppd.date_left
    FROM staff ppd, post ppmd
    where ppd.person_reference = ppmd.Personnel_No
    and ppmd.category like 'ACM'
    and ((ppd.date_left) is null or (ppd.date_left) >= '31-AUG-13')
    and (ppd.date_started < '31-Aug-13')
    and ((ppmd.actual_end_date) is null OR (ppmd.actual_end_date) >= '31-AUG-13')
    order by ppd.surname asc;


    This returns the data correctly for staffid G0584 but returns all the other data for the other staff but i put in the following line to my sql
    and ((ppmd.actual_start_date) < '31-Aug-13')

    I get the other returns correct but only get 1 entry for G0584 whereas I should 2 post entries for him
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,206
    Rep Power
    2012
    Why did you place parenthesis around the field name only and not the actual complete comparison?
    E.g
    ((ppd.date_left) is nulll
    vs.:
    ((ppd.date_left is nulll)

IMN logo majestic logo threadwatch logo seochat tools logo