1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
|
' Set f7 = fso.OpenTextFile(chemin & "\sql\t2.sql", ForReading)
' une_variable7 = f7.ReadAll
' f7.Close
' Set f07 = fso.OpenTextFile(chemin & "\test.txt", 2, True)
' f07.write (une_variable7)
'f07.Close
Sql = "WITH requete1 AS (SELECT G.razon AS Societe, T.descrip AS Atelier, R.Descrip AS tarif, TPF.Tecnicidad AS M, TPF.FechaDesde AS date_debut, TPF.PrecioHora AS prix"
Sql = Sql & " FROM ttTarifaPrecioFecha AS TPF"
Sql = Sql & " INNER JOIN tgempresa AS G ON TPF.emp = G.emp"
Sql = Sql & " INNER JOIN tgtaller AS T ON TPF.emp = T.emp AND TPF.taller = T.taller"
Sql = Sql & " INNER JOIN ttTarifa AS R ON R.Codigo=TPF.Codigo"
Sql = Sql & " WHERE TPF.Tecnicidad = 'M2' AND TPF.Codigo IN ('TCE','TCL','TGA')),"
Sql = Sql & "requete2 AS (SELECT Societe ,Atelier, tarif, date_debut, Prix, RANK() OVER(PARTITION BY Societe ,Atelier,tarif ORDER BY date_debut DESC) AS RANG FROM requete1)"
Sql = Sql & "SELECT Societe ,Atelier, tarif, date_debut ,prix, RANG FROM requete2 WHERE RANG = 1;"
Dim rsBatiiiiii As ADODB.Recordset
Set rsBatiiiiii = New ADODB.Recordset
With rsBatiiiiii
.ActiveConnection = cnBat
.Open Sql
DerniereLigne7 = Worksheets("Tarif M2").Range("A100000").End(xlUp).Row + 1
Worksheets("Tarif M2").Range("A" & DerniereLigne7).CopyFromRecordset rsBatiiiiii
.Close
End With |
Partager