SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Help me with my Syntax
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rtr1900
Starting Member

48 Posts

Posted - 04/23/2008 :  10:46:51  Show Profile  Reply with Quote
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

Sweden
30216 Posts

Posted - 04/23/2008 :  10:58:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why are you grouping by "MAE_ART.STOCK_R" twice?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

rtr1900
Starting Member

48 Posts

Posted - 04/23/2008 :  11:09:18  Show Profile  Reply with Quote
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!
Go to Top of Page

rtr1900
Starting Member

48 Posts

Posted - 04/23/2008 :  12:12:43  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/23/2008 :  12:50:08  Show Profile  Reply with Quote
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?
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 04/23/2008 :  14:59:26  Show Profile  Reply with Quote
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
Go to Top of Page

chandan_joshi80
Starting Member

India
30 Posts

Posted - 04/24/2008 :  01:28:02  Show Profile  Reply with Quote
I THINK MORE THAN ONE RECORD IS SATISFYING YOUR JOIN CONDITION,SO ACCORDING TO THAT RESULT IS CORRECT.


chandan Joshi
Go to Top of Page

rtr1900
Starting Member

48 Posts

Posted - 04/24/2008 :  04:16:57  Show Profile  Reply with Quote
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!
Go to Top of Page

rtr1900
Starting Member

48 Posts

Posted - 04/24/2008 :  04:21:38  Show Profile  Reply with Quote
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?
Go to Top of Page

rtr1900
Starting Member

48 Posts

Posted - 04/24/2008 :  04:45:48  Show Profile  Reply with Quote
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!!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000