Gap between the Terms. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Gap between the Terms.

Author

Topic

spendyala
Starting Member

15 Posts

Posted - 2010-06-18 : 15:55:57

Hi I am looking for some help in finding the gap between each term. I want a total number of holidays between all the terms.

Could somebody please help me with this. Thanks in advance.

DECLARE @EnrollDetails TABLE (EnrollID INT, TERMID INT, StartDate DATETIME, EndDate DATETIME)
INSERT @EnrollDetails
SELECT 36040,51801,'2004-01-13 00:00:00.000','2004-03-30 00:00:00.000' UNION ALL
SELECT 36040,51801,'2004-01-14 00:00:00.000','2004-03-31 00:00:00.000' UNION ALL
SELECT 36040,51801,'2004-01-16 00:00:00.000','2004-04-02 00:00:00.000' UNION ALL
SELECT 36040,51844,'2004-04-12 00:00:00.000','2004-06-28 00:00:00.000' UNION ALL
SELECT 36040,51844,'2004-04-15 00:00:00.000','2004-07-01 00:00:00.000' UNION ALL
SELECT 36040,51844,'2004-04-16 00:00:00.000','2004-07-02 00:00:00.000' UNION ALL
SELECT 36040,51881,'2004-07-12 00:00:00.000','2004-09-27 00:00:00.000' UNION ALL
SELECT 36040,51881,'2004-07-14 00:00:00.000','2004-09-29 00:00:00.000' UNION ALL
SELECT 36040,51881,'2004-07-16 00:00:00.000','2004-10-01 00:00:00.000' UNION ALL
SELECT 36040,51913,'2004-10-12 00:00:00.000','2005-01-11 00:00:00.000' UNION ALL
SELECT 36040,51913,'2004-10-13 00:00:00.000','2005-01-12 00:00:00.000' UNION ALL
SELECT 36040,51913,'2004-10-13 00:00:00.000','2005-01-12 00:00:00.000' UNION ALL
SELECT 36040,51954,'2005-01-18 00:00:00.000','2005-04-05 00:00:00.000' UNION ALL
SELECT 36040,51954,'2005-01-19 00:00:00.000','2005-04-06 00:00:00.000' UNION ALL
SELECT 36040,51954,'2005-01-19 00:00:00.000','2005-04-06 00:00:00.000' UNION ALL
SELECT 36040,51984,'2005-04-19 00:00:00.000','2005-05-24 00:00:00.000' UNION ALL
SELECT 36040,51984,'2005-04-20 00:00:00.000','2005-05-27 00:00:00.000' UNION ALL
SELECT 36040,51984,'2005-04-22 00:00:00.000','2005-07-08 00:00:00.000' UNION ALL
SELECT 36040,52002,'2005-05-31 00:00:00.000','2005-07-05 00:00:00.000'

The Term 51801 last class is on 2004-04-02 and the next terms start date is on 2004-04-12. So the difference or gap between these two terms is 10 days. I am looking for all the gaps or holidays for this Students between teh Terms.

Any help on this is appreciated.

I tried by inserting the minimum of startdate and maximum of enddate for each term in a temp table and again joining them using a row number, some how, i believe there would be some easy solution for this.

Thank you,

Sri.

josephj1989
Starting Member

7 Posts

Posted - 2010-06-18 : 18:17:34

This should work
select sum(DATEDIFF(day,prevend,startdate)) as TotalHolidays
--select x.*,datediff(day,prevend,startdate) as diff
from
(
select a.*,
(select max(b.EndDate) from @EnrollDetails as b where b.EndDate<a.startdate) prevend
from @EnrollDetails as a)
as x where datediff(day,prevend,startdate)>0

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-18 : 19:50:26

I think this will work better, more code but shows diff in term dates.

select identity(int,1,1) as id,termid,min(startdate) as sdate,max(enddate) as edate
into #tmpTerm
from @EnrollDetails
group by termid
order by termid

select trm.termid,trm2.termid,trm.edate,trm2.sdate,
case when datediff(dd,dateadd(dd,1,trm.edate),trm2.sdate) < 0 then 0 else datediff(dd,dateadd(dd,1,trm.edate),trm2.sdate) end as diff
from #tmpTerm trm
join #tmpTerm trm2 on trm.id + 1 = trm2.id
join #tmpTerm trm2 on trm.id + 1 = trm2.id

Sachin.Nand

2937 Posts

Posted - 2010-06-20 : 14:36:55

[code]
DECLARE @EnrollDetails TABLE (id int identity,EnrollID INT, TERMID INT, StartDate DATETIME, EndDate DATETIME)
INSERT @EnrollDetails
SELECT 36040,51801,'2004-01-13 00:00:00.000','2004-03-30 00:00:00.000' UNION ALL
SELECT 36040,51801,'2004-01-14 00:00:00.000','2004-03-31 00:00:00.000' UNION ALL
SELECT 36040,51801,'2004-01-16 00:00:00.000','2004-04-02 00:00:00.000' UNION ALL
SELECT 36040,51844,'2004-04-12 00:00:00.000','2004-06-28 00:00:00.000' UNION ALL
SELECT 36040,51844,'2004-04-15 00:00:00.000','2004-07-01 00:00:00.000' UNION ALL
SELECT 36040,51844,'2004-04-16 00:00:00.000','2004-07-02 00:00:00.000' UNION ALL
SELECT 36040,51881,'2004-07-12 00:00:00.000','2004-09-27 00:00:00.000' UNION ALL
SELECT 36040,51881,'2004-07-14 00:00:00.000','2004-09-29 00:00:00.000' UNION ALL
SELECT 36040,51881,'2004-07-16 00:00:00.000','2004-10-01 00:00:00.000' UNION ALL
SELECT 36040,51913,'2004-10-12 00:00:00.000','2005-01-11 00:00:00.000' UNION ALL
SELECT 36040,51913,'2004-10-13 00:00:00.000','2005-01-12 00:00:00.000' UNION ALL
SELECT 36040,51913,'2004-10-13 00:00:00.000','2005-01-12 00:00:00.000' UNION ALL
SELECT 36040,51954,'2005-01-18 00:00:00.000','2005-04-05 00:00:00.000' UNION ALL
SELECT 36040,51954,'2005-01-19 00:00:00.000','2005-04-06 00:00:00.000' UNION ALL
SELECT 36040,51954,'2005-01-19 00:00:00.000','2005-04-06 00:00:00.000' UNION ALL
SELECT 36040,51984,'2005-04-19 00:00:00.000','2005-05-24 00:00:00.000' UNION ALL
SELECT 36040,51984,'2005-04-20 00:00:00.000','2005-05-27 00:00:00.000' UNION ALL
SELECT 36040,51984,'2005-04-22 00:00:00.000','2005-07-08 00:00:00.000' UNION ALL
SELECT 36040,52002,'2005-05-31 00:00:00.000','2005-07-05 00:00:00.000'

select * from @EnrollDetails

select e1.EnrollID,e1.TERMID,DATEDIFF(dd,e1.EndDate,E.StartDate) as Difference from @EnrollDetails e1
cross apply(select top 1 * from @EnrollDetails e2 where e1.EnrollID=e2.EnrollID and e1.TERMID<>e2.TERMID
and e1.id+1=e2.id
order by EndDate )E

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

spendyala
Starting Member

15 Posts

Posted - 2010-06-21 : 14:28:37

Thank you All!!!

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 02:46:19