Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Unanswered: Double values id

    Hello,

    I have a table time_registration and a table invoice (v_rep_facturen).
    With next SQL I get double time_registration values:

    SELECT time_registration.time_registration_id,
    time_registration.date_registration,
    time_registration.announced_hours,
    v_rep_facturen.factuurbedrag,
    left(f_gettranslation ('SYNETON_DYNAMIC_DDDW_VALUES',month(time_registra tion.date_registration), (select dddw_value from syneton_dynamic_dddw_values where dddw_value_id = month(time_registration.date_registration))) ,3) as the_month_name
    FROM time_registration,
    employee,
    project,
    v_rep_facturen

    LEFT JOIN project ON v_rep_facturen.project_id = project.projectid

    WHERE ( employee.employee_id = time_registration.person_id ) and
    ( project.projectid = time_registration.project_id ) and
    ( ( project.invoice_group_id = 277 ) AND
    ( year(time_registration.date_registration) >= 2007 ) AND
    ( year(time_registration.date_registration) <= 2008 ) ) AND
    ( year(v_rep_facturen.facturatie_datum) >= 2007 AND
    year(v_rep_facturen.facturatie_datum) <= 2008)

    Someone can see why I have double values? It's because in the period between 2007 and 2008, I have 2 invoices. So with each invoice, he shows again the time_registration.

    How can I avoid that ?



    Here's the data from my query:

    62 2007-01-04 15 1897.13 Jan
    62 2007-01-04 15 1069.48 Jan
    63 2007-01-12 15 1897.13 Jan
    63 2007-01-12 15 1069.48 Jan
    64 2007-01-12 15 1897.13 Jan
    64 2007-01-12 15 1069.48 Jan
    65 2007-01-12 15 1897.13 Jan
    65 2007-01-12 15 1069.48 Jan
    66 2007-01-15 15 1069.48 Jan
    66 2007-01-15 15 1897.13 Jan

    Here's what is in time_registration:

    62 2007-01-04 15
    63 2007-01-12 15
    64 2007-01-12 15
    65 2007-01-12 15
    66 2007-01-15 15

    Here's what is in invoice (v_rep_facturen):

    26 2007-12-22 1897.13
    28 2008-12-22 1069.48

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    So which v_rep_facturen.factuurbedrag do you want to display, the one from 2007, the one from 2008 or the sum of the two?

  3. #3
    Join Date
    Jan 2012
    Posts
    6
    Both, in the timeregistration with date 2007 the invoice amount of 2007 and in the timeregistration with date 2008 the invoice amount of 2008.

    If I have this, I can show them in a Crosstab in Infomaker with my timeregistration hours.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    What do you meen when you say " I have double values"
    Maybe if you provide sample data and your expected result given the sample data I might better understand your problem.

  5. #5
    Join Date
    Jan 2012
    Posts
    6
    The column of 62 is my time_registration_id and I have always 2 because I have 2 invoices. That's why I have double values...

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    So you don't want to see both invoices? But when I asked which one do you want to see you said both. Now it seems you don’t want both. Sorry I don’t get it.

  7. #7
    Join Date
    Jan 2012
    Posts
    6
    Sorry, maybe I wasn't too clear about that.

    If you look at the two first records you see as ID two times 62.
    If you look at the date, it's both year 2007.

    But here is the problem. In my invoice table:
    2007: 1897,13
    2008: 1069,48

    Since in my results, they are mixed up (both are at 2007 and also both at records with 2008), I can't split them into the right year.

    So it should be:
    62 - 2007 - 1897,13
    63 - 2007 - 1897,13
    64 - 2007 - 1897,13
    ....
    128 - 2008 - 1069,48
    128 - 2008 - 1069,48
    ...

    Hope this is a bit clearer

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    In your select change
    select ...
    time_registration.date_registration
    ...
    from ...

    to
    select ...
    isnull( v_rep_facturen.facturatie_datum, time_registration.date_registration)
    ...
    from ...

    OR maybe select both

Posting Permissions

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