Click here to Skip to main content
14,176,878 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have a table like below

ACADEMIC_TERM	ACADEMIC_YEAR	      START_DATE	            END_DATE
FALL	                2014          2014-10-12 00:00:00.000	2015-01-22 00:00:00.000
FALL	                2015	      2015-09-13 00:00:00.000	2016-01-05 00:00:00.000
SPRG	                2015	      2015-02-15 00:00:00.000	2015-05-28 00:00:00.000


I want to extend the date range

like below

DATE            ACADEMIC_YEAR       ACADEMIC_TERM
     12-10-2014      2014                FALL
     13-10-2014      2014                FALL
     ----------      ------              ----
     ----------      ------              ----
     ----------      ------              ----
     15-02-2015      2015                SPRG
     16-02-2015      2015                SPRG
     ----------      ------              ----
     ----------      ------              ----
     13-09-2015      2015                FALL
     ----------      ------              ----
     ----------      ------              ----
     ----------      ------              ----


the dates should create automatic with start and end date of each term. How can I do this?

What I have tried:

I created the dates between the start date of the first term and getdate() as the last date but it cannot identify which term is the date. For year I can simply take the Datepart(date)
Posted
Updated 18-Sep-18 22:48pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

I'd recommend to use CTE[^], which - in a short - means: a recursive query.

Take a look at example:
DECLARE @tmp TABLE(ACADEMIC_TERM VARCHAR(50), ACADEMIC_YEAR INT, [START_DATE] DATETIME, END_DATE DATETIME)

INSERT INTO @tmp (ACADEMIC_TERM, ACADEMIC_YEAR, [START_DATE], END_DATE)
VALUES('FALL', 2014, '2014-10-12 00:00:00.000', '2015-01-22 00:00:00.000'),
('FALL', 2015, '2015-09-13 00:00:00.000', '2016-01-05 00:00:00.000'),
('SPRG', 2015, '2015-02-15 00:00:00.000', '2015-05-28 00:00:00.000')

;WITH CTE AS
(
	--initial query
	SELECT ROW_NUMBER() OVER(ORDER BY ACADEMIC_YEAR) AS RowNo, 1 AS IterationID, ACADEMIC_TERM, ACADEMIC_YEAR, [START_DATE] AS CurrDate, END_DATE
	FROM @tmp 
	--recursive part
	UNION ALL
	SELECT RowNo, IterationID +1, ACADEMIC_TERM, ACADEMIC_YEAR, DATEADD(dd, 1, CurrDate)  AS CurrDate, END_DATE
	FROM CTE
	WHERE DATEADD(dd, IterationID, CurrDate)<=END_DATE 
)
SELECT CurrDate, ACADEMIC_TERM, ACADEMIC_YEAR 
FROM CTE 
ORDER BY RowNo, IterationID



For further information, please, see: DATEADD (Transact-SQL) | Microsoft Docs[^]

Good luck!
   
Comments
binu.emiliya 19-Sep-18 6:41am
   
This is working fine Thank you so much for your help
Maciej Los 19-Sep-18 6:42am
   
You're very welcome.
binu.emiliya 25-Sep-18 3:17am
   
Dear Maciej Los,

I am using this code now, but it is not returning last month data, say example Spring 2017 last date is 31-Dec-2107 but the code is getting data up to nov only.
Maciej Los 25-Sep-18 3:38am
   
This should work fine, even for 2017-12-31. Please, check out if date is in correct format.
binu.emiliya 25-Sep-18 4:27am
   
'2014-10-13 00:00:00.000' this is the date format I am using, and I am storing this code in a view so I am using TOP 100 PERCENT in this code. But I am not getting the last month of each semester dates.

Thank you for your time
Maciej Los 25-Sep-18 4:38am
   
Well, i have no idea why you don't get last month of each semester dates... Note, that i don't have an access to your HDD or SQL server. As you know, above CTE query works fine for sample data, so... is should also works for real data.
binu.emiliya 25-Sep-18 4:43am
   
Ok, Thank you!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I have recreated your table as @AcadmicDates and populated as such like below
DECLARE @AcademicDates TABLE (
     AcademicTerm  VARCHAR(6)  NULL,
     AcademicYear  INT         NULL,
     StartDate     DATE        NULL,
     EndDate       DATE        NULL
)

INSERT  @AcademicDates 
VALUES  ('Fall',    2014, '10/12/2014', '01/22/2015')
,       ('Fall',    2015, '09/13/2015', '01/05/2016')
,       ('Spring',  2015, '02/15/2015', '05/28/2015')


Your portion of "extending the date" range really is a simple query:
SELECT  AcademicYear, AcademicTerm
FROM    @AcademicDates
WHERE   '10/12/2014' BETWEEN StartDate AND EndDate


And could be used to populate a secondary table
DECLARE	@DateToAcademicDate TABLE (
     DateToCheck   DATE        NULL,
     AcademicYear  INT         NULL,
     AcademicTerm  VARCHAR(6)  NULL
)

DECLARE @RangeDate DATE = '10/12/2014'
WHILE (@RangeDate <= '01/05/2016') BEGIN 
  IF EXISTS(SELECT 1 FROM @AcademicDates WHERE @RangeDate BETWEEN StartDate AND EndDate) BEGIN
    INSERT  @DateToAcademicDate 
    SELECT  @RangeDate, AcademicYear, AcademicTerm
    FROM    @AcademicDates
    WHERE   @RangeDate BETWEEN StartDate AND EndDate
  END
  SET @RangeDate = DATEADD(dd, 1, @RangeDate)
END


Which will return the following
DateToCheck AcademicYear AcademicTerm
----------- ------------ ------------
2014-10-12  2014         Fall
...         2014         Fall
2015-01-22  2014         Fall
2015-02-15  2015         Spring
...         2015         Spring
2015-05-28  2015         Spring
2015-09-13  2015         Fall
...         2015         Fall
2016-01-05  2015         Fall
   
Comments
Maciej Los 18-Sep-18 14:40pm
   
Looks promisingly...
5ed!
binu.emiliya 19-Sep-18 6:42am
   
This is also working great.thank you so much for your time
binu.emiliya 26-Sep-18 1:37am
   
Dear MadMyche,
Is there any way to use this code in a VIEW?
MadMyche 26-Sep-18 9:20am
   
It could be developed into something like that; however, would need to know EXACTLY what you are looking for and what type of DB infrastructure you intend to maintain for supporting this
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

You can create a Date between range using this query

Declare @StartDay datetime, @EndDay datetime
Set @StartDay='2018-01-01'
Set @EndDay='2018-12-31'

;with cte(Date) as 
(
select @StartDay
union all
select Date+1 from cte where Date < @EndDay
)
select Date,DATENAME(W,Date)Day from cte option (MAXRECURSION 400)
   
Comments
Maciej Los 19-Sep-18 6:39am
   
This solution is partially helpful only. Take a look at date ranges... They're different. It isn't simple date-from to date-to, but the set of them. Please, see my answer.
binu.emiliya 19-Sep-18 6:42am
   
Thank you Krithiga. This I have already done

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web06 | 2.8.190526.1 | Last Updated 19 Sep 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100