Help me with my Syntax - 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 2000 Forums

Transact-SQL (2000)

Help me with my Syntax

Author

Topic

rtr1900
Starting Member

48 Posts

Posted - 2008-04-23 : 10:46:51

Hi,

I have a syntax which works fine, but when I want to add another table I don´t get the results. I know where the problem is (I guess), but can´t get the syntax right to work.

My syntax is:

SELECT mae_lab.NOMBRE, MAE_ART.COD_ART, MAE_ART.DESCRIP, MAX(historic.FECHA) AS FechaFac, MAE_ART.STOCK_R, MAE_ART.NETO1 AS PVF
FROM  MAE_ART INNER JOIN
      historic ON MAE_ART.COD_ART = historic.COD_ART INNER JOIN
      mae_lab ON MAE_ART.COD_LAB = mae_lab.COD_LAB
WHERE (MAE_ART.STOCK_R > 0)
GROUP BY MAE_ART.COD_ART, MAE_ART.DESCRIP, MAE_ART.STOCK_R, MAE_ART.STOCK_R, mae_lab.NOMBRE, MAE_ART.NETO1
ORDER BY MAE_ART.DESCRIP

Mae_art: Table with articles
Historic: History of bills (here you can find the same article more than one)
Mae_lab: Code_lab

Now My result is that I get for every article the date (FECHA) of the latest bill of it, with some data (price,stock,...)

This works fine.

Now I have another table which contains the status of buying the articles.
Name table: mae_LPED
Structure: COD_ART ,DTO ,FECHA
(-> article numer, discount and date of order)

If I link the Mae_Lped.COD_ART with Mae_art.Cod_art I get multiple lines where the historic.Fecha is always the same (Which is correct as it is put MAX(historic.FEcha)).

Now my question is how can I just add to the result output 1 column that has the latest DTO of that article. Without multiplying the output lines.
I tried a lot of thing, but it doensñt work otherwhise I get more than 70.000 lines and multiplied.

I hope I have explained it OK.

Any help is welcome.

Johny

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 10:58:04

Why are you grouping by "MAE_ART.STOCK_R" twice?

E 12°55'05.25"
N 56°04'39.16"

rtr1900
Starting Member

48 Posts

Posted - 2008-04-23 : 11:09:18

Sorry, must have slipped into it. I just removed it, and it makes the same problem.
I am going to try to put the reuslt down here:
With the next syntax:

SELECT mae_lab.NOMBRE, MAE_ART.COD_ART, MAE_ART.DESCRIP, MAX(historic.FECHA) AS FechaFac, MAE_ART.STOCK_R, MAE_ART.NETO1 AS PVF
FROM MAE_ART INNER JOIN
historic ON MAE_ART.COD_ART = historic.COD_ART INNER JOIN
mae_lab ON MAE_ART.COD_LAB = mae_lab.COD_LAB
WHERE (MAE_ART.STOCK_R > 0)
GROUP BY MAE_ART.COD_ART, MAE_ART.DESCRIP,MAE_ART.STOCK_R, mae_lab.NOMBRE, MAE_ART.NETO1
ORDER BY MAE_ART.DESCRIP

I get as result:
Name Lab Code Prod Name Prod Date Last selling units price
LAbo1 860221 Name of product 21/04/2008 100 5.60€
Labo1 860225 Name of Product 18/03/2008 5 2.35€
Labo2 860226 Name of Product 8/03/2008 5 2.5€

Now I added the next table with:
SELECT mae_lab.NOMBRE, MAE_ART.COD_ART, MAE_ART.DESCRIP, MAX(historic.FECHA) AS FechaFac, MAE_ART.STOCK_R, MAE_ART.NETO1 AS PVF,
mae_lped.DTO, mae_lped.FECHA
FROM MAE_ART INNER JOIN
historic ON MAE_ART.COD_ART = historic.COD_ART INNER JOIN
mae_lab ON MAE_ART.COD_LAB = mae_lab.COD_LAB INNER JOIN
mae_lped ON MAE_ART.COD_ART = mae_lped.COD_ART
WHERE (MAE_ART.STOCK_R > 0)
GROUP BY MAE_ART.COD_ART, MAE_ART.DESCRIP, MAE_ART.STOCK_R, mae_lab.NOMBRE, MAE_ART.NETO1, mae_lped.DTO, mae_lped.FECHA

And the result is:

Name Lab Code Prod Name Prod Date Last selling units price Dto Date Buying
LAbo1 860221 Name of product 21/04/2008 100 5.60€ 0 01/02/2008
LAbo1 860221 Name of product 21/04/2008 100 5.60€ 6 01/03/2008
LAbo1 860221 Name of product 21/04/2008 100 5.60€ 16 01/04/2008
Labo1 860225 Name of Product 18/03/2008 5 2.35€ 5 15/02/2008
Labo1 860225 Name of Product 18/03/2008 5 2.35€ 5 16/02/2008
...

While it should be

Name Lab Code Prod Name Prod Date Last selling units price Dto Date Buying
LAbo1 860221 Name of product 21/04/2008 100 5.60€ 16 01/04/2008
Labo1 860225 Name of Product 18/03/2008 5 2.35€ 5 16/02/2008
...

So it would have to return the latest selling date, but only the latest date with its DTO(discount) of when we ordered the product.

Many thx!

rtr1900
Starting Member

48 Posts

Posted - 2008-04-23 : 12:12:43

I have been trying to simplify my problem.
I tried to get all the lines of a table, BUT only where the date is the highest(=latest) and just shows me from that line all the data. It doesnñt go.

For example:

SELECT DISTINCT COD_ART, MAX(FECHA) AS Expr1, DTO AS dtoi
FROM mae_lped
GROUP BY COD_ART,dto
ORDER BY COD_ART DESC

It just shows me all the files.

Now, how can I just simnplyfi it, it will show me ONLY the row where the MAX(FECHA) condition is valid?

thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-23 : 12:50:08

quote:
Originally posted by rtr1900

I have been trying to simplify my problem.
I tried to get all the lines of a table, BUT only where the date is the highest(=latest) and just shows me from that line all the data. It doesnñt go.

For example:

SELECT DISTINCT COD_ART, MAX(FECHA) AS Expr1, DTO AS dtoi
FROM mae_lped
GROUP BY COD_ART,dto
ORDER BY COD_ART DESC

It just shows me all the files.

Now, how can I just simnplyfi it, it will show me ONLY the row where the MAX(FECHA) condition is valid?

thx

didnt get you. Can you explain with some data?

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-04-23 : 14:59:26

It's hard to determine what you're after here. But I think you need a derived table.

I think mae_lped has multiple records that match the join condition for each row earlier in the query. If you change

join mae_lped

to


join (select max(fecha) as fecha, COD_ART from mae_lped group by COD_ART) as DT1 ON MAE_ART.COD_ART = DT1.COD_ART
join mae_lped on DT1.COD_ART = mae_lped.COD_ART AND DT1.fecha = mae_lped.fecha

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-24 : 01:28:02

I THINK MORE THAN ONE RECORD IS SATISFYING YOUR JOIN CONDITION,SO ACCORDING TO THAT RESULT IS CORRECT.

chandan Joshi

rtr1900
Starting Member

48 Posts

Posted - 2008-04-24 : 04:16:57

quote:
Originally posted by visakh16

quote:
Originally posted by rtr1900

I have been trying to simplify my problem.
I tried to get all the lines of a table, BUT only where the date is the highest(=latest) and just shows me from that line all the data. It doesnñt go.

For example:

SELECT DISTINCT COD_ART, MAX(FECHA) AS Expr1, DTO AS dtoi
FROM mae_lped
GROUP BY COD_ART,dto
ORDER BY COD_ART DESC

It just shows me all the files.

Now, how can I just simnplyfi it, it will show me ONLY the row where the MAX(FECHA) condition is valid?

thx

didnt get you. Can you explain with some data?

Ok, if I execute this code I get this as result:

COD_ART Expr1 dtoi
250250 21/03/2008 5
250250 22/03/2008 5
250250 01/04/2008 7.5
250250 02/04/2008 5
250999 06/02/2007 6
250999 08/02/2007 6.2
250999 18/02/2007 6.2
..... ...... ......

When I actually want that for every Article(Cod_ART) I only get back the row with the latest Date(Expr1)
Something like

COD_ART Expr1 dtoi
250250 02/04/2008 5
250999 06/02/2007 6
21252 01/01/2008 6
..... ..... ...

That is why I tried the MAX() command, so it would give me the highest data of that column, Also thought that using DIstinct it wouldn´t return the same COD_ART more than once. But it doesn´t work and it is truning me crazy....

This problem is giving me the same as the mails before.....

Thx already!

rtr1900
Starting Member

48 Posts

Posted - 2008-04-24 : 04:21:38

quote:
Originally posted by cat_jesus

It's hard to determine what you're after here. But I think you need a derived table.

I think mae_lped has multiple records that match the join condition for each row earlier in the query. If you change

join mae_lped

to

join (select max(fecha) as fecha, COD_ART from mae_lped group by COD_ART) as DT1 ON MAE_ART.COD_ART = DT1.COD_ART
join mae_lped on DT1.COD_ART = mae_lped.COD_ART AND DT1.fecha = mae_lped.fecha

You are right, the mae_lped has multiple rows with the same COD_ART and it is related to the other table. S that is why it return so many uplicated rows, with only the data of mae_lped changed.

I tried your suggestion, but where do I have to change the code?

rtr1900
Starting Member

48 Posts

Posted - 2008-04-24 : 04:45:48

quote:
Originally posted by cat_jesus

It's hard to determine what you're after here. But I think you need a derived table.

I think mae_lped has multiple records that match the join condition for each row earlier in the query. If you change

join mae_lped

to

join (select max(fecha) as fecha, COD_ART from mae_lped group by COD_ART) as DT1 ON MAE_ART.COD_ART = DT1.COD_ART
join mae_lped on DT1.COD_ART = mae_lped.COD_ART AND DT1.fecha = mae_lped.fecha

Hi Cat_jesus,

I can´t express myself better than saying "THANK YOU!!!!!!!!!!!!!"
It works fine and it shows me the results I wanted.
I really want to thank you (and the rest of the people responding to my problem) for helping me. It is fine to know that there are sill people in this world helping others just because they like to.

Again, THANK YOU!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources