splitting the row into multiple rows based on the date difference.

Question

Hi,

I have this data

create table #temp_test
(
	id int,
	stdate date,
	endate date,
	cnt int
)

insert into #temp_test
(
	id,
	stdate,
	endate,
	cnt 
)
values
(
	1287,
	'2010-12-02',
	'2011-02-02',
	3
) 		 
select * from #temp_test 

One record is splitted to the difference of start and enddate count at each day level.

to be exact this is how the output should look like.

id   [date]     cnt 
---- ---------- ---
1287 2010-12-02 3/60  
1287 2010-12-03 3/60   
1287 2010-12-04 3/60  
.
.
.
.
.
.
.
1287 2011-02-01  3/60
1287 2011-02-02  3/60

for every day there shoud be one record with the date and count/noof days.

how can i do that using t-sql with a simple query.

Thanks,

Chakri.

Answer 1

Try this:

DECLARE @ID INT, @StartDate DATE, @EndDate Date
SELECT @ID = id, @StartDate = stdate, @EndDate = endate FROM #temp_test

;WITH Dates
AS
(
	SELECT ID = @id, Date = @StartDate
	UNION ALL
	SELECT @id,DATEADD(DAY,1,Date)
	FROM Dates
	WHERE DATEADD(DAY,1,Date) <= @EndDate
)
SELECT
	*
	,CAST(RANK() OVER(ORDER BY Date) AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM Dates) AS VARCHAR) AS Cnt
FROM Dates

Answer 2

You can use an auxiliary table of numbers. This table can be a permanent one or an inline TVF like this one:

Virtual Auxiliary Table of Numbers
http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers

select T.id, T.cnt, dateadd([day], D.n - 1, T.stdate) as the_date
from #temp_test as T cross apply dbo.GetNums(datediff([day], T.stdate, T.endate) + 1) as D;

---

AMB

Some guidelines for posting questions...

Answer 3

You can try using a numbers table.Something like

;WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L4)
  SELECT id,Dateadd(dd,n-1,A.stdate),cnt,DATEDIFF(dd,A.stdate,A.endate) as dtdiff FROM #temp_test A
  JOIN Nums B on DATEDIFF(dd,A.stdate,A.endate)+1>=B.n Order by n

---

Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer