sql query calculating no of employees joined each financial year i.e from 1-04-2002 to 31-03-2003

Question

I have a table in which joining dates are give in datetime format. I have to calculate how many employees joined each financial year resp. ie for eg from 1-04-2002 to 31-03-2003.this should work for each year..from 2003 to 2004,2004 to 2005...n so on.

can anybdy help? thanxx.

Answer 1

You can map start date to financial year using YEAR(DATEADD(M,-3,JoinDate) I think and you can count records with a CTE, e.g.

with EmployeeStartFinYear(FinYear, EmployeeId)
as
(
    select  year(dateadd(M,-3,JoinDate)), EmployeeId
      from  Employees
      where JoinDate is not null
)
select  FinYear, count(EmployeeId)
  from  EmployeeStartFinYear
group by FinYear
order by FinYear;

Answer 2

Here's my answer. I think it looks horrid, but i think it works. I'll explain the logic behind it.

1. I declared the Start and End dates just because it's easier to write than a date.
2. The @Years variable is the difference in years between the start and end date.
3. @Counter is used to loop through the number of years stored in @Years. @Diff is always one more than @Counter because each time we go through the loop, we want to increment the date range so it's always 1 year, rather than be counting employees that joined in 1 year, then 2 years etc.
4. @TempTable stores the info we get each time we go through the query.

All the query does is get the count of employees between the Start Date and a year from that start date and puts it into a temp table. Then it looks through again, and gets the employees that started between Start Date + 1 and Start Date + 2.

Sorry if it's horrible and ugly and doesn't work.

    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    DECLARE @Years TINYINT
    DECLARE @Counter TINYINT
    DECLARE @Diff TINYINT        
    DECLARE @TempTable TABLE
    (
        FinancialYear   VARCHAR(9)
        ,Employees TINYINT
    )

    SET @Count = 0
    SET @Diff = 1
    SET @Years = DATEDIFF(yyyy, @StartDate, @EndDate)

    WHILE @Count < @Years - 1
    BEGIN
        SELECT
            CAST(DATEPART(yyyy, DATEADD(yyyy, @Count, @StartDate) AS VARCHAR(4)) + '-' + CAST(DATEPART(yyyy, DATEADD(yyyy, @Diff, @StartDate)) AS VARCHAR(4) AS FinancialYear
            ,COUNT(employee_id) AS Employees
        INTO @TempTable
        FROM
            Employees
        WHERE
            join_date >= @StartDate AND join_date < DATEADD(yyyy, 1, @StartDate)
        GROUP BY 
            CAST(DATEPART(yyyy, DATEADD(yyyy, @Count, @StartDate) AS VARCHAR(4)) + '-' + CAST(DATEPART(yyyy, DATEADD(yyyy, @Diff, @StartDate)) AS VARCHAR(4) 

        SET @Count = @Count + 1
        SET @Diff = @Diff + 1
    END

    SELECT * FROM @TempTable