Adding data row

1

I have these data

ID subject Date1 Date2

M001 Math 1/1/2017 20/01/2017
M002 Aljabar 2/2/2017 20/02/2017
E001 Conversation 3/1/2017 25/01/2017
E002 Vocabs 3/1/2017 20/02/2017

I want to make it like this :

ID Resultdate

M001 1/1/2017
M001 2/1/2017
M001 3/1/2017
so on until 20/1/2017
M002 2/2/2017
M002 3/2/2017
so on until 20/02/2017
E001 3/1/2017
E001 4/1/2017
so on until 25/01/2017
E002 3/1/2017
E002 4/1/2017
so on until 20/02/2017

what should I do ?

thx

2

Do you have a calendar table ? You can use this , and join with source table on calendarDate >= Data1 and calendarDate<=Data2

I used spt_values ,

declare @tv_Source as table
( ID CHAR(4) NOT NULL,
  Subject VARCHAR(50) NOT NULL,
  Date1  DATE NOT NULL,
  Date2  DATE NOT NULL
)

INSERT INTO @tv_Source(ID,Subject,Date1,Date2)
VALUES('M001','Math','20170101','20170120'),
	   ('M002','Aljabar','20170202','20170220'),
	   ('E001','Conversation','20170103','20170125'),
	   ('E002','Vocabs','20170103','20170220')


SELECT
	S.ID
	,DATEADD(DAY,v.number,S.Date1) AS ResultDate
FROM @tv_Source AS S
  INNER JOIN (SELECT number FROM master..spt_values WHERE type ='p' AND number <=356) AS V
  ON S.Date2>= DATEADD(DAY,v.number,S.Date1)
--WHERE
--	S.Date2>= DATEADD(DAY,v.number,S.Date1)

If you have a calendarTable, you can replace this part:

INNER JOIN (SELECT number FROM master..spt_values WHERE type ='p' AND number <=356) AS V
  ON S.Date2>= DATEADD(DAY,v.number,S.Date1)
3

does ..
" INNER JOIN (SELECT number FROM master..spt_values WHERE type ='p' AND number <=356) AS V "
mean is master table? or I must create the master table? and how does the structure ?

thx

4

master..spt_values

is a build in system table , from Microsoft. You don't have to create it. It is placed in master.

If you have a calendar table(search pls on internet for it) , use it instead of "V"

5

if the next date is 6 days after the before date, what should I do?
thanks a lot

6

Sorry, I don't understand. Can you give an example ?

7

the result will be like this
M001 1/1/2017
M001 7/1/2017
M001 14/1/2017
so on until 20/1/2017
M002 2/2/2017
M002 8/2/2017
so on

the date adding for next week (6 days later)..

thx

8

I multiply with 6

SELECT
	S.ID
	,DATEADD(DAY,v.number*6,S.Date1) AS ResultDate
FROM @tv_Source AS S
  INNER JOIN (SELECT number FROM master..spt_values WHERE type ='p' AND number <=356) AS V
  ON S.Date2>= DATEADD(DAY,v.number*6,S.Date1)
9

For something like this, I like to use either a numbers/tally table or function. In this case, I'll use a function called tfn_Tally (I'll post the function code below the answer)...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;
GO

CREATE TABLE #TestData (
	CourseID CHAR(4) NOT NULL,
	CourseTitle VARCHAR(20) NOT NULL,
	BegDate DATE NOT NULL,
	EndDate DATE NOT NULL 
	);
INSERT #TestData (CourseID, CourseTitle, BegDate, EndDate) VALUES
	('M001', 'Math',			'1/1/2017', '01/20/2017'),
	('M002', 'Aljabar',			'2/2/2017', '02/20/2017'),
	('E001', 'Conversation',	'1/3/2017', '01/25/2017'),
	('E002', 'Vocabs',			'1/3/2017', '02/20/2017');

--=========================================================

SELECT 
	td.CourseID, 
	td.CourseTitle, 
	TheDate = DATEADD(dd, t.n, td.BegDate)
FROM
	#TestData td
	CROSS APPLY dbo.tfn_Tally(DATEDIFF(dd, td.BegDate, td.EndDate) + 1, 0) t;

Code for tfn_Tally...

CREATE FUNCTION dbo.tfn_Tally
/* ============================================================================
07/20/2017 JL, Created. Capable of creating a sequense of rows 
				ranging from -10,000,000,000,000,000 to 10,000,000,000,000,000
============================================================================ */
(
	@NumOfRows BIGINT,
	@StartWith BIGINT 
)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN
	WITH 
		cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),	-- 10 rows
		cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),								-- 100 rows
		cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),								-- 10,000 rows
		cte_n4 (n) AS (SELECT 1 FROM cte_n3 a CROSS JOIN cte_n3 b),								-- 100,000,000 rows
		cte_Tally (n) AS (
			SELECT TOP (@NumOfRows)
				(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) + @StartWith
			FROM 
				cte_n4 a CROSS JOIN cte_n4 b													-- 10,000,000,000,000,000 rows
			)
	SELECT 
		t.n
	FROM 
		cte_Tally t;
GO
10

fantastic..it's working..thanks a lot

11

Super,
you are welcome