Sql-server – How to get the total days per month between two dates

Can you do the calculations in the dataset the report uses? May have an easier time doing that then building the expression needed to calculate the column for SSRS

Pretending this is your real table:

CREATE TABLE #Employees
(
  EmployeeID INT PRIMARY KEY,
  StartDate  SMALLDATETIME,
  EndDate    SMALLDATETIME
);

INSERT #Employees(EmployeeID, StartDate, EndDate)
  VALUES(1,'20150621','20150821');

Then this is one approach to get the results you're after (you'll need to replace #Employees with dbo.YourRealTableName of course, and this assumes you won't have anyone who has worked more months than the number of rows in your version of sys.all_columns - if you have a Numbers table that is a better approach). I added year to the output because, hopefully, you have employees that last longer than 12 months and probably some of those will last past January 1st - so being able to tell which June is which might be important.

;WITH x AS 
(
  SELECT rn1 = ROW_NUMBER() OVER (ORDER BY [object_id])-1
  FROM sys.all_columns
),
y AS 
(
  SELECT
    e.EmployeeID,
    x.rn1, 
    rn2 = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY x.rn1 DESC), 
    e.StartDate, 
    EndDate = COALESCE(e.EndDate, DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)),
    sm = DATEADD(DAY, 1-DAY(StartDate), StartDate)
  FROM x INNER JOIN #Employees AS e
  ON x.rn1 <= DATEDIFF(MONTH, e.StartDate, COALESCE(e.EndDate, GETDATE()))
),
z AS
(
  SELECT 
    EmployeeID,
    s = CASE rn2 WHEN 1 THEN StartDate 
      ELSE DATEADD(MONTH, rn2-1, sm) END,
    e = CASE rn1 WHEN 0 THEN DATEADD(DAY, 1, EndDate)
      ELSE DATEADD(MONTH, rn2, sm) END
  FROM y
)
SELECT 
  EmployeeID,
  [Month] = DATENAME(MONTH, s), 
  [Year] = YEAR(s),
  WorkingDays = DATEDIFF(DAY, s, e)
FROM z
ORDER BY EmployeeID, s;
GO

This works for all employees, but you can easily add a filter inside y to limit it to a single employee or a subset. You might also want to filter for only those employees with an EndDate; it wasn't clear to me what you wanted to do with employees who still work there, so I assumed you would want to count working days up until today.

Don't forget to clean up:

DROP TABLE #Employees

Edit for updates (please stop changing the requirements):

CREATE TABLE #Employees
(
  EmployeeID INT PRIMARY KEY,
  StartDate  SMALLDATETIME,
  EndDate    SMALLDATETIME,
  Salary     INT
);

INSERT #Employees(EmployeeID, StartDate, EndDate, Salary)
  VALUES(1,'20150621','20150821',620);

Query:

;WITH x AS 
(
  SELECT rn1 = ROW_NUMBER() OVER (ORDER BY [object_id])-1
  FROM sys.all_columns
),
y AS 
(
  SELECT
    e.EmployeeID, e.Salary, x.rn1, 
    rn2 = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY x.rn1 DESC), 
    e.StartDate, 
    EndDate = COALESCE(e.EndDate, DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)),
    sm = DATEADD(DAY, 1-DAY(StartDate), StartDate)
  FROM x INNER JOIN #Employees AS e
  ON x.rn1 <= DATEDIFF(MONTH, e.StartDate, COALESCE(e.EndDate, GETDATE()))
),
z AS
(
  SELECT 
    EmployeeID, Salary,
    StartDate, EndDate,
    s = CASE rn2 WHEN 1 THEN StartDate 
      ELSE DATEADD(MONTH, rn2-1, sm) END,
    e = CASE rn1 WHEN 0 THEN DATEADD(DAY, 1, EndDate)
      ELSE DATEADD(MONTH, rn2, sm) END
  FROM y
)
SELECT 
  EmployeeID, 
  [Month] = DATENAME(MONTH, s), 
  [Year] = YEAR(s),
  WorkingDays = DATEDIFF(DAY, s, e),
  Portion = Salary * 1.0 * DATEDIFF(DAY, s, e) / (1+DATEDIFF(DAY, StartDate, EndDate))
FROM z
ORDER BY EmployeeID, s;

Results I get (there are decimals because your salary won't always be nicely divisible by the number of working days):

EmployeeID  Month   Year  WorkingDays  Portion
----------  -----   ----  -----------  ----------------
1           June    2015  10           100.000000000000
1           July    2015  31           310.000000000000
1           August  2015  21           210.000000000000

Again, clean up:

DROP TABLE #Employees