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

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?

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.

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.

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...

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.

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

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