on 08-20-2013 3:01 AM
Tengo el siguiente query que funciona bien y quisiera agregarle el número de factura, pero no sé qué campo utilizar ¿me pueden ayudar?
SELECT distinct t1.docentry,t1.baseref as 'O.C',t1.basedocnum,baseentry,t1.linestatus, ultimaf.maxdocdate,t1.itemcode,t1.Dscription,
t1.currency,t1.rate as ' t.c.',
T6.ItmsGrpCod AS 'COD. GRUPO',
T6.ItmsGrpNam AS 'NOM. GRUPO',
t3.cardcode AS 'COD. PROV',
t3.cardname AS 'NOM. PROV'
from pch1 t1
inner join
(
SELECT itemcode,max(docdate) as 'maxdocdate' FROM pch1 group by itemcode
) ultimaf on t1.itemcode=ultimaf.itemcode and t1.docdate=ultimaf.maxdocdate
left join OCRD t3 on t1.basecard = t3.cardcode
left join (select t4.itemcode,t4.ItmsGrpCod,t5.ItmsGrpNam,t4.frozenfor from oitm T4 left join OITB T5 on t4.ItmsGrpCod=t5.ItmsGrpCod) t6 on t1.itemcode = t6.itemcode
where t6.frozenfor='N'
order by t1.itemcode
Muchas gracias
Si estas almacenando el No. Factura en la NumatCard de OPCH, intenta así:
SELECT distinct
t1.docentry,
t1.baseref as 'O.C',
t1.basedocnum,
baseentry,
t1.linestatus,
ultimaf.maxdocdate,
t1.itemcode,
t1.Dscription,
t1.currency,
t1.rate as ' t.c.',
T6.ItmsGrpCod AS 'COD. GRUPO',
T6.ItmsGrpNam AS 'NOM. GRUPO',
t3.cardcode AS 'COD. PROV',
t3.cardname AS 'NOM. PROV',
T0.NumAtcard 'No. FACT PROVEEDOR'
from pch1 t1 inner join
opch t0 on t1.DocEntry=t0.DocEntry
inner join
(
SELECT
itemcode,
max(docdate) as 'maxdocdate' FROM pch1 group by itemcode
) ultimaf on t1.itemcode=ultimaf.itemcode and t1.docdate=ultimaf.maxdocdate
left join OCRD t3 on t1.basecard = t3.cardcode
left join (select t4.itemcode,t4.ItmsGrpCod,t5.ItmsGrpNam,t4.frozenfor
from oitm T4 left join OITB T5 on t4.ItmsGrpCod=t5.ItmsGrpCod) t6 on t1.itemcode = t6.itemcode
where t6.frozenfor='N'
order by t1.itemcode
Si no, ¿donde lo estas colocando?
Saludos.
Alessandro.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.