Selection plusieurs dates dans base sql

Le 19/03/2021 à 23:01

bonjour

je souhaite avoir la plus petite valeur entre des dates debut et plus grande des dates fin

'************selection date***********
    datedebut = Format$(DTPicker1.Value, "yyyy-MM-dd")
    datefin = Format$(DTPicker2.Value, "yyyy-MM-dd")
 '************selection weekend**********
    DTDEBWEEK = Date - Weekday(Date, vbMonday) + 1
    DTFINWEEK = Date - Weekday(Date, vbMonday) + 7
    datedebutWEEK = Format$(DTDEBWEEK, "yyyy-MM-dd")
    datefinWEEK = Format$(DTFINWEEK, "yyyy-MM-dd")
 '************selection mois*************
    dFirst = DateSerial(Year(Date), Month(Date), 1)
    dLast = DateAdd("m", 1, dFirst) - 1
    datedebutMOIS = Format$(dFirst, "yyyy-MM-dd")
    datefinMOIS = Format$(dLast, "yyyy-MM-dd")

pour le moment je récupère chaque sélection date en plusieurs fois en remplaçant datedebut par datedebutWeek etc...

'***********selection site*************
    site = " T1.STA_SiteNumber =" & Sheets("DATA").Range("A2")
    stat = " T1.INF_NumberInStation =" & Sheets("DATA").Range("b2")

'********** Create the connection string.
     sConnString = "Provider=SQLOLEDB; Data Source=.**********

'********* Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

'********* Open the connection and execute.
    conn.Open sConnString

'*************lecture water meter***************************
  Set rs = conn.Execute("WITH v as(SELECT T1.ID , T1.STA_SiteNumber, T1.INF_NumberInStation," _
 & " T1.INF_Date, T1.INF_Value, row_number() over " _
 & "(partition by T1.STA_SiteNumber, T1.INF_NumberInStation order by T1.INF_Date) rn_asc," _
 & " row_number() over (partition by T1.STA_SiteNumber, T1.INF_NumberInStation " _
 & " order by T1.INF_Date desc) rn_desc FROM dbo.View_ArchivedInformations AS T1 " _
 & " where  " _
& "  ((convert(date, T1.INF_Date)>='" & datedebutWEEK & " ' ) and (convert(date, T1.INF_Date)<='" & datefinWEEK & " '))" _
 & "and  (" & site & " and " & stat & " )) " _
 & " select * from v where  rn_asc = 1 or rn_desc = 1; ")

est il possible de ne faire qu'un seule Set rs pour rechercher toutes les dates souhaitais

merci pour l'aide
Le 20/03/2021 à 14:33

bonjour,

essaie ceci (pas sûr d'avoir tout compris)

& " where  " _
& "  ((convert(date, T1.INF_Date)>='" & datedebut & " ' ) and (convert(date, T1.INF_Date)<='" & datefin & " ')) or " _
& "  ((convert(date, T1.INF_Date)>='" & datedebutWEEK & " ' ) and (convert(date, T1.INF_Date)<='" & datefinWEEK & " ')) or " _
& "  ((convert(date, T1.INF_Date)>='" & datedebutMOIS & " ' ) and (convert(date, T1.INF_Date)<='" & datefinMOIS & " '))" _
Le 20/03/2021 à 18:30

bonjour et merci

bon cela ne fonctionne pas

ca ne prend pas en compte and (" & site & " and " & stat & " )) et il me prend la plus première et la dernière valeur enregistré pour tout les site en stat

donc pour le moment je fait comme ça

Private Sub lecturebase11()
Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset   
    Dim sConnString As String
    Dim fld As ADODB.Field
    Dim Target As Range
    '*******************
  '  ************selection date***********
    datedebut = Format$(DTPicker1.Value, "yyyy-MM-dd")
    datefin = Format$(DTPicker2.Value, "yyyy-MM-dd")
 '************selection weekend**********
    DTDEBWEEK = Date - Weekday(Date, vbMonday) + 1
    DTFINWEEK = Date - Weekday(Date, vbMonday) + 7
    datedebutWEEK = Format$(DTDEBWEEK, "yyyy-MM-dd")
    datefinWEEK = Format$(DTFINWEEK, "yyyy-MM-dd")
 '************selection mois*************
    dFirst = DateSerial(Year(Date), Month(Date), 1)
    dLast = DateAdd("m", 1, dFirst) - 1
    datedebutMOIS = Format$(dFirst, "yyyy-MM-dd")
    datefinMOIS = Format$(dLast, "yyyy-MM-dd")
    '****************************** 
    site = " T1.STA_SiteNumber =11"
   stat = " T1.INF_NumberInStation =3"  
'********** Create the connection string.
     sConnString = "Provider=SQLOLEDB; Data Source=.\SQLEXPRESS;Initial Catalog=ScadaNetDb;User ID=EXTERNE;Password=EXTERNEPCWIN2; Trusted_Connection=no"

'********* Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

'********* Open the connection and execute.
    conn.Open sConnString

'******************lecture DTPicker ******************
  Set rs = conn.Execute("WITH v as(SELECT T1.ID , T1.STA_SiteNumber, T1.INF_NumberInStation," _
 & " T1.INF_Date, T1.INF_Value, row_number() over " _
 & "(partition by T1.STA_SiteNumber, T1.INF_NumberInStation order by T1.INF_Date) rn_asc," _
 & " row_number() over (partition by T1.STA_SiteNumber, T1.INF_NumberInStation " _
 & " order by T1.INF_Date desc) rn_desc FROM dbo.View_ArchivedInformations AS T1 " _
& " where  " _
& "  ((convert(date, T1.INF_Date)>='" & datedebut & " ' ) and (convert(date, T1.INF_Date)<='" & datefin & " '))  " _
& " and  (" & site & " and " & stat & " )) " _
 & " select * from v where  rn_asc = 1 or rn_desc = 1; ")
 '****************
    ' Check premier data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets("Feuil1").Range("b2").CopyFromRecordset rs
        End If
 '******************lecture semaine en cours****************************
  Set rs = conn.Execute("WITH v as(SELECT T1.ID , T1.STA_SiteNumber, T1.INF_NumberInStation," _
 & " T1.INF_Date, T1.INF_Value, row_number() over " _
 & "(partition by T1.STA_SiteNumber, T1.INF_NumberInStation order by T1.INF_Date) rn_asc," _
 & " row_number() over (partition by T1.STA_SiteNumber, T1.INF_NumberInStation " _
 & " order by T1.INF_Date desc) rn_desc FROM dbo.View_ArchivedInformations AS T1 " _
& " where  " _
& "  ((convert(date, T1.INF_Date)>='" & datedebutWEEK & " ' ) and (convert(date, T1.INF_Date)<='" & datefinWEEK & " '))  " _
& " and  (" & site & " and " & stat & " )) " _
 & " select * from v where  rn_asc = 1 or rn_desc = 1; ")
 '****************
    ' Check deuxieme data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets("Feuil1").Range("b4").CopyFromRecordset rs
        End If
'******************lecture mois en cours****************************
  Set rs = conn.Execute("WITH v as(SELECT T1.ID , T1.STA_SiteNumber, T1.INF_NumberInStation," _
 & " T1.INF_Date, T1.INF_Value, row_number() over " _
 & "(partition by T1.STA_SiteNumber, T1.INF_NumberInStation order by T1.INF_Date) rn_asc," _
 & " row_number() over (partition by T1.STA_SiteNumber, T1.INF_NumberInStation " _
 & " order by T1.INF_Date desc) rn_desc FROM dbo.View_ArchivedInformations AS T1 " _
& " where  " _
& "  ((convert(date, T1.INF_Date)>='" & datedebutMOIS & " ' ) and (convert(date, T1.INF_Date)<='" & datefinMOIS & " '))  " _
& " and  (" & site & " and " & stat & " )) " _
 & " select * from v where  rn_asc = 1 or rn_desc = 1; ")
 '****************
    ' Check deuxieme data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets("Feuil1").Range("b6").CopyFromRecordset rs
        End If
 '************ fermeture connexion***************
      rs.Close

End Sub

et ca me donne ceci

IDSTA_SiteNumberINF_NumberInStationINF_DateINF_Valuern_ascrn_desc
JOUR/DATE11132021-03-17 23:00:00.000000016481
21132021-03-16 00:00:00.00000001148
SEMAINE31132021-03-20 11:00:00.00000004051191
41132021-03-15 13:00:00.000000001119
MOIS51132021-03-20 11:00:00.00000004051191
61132021-03-15 13:00:00.000000001119
Le 23/03/2021 à 14:22

bonjour,

je pense avoir oublié des parenthèses

& " where  " _
& "  (((convert(date, T1.INF_Date)>='" & datedebut & " ' ) and (convert(date, T1.INF_Date)<='" & datefin & " ')) or " _
& "  ((convert(date, T1.INF_Date)>='" & datedebutWEEK & " ' ) and (convert(date, T1.INF_Date)<='" & datefinWEEK & " ')) or " _
& "  ((convert(date, T1.INF_Date)>='" & datedebutMOIS & " ' ) and (convert(date, T1.INF_Date)<='" & datefinMOIS & " ')))" _
Le 23/03/2021 à 15:42

bonjour

désolé ca ne fonctionne pas ça me renvoie la plus petite valeur date début et la plus grande date fin

ce que je souhaite c'est la plus petite valeur date début et la plus grande date fin pour 1 jour, 1semaine, 1 mois

donc 6 lignes

merci encore

Rechercher des sujets similaires à "selection dates base sql"