SELECT DISTINCT CONVERT(VARCHAR, LOG_DATE, 103) AS date103, CONVERT(VARCHAR, LOG_DATE, 112) AS Expr2 , PDA.bt, PDA_MOBIL.bm, PDA_FIXE.bf,PDA_INCONNU.bx FROM dbo.TPDA_HISTO_LOG LEFT JOIN ( SELECT TOP 100 PERCENT COUNT(ID_LOG) AS bt, CONVERT(VARCHAR, LOG_DATE, 103) AS datebt, CONVERT(VARCHAR, LOG_DATE, 112) AS datebt2 FROM dbo.TPDA_HISTO_LOG GROUP BY CONVERT(VARCHAR, LOG_DATE, 103), CONVERT(VARCHAR, LOG_DATE, 112) ) PDA ON PDA.datebt = CONVERT(VARCHAR, TPDA_HISTO_LOG.LOG_DATE, 103) LEFT JOIN ( SELECT TOP 100 PERCENT COUNT(ID_LOG) AS bm, CONVERT(VARCHAR, LOG_DATE, 103) AS datebm, CONVERT(VARCHAR, LOG_DATE, 112) AS datebm2 FROM dbo.TPDA_HISTO_LOG WHERE PDA = -3 GROUP BY CONVERT(VARCHAR, LOG_DATE, 103), CONVERT(VARCHAR, LOG_DATE, 112) ) PDA_MOBIL ON PDA_MOBIL.datebm = CONVERT(VARCHAR, TPDA_HISTO_LOG.LOG_DATE, 103) LEFT JOIN ( SELECT TOP 100 PERCENT COUNT(ID_LOG) AS bf, CONVERT(VARCHAR, LOG_DATE, 103) AS datebf FROM dbo.TPDA_HISTO_LOG WHERE PDA = -1 GROUP BY CONVERT(VARCHAR, LOG_DATE, 103) ) PDA_FIXE ON PDA_FIXE.datebf = CONVERT(VARCHAR, TPDA_HISTO_LOG.LOG_DATE, 103) LEFT JOIN ( SELECT TOP 100 PERCENT COUNT(ID_LOG) AS bx, CONVERT(VARCHAR, LOG_DATE, 103) AS datebx FROM dbo.TPDA_HISTO_LOG WHERE PDA = 36 GROUP BY CONVERT(VARCHAR, LOG_DATE, 103) ) PDA_INCONNU ON PDA_INCONNU.datebx = CONVERT(VARCHAR, TPDA_HISTO_LOG.LOG_DATE, 103)ORDER BY Expr2
J'aimerais faire aussi la même chose sur une table de log historisée qui s'appelle TPDA_HISTO_LOG à la place de TPDA_LOG.
Partager