Devam Ediyor

similar SQL query than yesterday

Hi,

this is our SQL query, which is workin fine now:

=======

update my011_lag_lagerdatei updTable, (select * from my011_lag_lagerdatei as lag

inner join my204_tmpfp_formpos

on my204_TMPFP_Mandant=my011_LAG_Mandant and my204_TMPFP_MandantOrdner=my011_LAG_MandantOrdner and

my204_TMPFP_Artikelnr=my011_LAG_Artikelnummer

left join ( select slbp.my022_LBP_ID_Lagerartikel, SUM(slbp.my022_LBP_Bewegung) AS sum_bewegung_total

from my022_lbp_lagerbewegungprot slbp

join my011_lag_lagerdatei ll on slbp.my022_LBP_ID_Lagerartikel=ll.my011_LAG_ID

where my022_LBP_Datum>ll.my011_LAG_Inventurdatum and my022_LBP_Mandant='SAND' and my022_LBP_MandantOrdner='SANDV12'

group by slbp.my022_LBP_ID_Lagerartikel

) lbp

on lbp.my022_LBP_ID_Lagerartikel=lag.my011_LAG_ID

where lag.my011_LAG_Mandant='SAND' and lag.my011_LAG_MandantOrdner='SANDV12' and my204_TMPFP_PCName='PCCHRIS'

) as temp

set updTable.my011_LAG_Lagermenge = (CASE

WHEN isnull(temp.sum_bewegung_total) THEN temp.my011_LAG_Inventurmenge ELSE temp.my011_LAG_Inventurmenge+temp.sum_bewegung_total END)

where updTable.my011_LAG_ID=temp.my011_LAG_ID

=========

Now I need a very similar query:

with old query it calculates the total sum of a field from table my022_lbp

now we have to calculate the total sum of a field from table my020_bez. The different part is, that the date to compare is in another linked table. For better understanding: my019_bes and my020_bez are tables of an order form, where my019_bes is the order header and my020_bez contains the order rows. My_019_bes and My_020_bez is a one-to-many-relationship where the connecting fields are:

my020_BEZ_Mandant=my019_BES_Mandant

my020_BEZ_MandantOrdner=my019_BES_MandantOrdner

my020_BEZ_Bestellnr=my019_BES_Bestellnr

The date to compare is my019_BES_Bestelldatum.

So, rows should summed where: my019_BES_Bestelldatum > my011_LAG_Inventurdatum

This filed from table my020_bez should be summed: my020_BEZ_OffeneMenge

Beceriler: MySQL

Daha fazlasını görün: sql query, sql different, sql select order, sql query field, sql query sum, form update sql, need sql query, sql update fields, workin, update query, sql table field, sql query date, sql order form, order date sql query, linked similar, case sum, calculate date sql, calculate order form, update sql table, sql field table, sql select calculate, php html query sql, calculate sql date, sql date calculate, sql relationship

İşveren Hakkında:
( 43 değerlendirme ) Sankt Andrae, Austria

Proje NO: #6560716

Seçilen:

yuriyvsl

Vom Auftraggeber geheuert

20$ USD / saat
(7 Değerlendirme)
3.1