1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| Case "DE"
Set qdf = db.QueryDefs![RqDATLIV-DATEXP]
qdf.Connect = "ODBC;dsn=" & RSEntr!dsn
qdf.sql = "select refliv, datexp, datliv, livtot, cumlig from gehexpd " & _
" where datexp>" & datos & " and datexp<=" & maxdatdist
Debug.Print qdf.sql
Set qdf2 = db.CreateQueryDef("RQCreate")
With qdf2
.Connect = "ODBC;DSN=infolog C2"
.sql = "select refliv, datexp, datliv, livtot, cumlig from gehexpd " & _
" where datexp>" & datos & " and datexp<=" & maxdatdist & " and (gehexpd.REFLIV Like '%MACHIN%' or gehexpd.REFLIV Like '%BIDULE%')"
.ReturnsRecords = True
End With
Debug.Print qdf2.sql
str = "select sum(uvcecart)/max(totliv) as txservpcs, sum(ligecart)/max(ligliv) as txservlig, sum(cdecart)/max(cdeliv) as txservcde, datexp, 'C3' as entrepot, format(Datepart('ww',DateSerial(CInt(Left(Str(datexp),5)),CInt(Mid(Str(datexp),6,2)),CInt(Right(Str(datexp),2))),2),'00') AS semaine from( " & _
" SELECT count(refliv) AS cdecart, datexp, sum(livtot) AS uvcecart, datediff('d',DateSerial(CInt(Left(Str(datexp),5)),CInt(Mid(Str(datexp),6,2)),CInt(Right(Str(datexp),2))),DateSerial(CInt(Left(Str(datliv),5)),CInt(Mid(Str(datliv),6,2)),CInt(Right(Str(datliv),2)))) as jecart, " & _
" sum(cumlig) AS ligecart, (select count(refliv) from [RqDATLIV-DATEXP] as T1 where T1.datexp=T2.datexp group by datexp) AS cdeliv, " & _
" (select sum(cumlig) from [RqDATLIV-DATEXP] as T1 where T1.datexp=T2.datexp group by datexp) AS ligliv, " & _
" (select sum(livtot) from [RqDATLIV-DATEXP] as T1 where T1.datexp=T2.datexp group by datexp) AS totliv " & _
" FROM (SELECT * from [RqDATLIV-DATEXP] union SELECT * from RQCreate) as T2 " & _
" GROUP BY datexp, datediff('d',DateSerial(CInt(Left(Str(datexp),5)),CInt(Mid(Str(datexp),6,2)),CInt(Right(Str(datexp),2))),DateSerial(CInt(Left(Str(datliv),5)),CInt(Mid(Str(datliv),6,2)),CInt(Right(Str(datliv),2)))) " & _
" having datediff('d',DateSerial(CInt(Left(Str(datexp),5)),CInt(Mid(Str(datexp),6,2)),CInt(Right(Str(datexp),2))),DateSerial(CInt(Left(Str(datliv),5)),CInt(Mid(Str(datliv),6,2)),CInt(Right(Str(datliv),2))))<0) group by datexp" |
Partager