Solved - script - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Solved - script

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2009-08-18 : 04:14:10

Hello,

i have 2 tables like the following:

table 1

code // prod // q // or_1 // date1 // date2

1001 // x1 // 10 // 355 // 2009-07-05 // 2009-07-12
1001 // x2 // 15 // 355 // 2009-07-05 // 2009-07-12
1001 // x3 // 20 // 355 // 2009-07-05 // 2009-07-12

table 2

or_2 // or_date

355 // 2009-07-01

i'm having trouble at getting the or_date in my script because it triples the results. the script is like this:

select code ,
prod ,
q,
or_1,
or_date,
date1,
date2,
datediff(day, date2, date1)

from table1, table2

where or_1 = or_2

how can i get the or_date in my script without triple the results in this case?

Thanks in advance.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 04:22:58

but ur q value is changing in the for every record in the table1
how would u like to get output?

tempus
Starting Member

47 Posts

Posted - 2009-08-18 : 05:01:34

this is the real script

SELECT

ST03001 ,
--SL01002 DENUMIRE_CLIENT,
ST03009 ,
OR20015 ,
ST03014 ,
ST03015 ,
ST03017 ,
ST03020 CANTITATE_VANDUTA,
DATEDIFF(day,ST03015, ST03031) ,
DATEDIFF(day,ST03031, OR20015)

FROM ST030100, OR200100 -- SL010100

WHERE ST03009 = OR20001 AND
--ST03001 = SL01001 AND
ST03015 BETWEEN '2009-07-01' AND '2009-07-31'

look at results (maybe my example wasn't to great or i made a mistake there):

411.1850 0000006480 2009-06-25 00:00:00.000 A1 -04753383 2009-07-02 00:00:00.000 PFPT650A 25.70000000 -2 -5
411.1850 0000006480 2009-06-25 00:00:00.000 A1 -04753383 2009-07-02 00:00:00.000 PFPT650A 25.70000000 -2 -5
411.1850 0000006480 2009-06-25 00:00:00.000 A1 -04753383 2009-07-02 00:00:00.000 PFPT650A 25.70000000 -2 -5
411.1850 0000006480 2009-06-25 00:00:00.000 A1 -04753383 2009-07-02 00:00:00.000 PFPT650A 1224.30000000 -2 -5
411.1850 0000006480 2009-06-25 00:00:00.000 A1 -04753383 2009-07-02 00:00:00.000 PFPT650A 1224.30000000 -2 -5
411.1850 0000006480 2009-06-25 00:00:00.000 A1 -04753383 2009-07-02 00:00:00.000 PFPT650A 1224.30000000 -2 -5

it repeats the same quantities in some cases.

hope this helps.

Thanks.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 05:23:51

if u want the required output as
411.1850 0000006480 2009-06-25 00:00:00.000 A1 -04753383 2009-07-02 00:00:00.000 PFPT650A 25.70000000 -2 -5
411.1850 0000006480 2009-06-25 00:00:00.000 A1 -04753383 2009-07-02 00:00:00.000 PFPT650A 1224.30000000 -2 -5

use distinct keyword

tempus
Starting Member

47 Posts

Posted - 2009-08-18 : 05:59:51

SELECT DISTINCT

ST03001 ,
SL01002 ,
ST03009 ,
OR20015 ,
ST03014 ,
ST03015 ,
ST03017 ,
ST03020 ,
DATEDIFF(day,ST03015, ST03031) ,
DATEDIFF(day,ST03031, OR20015)

FROM ST030100, OR200100, SL010100

WHERE ST03009 = OR20001 AND
ST03001 = SL01001 AND
ST03015 BETWEEN '2009-07-01' AND '2009-07-31'

ORDER BY ST03015, ST03009

This seems indeed the corect script now. thank you bklr. still i don't get why the select distinct ST03001 is corect formula. the distinct aplies to the whole script?

Thanks anyway verry much.
I really apreciate it.

Best regards.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 06:02:32

welcome

The DISTINCT keyword eliminates duplicate rows from a result set

tempus
Starting Member

47 Posts

Posted - 2009-08-18 : 06:05:33

and it allways must be inserted after the Select ?

just for my information. i had this problem many times.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 06:10:51

yes

select distinct required columns..............
or u can use row_number also...........