Double values id - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Double values id

Author

Topic

Myclos81
Starting Member

5 Posts

Posted - 2012-02-06 : 09:54:00

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:59:37

sorry we cant see
how do you expect us to see your problem without you posting some data?
We dont have any access to your system so unless you poast some sample data and explain your issue nobody will be able to make out.
Unless you explain, its very difficult and takes some serious effort from someone to understand logic from yout code which I dont think anybody will bother to do!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Myclos81
Starting Member

5 Posts

Posted - 2012-02-06 : 10:38:13

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 11:37:44

can you tell on what basis the two tables are linked? As per sample data there's no common valued columns so didnt understand how tables are related

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Myclos81
Starting Member

5 Posts

Posted - 2012-02-07 : 02:38:43

Well, normally they are linked by an invoice_id but since it is an import, the old time registrations aren't linked by an invoice.

So I want to link them by project_id and get a summary per year but I don't know that it is possible with SQL only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources