Analyses Statistiques LAD

Analyses Statistiques LAD

Télécharger le fichier Excel Analyses Statistiques LAD

Remplir la colonne C (Nb docs) avec les résultats du script:

SELECT
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\01062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '01-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\02062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '02-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\03062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '03-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\04062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '04-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\07062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '07-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\08062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '08-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\09062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '09-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\10062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '10-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\11062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '11-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\14062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '14-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\15062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '15-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\16062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '16-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\17062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '17-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\18062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '18-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\21062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '21-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\22062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '22-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\23062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '23-06',
  (SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\24062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '24-06',
 (SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\25062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '25-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\28062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '28-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\29062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '29-06',
  (SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\30062021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '30-06',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\01072021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '01-07',
(SELECT COUNT(SF_STACKS.STACK_ID) 
  FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
  INNER JOIN SmartFix_Control_DB.smartfix.SF_STACK_ATTR ON SF_STACK_ATTR.STACK_ID = SF_STACKS.STACK_ID
  INNER JOIN SmartFix_Control_DB.smartfix.SF_OVERVIEW_EDGE ON SF_STACK_ATTR.STACK_ID = SF_OVERVIEW_EDGE.STACK_ID
  WHERE SF_STACKS.SUBSYSTEM='Invoice' 
  AND ATTR_VALUE LIKE '%\02072021%'
  AND ATTR_NAME = '$SourceFile'
  AND FROM_NODE = 'Analyser' AND TO_NODE = 'Learn') AS '02-07';

Les résultats:

Remplir la colonne D (Duree-sec) avec le résultat du script:

SELECT SUM(VER_DURATION) 
FROM [SmartFix_Control_DB].[smartfix].[SF_STACKS]
WHERE VER_START BETWEEN '2021-02-07 00:00' AND '2021-02-07 23:59'

  /* Year-day-month */

Les résultats: