Trouver un employé de YTD Moyenne

Voici SQL Fiddle avec vos données d'échantillon mises à jour. Il y a deux requêtes: la première renvoie un seul nombre moyen, la seconde renvoie les nombres quotidiens pour aider à comprendre comment cela fonctionne. Suivez les dates et vous pouvez voir comment le nombre change au fur et à mesure que les gens vont et viennent.

Pour chaque personne, vous devez connaître deux dates: quand il a été embauché et quand il est parti. J'espère que c'est ce que LastHireDate et TerminationDate signifient. Je suppose que NULL TerminationDate signifie que la personne n'a pas encore quitté, est toujours employé.

Lorsque je calcule des rapports similaires, je calcule le nombre de personnes employées pour chaque jour dans la fourchette donnée (plutôt que le mois). Ensuite, vous pouvez augmenter la moyenne des nombres quotidiens au besoin.

J'utilise une table de Calendar . Cette table a simplement une list de dates pour plusieurs décennies.

 CREATE TABLE [dbo].[Calendar]( [dt] [date] NOT NULL, CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED ( [dt] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

Dans mon système, il y a quelques colonnes supplémentaires, telles que [IsLastDayOfMonth] , [IsLastDayOfQuarter] , qui sont utiles dans certains rapports, mais dans votre cas, vous n'avez besoin que de la colonne de date. Il existe plusieurs façons de remplir une telle table .

Par exemple, 100K lignes (~ 270 ans) à partir du 1900-01-01:

 INSERT INTO dbo.Calendar (dt) SELECT TOP (100000) DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '19000101') AS dt FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 OPTION (MAXDOP 1); 

Une fois que vous avez la table Calendar , voici comment l'utiliser:

 WITH CTE_EmployedPeople -- this is how many people were employed on each day in the given period AS ( SELECT dbo.Calendar.dt ,CAST(COUNT(*) as float) AS People -- without this cast the final average is int FROM dbo.Calendar CROSS JOIN EmployeeTable WHERE (dbo.Calendar.dt >= '2015-01-01') AND (dbo.Calendar.dt <= '2015-06-30') AND (dbo.Calendar.dt >= EmployeeTable.LastHireDate) AND (dbo.Calendar.dt <= EmployeeTable.TerminationDate OR EmployeeTable.TerminationDate IS NULL) GROUP BY dbo.Calendar.dt ) ,CTE_Daily -- if it is possible that nobody was employed on a certain day -- left join previous results to the Calendar table again to get 0 for such days AS ( SELECT dbo.Calendar.dt ,ISNULL(CTE_EmployedPeople.People, 0) AS People FROM dbo.Calendar LEFT JOIN CTE_EmployedPeople ON dbo.Calendar.dt = CTE_EmployedPeople.dt WHERE (dbo.Calendar.dt >= '2015-01-01') AND (dbo.Calendar.dt <= '2015-06-30') ) -- simple average of daily numbers SELECT AVG(People) AS AvgPeople FROM CTE_Daily; 

Aller avec la réponse de @ Vladimir Baranov pour une solution générique.

Mais dans votre cas particulier, vous n'aurez peut-être pas besoin de calculer les employés par mois et de les calculer en moyenne. Le fait de simplement additionner le nombre de mois employés dans la fourchette demandée et de la split par 6 renvoie le même résultat.

  SELECT -- approximate monthly average SUM(datediff(month, start_dt, end_dt)+1) / 6.00, -- exact daily average SUM(datediff(day, start_dt, end_dt)+1) / cast(datediff(day, '2015-01-01', '2015-07-01') as float) FROM ( SELECT LastHireDate ,TerminationDate -- fixing start date to match the requested range ,CASE WHEN LastHireDate < '2015-01-01 00:00:00' THEN '2015-01-01 00:00:00' ELSE LastHireDate END AS start_dt -- fixing end date to match the requested range ,CASE WHEN TerminationDate <= '2015-06-30 23:59:59' THEN TerminationDate ELSE '2015-06-30 23:59:59' END AS end_dt FROM EmployeeTable AS a WHERE a.OrgCodeIdNo = '69' -- As @Turophile mentioned, your logic seems to be wrong, -- your sample result shows employees hired after june 2015 AND (TerminationDate >= '2015-01-01 00:00:00' OR (TerminationDate IS NULL AND a.employeestatus = 'Active'))) AND LastHireDate <= '2015-06-30 23:59:59' ) AS dt 

Cette solution countra un employé s'il a été embauché seulement un seul jour dans un mois, bien sûr cela pourrait ne pas être une moyenne correcte basée sur la façon dont vous définissez "nombre d'employés par mois".

Modifier:

Ajout d'un calcul comme Vladimir Baranov pour get une moyenne quotidienne:

violon

Pourriez-vous utiliser DATEPART?

 SELECT COUNT( 1 ) / 6.0 FROM EmployeeTable AS a WHERE OrgCodeIdNo = '69' AND DATEPART( YEAR, TerminationDate ) = 2015 AND DATEPART( MONTH, TerminationDate ) <= 6