Complex Calcul - 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

Complex Calcul

Author

Topic

allo
Starting Member

3 Posts

Posted - 2009-06-22 : 06:45:57

Hello,

Thank you for your attention.

The aim is to calculate a total price

I have a table with id_prod PURCHASE product =
ty_doc = document type
nu_doc = document number
nucdli = code of document linked
px_com = price of the order

We have :

CREATE TABLE tbl_ligneachat (id_prod char(1), ty_doc char(3), nu_doc int , nucdli int NULL, px_com int NULL)
INSERT INTO tbl_ligneachat VALUES ('A','BE',13604,NULL,4)
INSERT INTO tbl_ligneachat VALUES ('B','BE',13604,4)
INSERT INTO tbl_ligneachat VALUES ('C','CDA',13604,NULL,3)
INSERT INTO tbl_ligneachat VALUES ('D','CDA',13604,NULL,3)
INSERT INTO tbl_ligneachat VALUES ('E','CDA',13604,NULL,2)
INSERT INTO tbl_ligneachat VALUES ('E','CDC',13661,13604,3)
INSERT INTO tbl_ligneachat VALUES ('B','CDC',13361,13604,2)
INSERT INTO tbl_ligneachat VALUES ('E','CDT',13360,13604,1)
INSERT INTO tbl_ligneachat VALUES ('E','FA',444,NULL,1)
INSERT INTO tbl_ligneachat VALUES ('E','BE','555',NULL,1)

And :

id_prod____ty_doc___ nu_doc___ nucdli___px_com
A___________ BE ____ 13604_____________ 4
B____________ BE ____ 13604 _____________ 4
C_______ ___ CDA ___ 13604 _____________ 3
D_______ ___ CDA ___ 13604 _____________ 3
E_______ ___ CDA ___ 13604 ____________ 2
E ______ ___ CDC ___ 13661 ___ 13604 ___ 3
B ______ ___ CDC ___ 13361 ___ 13604 ___ 2
E ______ ___ CDT ___ 13360 ___ 13604 ___ 1

My problem is :

I want to view in the CDA, the px_com accumulated when "nucdli" = "nu_doc"
And, the ligne with the nucdli corresponding must deasappear!

Finally we must obtain :

id_prod____ty_doc___ nu_doc___ nucdli___px_com
E___________ CDA ____13604 _____________6
B____________ BE ____ 13604 _____________ 4
C_______ ___ CDA ___ 13604 _____________ 3
D_______ ___ CDA ___ 13604 _____________ 3
B ______ ___ CDC ___ 13361 ___ 13604 ____ 2

My solution is :


SELECT
  k.id_produit,
  k.id_fournisseur,
  k.ty_commande,
  k.ty_document,
  k.nu_document,
  k.px_commande + coalesce(k3.px_commande,0) AS px_commande
FROM
  tbl_ligneachat k
           LEFT OUTER JOIN (
SELECT
  k2.id_produit,
  k2.id_fournisseur,
  k2.qt_cmde_ini,
  k2.ty_document,
  k2.nu_document,
  k2.px_commande,
  k2.nucdli

   FROM tbl_ligneachat K2) AS K3
         ON  K3.nucdli IS NOT NULL AND k3.nucdli=k.nu_document
WHERE K.nucdli IS NULL
         ORDER BY id_produit

This query returns me all the lines.

However, this does not calcul lines that have a nucdli = nu_document

----> RECAP :

As a first step, the request must includes the "nucdli"
(N° orders related) and "nu_document" (N° order)which have the same identifiers (2 columns in a table with the same identifier).

In a second step, it is supposed to add the "px_commande" (the cost of orders) correspondant.

Then, I must lose only lines with the nucdli correspondant.

I hope I was clear enough ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 07:09:16

Have you tried using a recursive CTE?
The recursive CTE requires SQL Server 2005 or later and compatibility level set to 90 or higher.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 10:34:03

sorry your explanation is not clear. can you explain how you got 4 as value for B?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 11:33:47

Anchor part is all records with empty (or NULL) nucdli and/or "cda" for ty_doc.
Recursive part is two-part key; id_prod and ty_doc. They are bound to parent with nucdli.

E 12°55'05.63"
N 56°04'39.26"

allo
Starting Member

3 Posts

Posted - 2009-06-22 : 11:35:41

I don't use SQL server but My sql.

I get 4 as value because ty_doc is "BE". So the value is the same.

We concatenate the nu_doc values with nucdli value only when ty_doc are CDA with CDD,CDC or CDT

nucdli exist only for ty_doc CDD,CDC,CDT.

We report the sum on ligne which have a CDA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 11:36:40

then you must be posting this in MySQL forum. try your luck at www.dbforums.com

allo
Starting Member

3 Posts

Posted - 2009-06-23 : 03:43:04

I post the message in MySQL forum. Thanx.

But I don't really understand the Peso's answer ....
Do you think that I don't carre with empty or null value ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources