How to get a count between to dates and write the count of rows into a table with T-SQL?

Question

My Language is T-SQL and I am working with MS SQLServer 2008.

Well, I have a table with a lot of data with information concerning employees. Every employee has a "startdate" (the time when he startet to work for the company) and and "enddate" (the time when he quit the job). I would like to write into a table the same count of rows as the employee worked for the company in month. For example:

My basic table:

Employee Number | StartDate | EndDate 4711 20150101 20150523

This example shows that the employee worked for the company for 5 Month. So I want to insert in the new table 5 rows with the following information:

New Table:

  Employee Number | StartDate | EndDate
  row1: 4711               20150101   20150523

  row2: 4711               20150201   20150523

  row3: 4711               20150301   20150523

  row4: 4711               20150401   20150523

  row5: 4711               20150501   20150523

I tried this to get the number of month between the dates. I guess I need to work with a cursor or something like that.

    declare @start DATE = '2011-05-01'
declare @end DATE = '2011-08-01'

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date)<=@end
)
select Datename(month,date) from months

Hope you got the idea, I tried to be as specific as I can.

possible duplicate of [Split row into several with SQL statement](http://stackoverflow.com/questions/32689234/split-row-into-several-with-sql-statement) — Shnugo, Sep 23 '15 at 08:17
You want to do it only with PLSQL ? It can be done quickly with php. — Prasad DLV, Sep 23 '15 at 08:28
Sorry, I forgot to mention...I am working with T-SQL on MS Server 2008 — Jinks, Sep 23 '15 at 08:31

score 3 · Accepted Answer · answered Sep 23 '15 at 09:01

I think that you were on right path.

declare @start DATE = (select min(startdate) from dbo.employee)
declare @end DATE = cast(sysdatetime() as date)

set @start = DATEADD(day, - datepart(day, @start) + 1, @start)

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date)<=@end
)
select employee.EmployeeNumber, Year = datepart(year, date), Month = DATENAME(month, date), employee.StartDate, employee.EndDate
from months
inner join dbo.employee on month.date >= employee.startdate and (month.date <= employee.enddate or employee.enddate is null)

score 1 · Answer 2 · answered Sep 24 '15 at 06:36

found your new question and want to show you another way:

You need a list of running numbers. In this example I create a very handsome function first. You will need this for sure in many scenarios...

CREATE FUNCTION [dbo].[GetRunningNumbers](@counter INT=10000000, @StartAt INT=0)
RETURNS TABLE
AS 
RETURN
WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally AS(
    SELECT TOP(ISNULL(@counter,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartAt,0) As Nmbr
    FROM E8
)
SELECT * FROM CteTally;
GO

Your problem is solved as a one-liner with CROSS APPLY. As you have different intervalls in each row you need a row based approach rather than a set based (what is the CTE).

DECLARE @tbl TABLE(id INT, someValue VARCHAR(10),StartDate DATETIME, EndDate DATETIME);
INSERT INTO @tbl VALUES(1,'test1',{d'2015-01-04'},{d'2015-01-06'})
                      ,(2,'test2',{d'2015-01-02'},{d'2015-01-08'}) --overlapping
                      ,(3,'test3',{d'2015-01-10'},{d'2015-01-13'});
SELECT *
      ,DATEADD(DAY,RuNmbr.Nmbr,StartDate) AS RunningDate
FROM @tbl AS tbl
CROSS APPLY dbo.GetRunningNumbers(DATEDIFF(DAY,StartDate,EndDate)+1,0) AS RuNmbr;

How to get a count between to dates and write the count of rows into a table with T-SQL?

2 Answers2