Anexo un ejemplo de comandos xbase y consultas sql con SQLRDD (Marcos Gambeta)
con Harbour
Podría utilizar PIVOT como hacen en (sql server 2021)
Pero me pregunte:
¿ cómo lo hago con ambas tecnologías ?
¡ surgió la magia !
con buena velocidad y resultado
link de resultado en Excel:
https://mega.nz/file/ArYDjCiL#186gYzy27 ... CFp3BhURm8
Sigamos adelante!!
saludos!!
Código:
Código: Selecionar todos
#include "sqlrdd.ch"
Procedure RepCompMovxMes()
Local cSql, oSql , nPos, oExcel, oLibro, oHoja, X, xFile
Local nTask, nIdle, nError
Use Provee Index Prove01,;
Prove02 Shared New
BXX(11,10,16,35,,3,8,cCAJA1)
@ 11,14 SAY "Reporte de Movimientos por Mes" COLOR COLOR4
Do While .T.
@ 13,12 Say "DESDE : "
@ 14,12 Say "HASTA : "
@ 15,12 Say "PROVEE. : "
dFECINI := Date()
dFECFIN := Date()
cID_PROV := Space(5)
cID_PROVANT:= Space(5)
@ 13, 22 Get dFECINI Picture "@D" Valid DToC(dFECINI) != " / / "
Read
If LastKey() = 27
Exit
Endif
@ 14, 22 Get dFECFIN Picture "@D" Valid dFECFIN >= dFECINI
Read
If LastKey() = 27
Loop
Endif
If Year(dFecini) <> Year(dFecfin)
Plswait(.T., "El rango de fechas no pertenece al mismo año...")
Inkey(3)
Plswait(.f.)
Loop
Endif
@ 15, 22 Get cID_PROV Picture "@!S5" Valid SECTOR(@cID_PROV)
Read
If LastKey() = 27
Loop
Endif
If Empty(cID_PROV)
Loop
Endif
cSql:= "select "
cSql+= "ec.id_prov , "
cSql+= "pv.NOM , "
cSql+= "ec.folio , "
cSql+= "ec.fec_ord , "
cSql+= "ec.fec_cap , "
cSql+= "ec.factura , "
cSql+= "dc.id_prod , "
cSql+= "dc.ean , "
cSql+= "ar.descrip1 , "
cSql+= "isnull(dc.cant , 0) as [cantidad], "
cSql+= "isnull(dc.precio, 0.00) as [preciox] , "
cSql+= "dc.lote , "
cSql+= "dc.FECHACAD , "
cSql+= "ec.usuario , "
cSql+= "dc.siva as tiva , "
cSql+= "dc.iva as tasa , "
cSql+= "ec.sta , "
cSql+= "Year(ec.fec_cap) as [mysiglo] , "
cSql+= "Month(ec.fec_cap) as [mymes] , "
cSql+= "Day(ec.fec_cap) as [mydia] , "
cSql+= "isnull(ex.invfin_c, 0) as [fisico] , "
cSql+= "ar.descrip2 , "
cSql+= "isnull(ex.disp_c, 0) as [disp] , "
cSql+= "isnull(ex.ult_costo, 0) as [costoU] , "
cSql+= "isnull(ex.ponderado, 0) as [costoP] , "
cSql+= "ma.nomcorto "
cSql+= "from enccomp as ec "
cSql+= "inner join detcomp as dc on ec.folio = dc.folio "
cSql+= "left join articulo as ar on dc.id_prod = ar.idcve "
cSql+= "left join marca as ma on ar.id_marca = ma.cve "
cSql+= "left join provee as pv on ec.id_prov = pv.cve "
cSql+= "left join existenc as ex on dc.id_prod = ex.llave "
cSql+= "where ( ec.fec_cap >= " + SR_cDBValue(dFecini) + " and ec.fec_cap <= " + SR_cDBValue(dFecfin) + " ) "
cSql+= "and (ec.sta = '4') and (dc.sr_deleted <> 'T') and (ec.id_prov = " + "'" + cID_PROV + "'" + ") "
cSql+= "order by ec.fec_cap, ec.folio "
aRet :={}
aDatos:={}
oSql := SR_GetConnection()
oSql:exec(cSql,,.t.,@aRet)
If Len(aRet) > 0
cdat:= ""
For Each cdat in aRet
X:= hB_Enumindex()
cdc_id_prod := aRet[X, 7]
ncant := aRet[X,10]
nmes := aRet[X,19]
nPos:= 0
nPos:= AScan(aDatos, {|X| X[7] == cdc_id_prod })
If nPos = 0
AAdd(aDatos,{ aRet[X, 1] ,;
aRet[X, 2] ,;
aRet[X, 3] ,;
aRet[X, 4] ,;
aRet[X, 5] ,;
aRet[X, 6] ,;
aRet[X, 7] ,;
aRet[X, 8] ,;
aRet[X, 9] ,;
aRet[X,10] ,;
aRet[X,11] ,;
aRet[X,12] ,;
aRet[X,13] ,;
aRet[X,14] ,;
aRet[X,15] ,;
aRet[X,16] ,;
aRet[X,17] ,;
aRet[X,18] ,;
aRet[X,19] ,;
aRet[X,20] ,;
aRet[X,21] ,;
aRet[X,22] ,;
aRet[X,23] ,;
aRet[X,24] ,;
aRet[X,25] ,;
aRet[X,26] ,;
0 ,; // 27 ENE
0 ,; // 28 FEB
0 ,; // 29 MAR
0 ,; // 30 ABR
0 ,; // 31 MAY
0 ,; // 32 JUN
0 ,; // 33 JUL
0 ,; // 34 AGO
0 ,; // 25 SEP
0 ,; // 36 OCT
0 ,; // 37 NOV
0 }) // 38 DIC
nPos:= Len(aDatos)
Do Case
Case nmes = 1
aDatos[nPos, 27] := ncant
Case nmes = 2
aDatos[nPos, 28] := ncant
Case nmes = 3
aDatos[nPos, 29] := ncant
Case nmes = 4
aDatos[nPos, 30] := ncant
Case nmes = 5
aDatos[nPos, 31] := ncant
Case nmes = 6
aDatos[nPos, 32] := ncant
Case nmes = 7
aDatos[nPos, 33] := ncant
Case nmes = 8
aDatos[nPos, 34] := ncant
Case nmes = 9
aDatos[nPos, 35] := ncant
Case nmes = 10
aDatos[nPos, 36] := ncant
Case nmes = 11
aDatos[nPos, 37] := ncant
Case nmes = 12
aDatos[nPos, 38] := ncant
EndCase
Else
aDatos[nPos, 10] += ncant
Do Case
Case nmes = 1
aDatos[nPos, 27] += ncant
Case nmes = 2
aDatos[nPos, 28] += ncant
Case nmes = 3
aDatos[nPos, 29] += ncant
Case nmes = 4
aDatos[nPos, 30] += ncant
Case nmes = 5
aDatos[nPos, 31] += nCant
Case nmes = 6
aDatos[nPos, 32] += ncant
Case nmes = 7
aDatos[nPos, 33] += ncant
Case nmes = 8
aDatos[nPos, 34] += ncant
Case nmes = 9
aDatos[nPos, 35] += ncant
Case nmes = 10
aDatos[nPos, 36] += ncant
Case nmes = 11
aDatos[nPos, 37] += ncant
Case nmes = 12
aDATOS[nPos, 38] += ncant
Endcase
Endif
Next
If Len(aDatos) > 0
** Ordenamos la suma de cantidad de mayor a menor
ASort(aDatos,,,{|X, Y| X[10] > Y[10] })
PlsWait(.T.,"Creando Reporte, espere ...")
GeneraRepcompxMes()
PlsWait(.f.)
Endif
Else
PlsWait(.T.,"No existen movimientos a esta fecha ...")
Inkey(2)
PlsWait(.F.)
Endif
Enddo
Provee->(DBCloseArea())
Return
