SQL help with date comaprisons

Question

Post reply

SQL help with date comaprisons

SSCrazy Eights

Points: 9892

August 1, 2018 at 10:28 am

Hi,
I have a scenario where i need to calculate the number of days of hold on a loan .
Example is provided below. HoldSequence indicates the appearence of loan sequence, order by date . We need to consider the Holdflag/indicator i.e the loan is still on hold or not. if 'Yes' then the Hold end date is always Null , meaning loan is still on hold.

Overlap field is for my explanation , if there is overlap in loan holds meaning we have continuous holds on that particular loan so , we need to calculate the difference from first appearance of hold on loan till today (example : loan 12346). If there are gaps on hold meaning the loan is not in hold continuously then we need to calculate the number of days of hold when the hold started till today example :loan 12345. My question :how can we compare the date difference in enddate and satrtdate of next hold apperance?

Loannumber	Hold indicator	holdsequence	HoldStartdate	HoldEnddate	Overlap
12345	N	1	8/10/2015	9/25/2015
12345	N	2	9/16/2016	9/19/2016	9/16<9/25 (yes)
12345	N	3	10/14/2016	12/2/2016	no
12345	N	4	10/14/2016	12/2/2016	yes (10/14<12/02)
12345	N	5	10/14/2016	12/2/2016	yes (10/14<12/02)
12345	N	6	10/14/2016	12/2/2016	yes (10/14<12/02)
12345	N	7	11/16/2016	3/23/2017	yes (11/16<12/02)
12345	Y	8	7/6/2017	NULL	no
12345	N	9	4/6/2018	5/1/2018
					Select datediff(d,'7/6/2017','8/1/2018')=391




Loan	Hold indicator	holdsequence	Hold Start Date	Hold End Date	Overlap
12346	N	1	5/1/2015	5/15/2015
12346	N	2	5/5/2015	6/1/2017	Yes ( 5/5 <5/15)
12346	N	3	6/1/2017	8/1/2017	Yes (6/1=6/1) same day
12346	N	4	7/25/2017	1/1/2018	Yes 7/25<8/1
12346	N	5	12/31/2017	5/1/2018	Yes 12/31<1/1
12346	Y	6	4/25/2018	null	yes 4/25<5/1
					select datediff(d,'5/1/2015','2018-07-30')

here is the temp table:
Drop table #hold
GO
CREATE TABLE #Hold (
Loannumber varchar(10)
,Holdflag char(1)
,holdsequence int
,HoldStartdate varchar(10)
,HoldEnddate varchar(10)
)

insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',1,'2015-08-10','2015-09-25')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',2,'2016-09-16','2016-09-19')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',3,'2016-10-14','2016-12-02')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',4,'2016-10-14','2016-12-02')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',5,'2016-10-14','2016-12-02')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',6,'2016-10-14','2016-12-02')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',7,'2016-11-16','2017-03-23')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','Y',8,'2017-07-06',Null)
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',9,'2018-04-06','2018-05-01')

insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',1,'5/1/2015','5/15/2015')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',2,'5/5/2015','6/1/2017')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',3,'6/1/2017','8/1/2017')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',4,'7/25/2017','1/1/2018')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',5,'12/31/2017','5/1/2018')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','Y',6,'4/25/2018',NULL)

Thanks.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

saravanatn SSCarpal Tunnel Points: 4531 More actions · Answer 1

komal145 - Wednesday, August 1, 2018 10:28 AM

Hi,
I have a scenario where i need to calculate the number of days of hold on a loan .
Example is provided below. HoldSequence indicates the appearence of loan sequence, order by date . We need to consider the Holdflag/indicator i.e the loan is still on hold or not. if 'Yes' then the Hold end date is always Null , meaning loan is still on hold.

Overlap field is for my explanation , if there is overlap in loan holds meaning we have continuous holds on that particular loan so , we need to calculate the difference from first appearance of hold on loan till today (example : loan 12346). If there are gaps on hold meaning the loan is not in hold continuously then we need to calculate the number of days of hold when the hold started till today example :loan 12345. My question :how can we compare the date difference in enddate and satrtdate of next hold apperance?

Loannumber Hold indicator holdsequence HoldStartdate HoldEnddate Overlap

12345 N 1 8/10/2015 9/25/2015

12345 N 2 9/16/2016 9/19/2016 9/16<9/25 (yes)

12345 N 3 10/14/2016 12/2/2016 no

12345 N 4 10/14/2016 12/2/2016 yes (10/14<12/02)

12345 N 5 10/14/2016 12/2/2016 yes (10/14<12/02)

12345 N 6 10/14/2016 12/2/2016 yes (10/14<12/02)

12345 N 7 11/16/2016 3/23/2017 yes (11/16<12/02)

12345 Y 8 7/6/2017 NULL no

12346 N 9 4/6/2018 5/1/2018

Select datediff(d,'7/6/2017','8/1/2018')=391

Loan Hold indicator holdsequence Hold Start Date Hold End Date Overlap

12346 N 1 5/1/2015 5/15/2015

12346 N 2 5/5/2015 6/1/2017 Yes ( 5/5 <5/15)

12346 N 3 6/1/2017 8/1/2017 Yes (6/1=6/1) same day

12346 N 4 7/25/2017 1/1/2018 Yes 7/25<8/1

12346 N 5 12/31/2017 5/1/2018 Yes 12/31<1/1

12346 Y 6 4/25/2018 null yes 4/25<5/1

select datediff(d,'5/1/2015','2018-07-30')

here is the temp table:
Drop table #hold
GO
CREATE TABLE #Hold (
Loannumber varchar(10)
,Holdflag char(1)
,holdsequence int
,HoldStartdate varchar(10)
,HoldEnddate varchar(10)
)

insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',1,'2015-08-10','2015-09-25')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',2,'2016-09-16','2016-09-19')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',3,'2016-10-14','2016-12-02')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',4,'2016-10-14','2016-12-02')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',5,'2016-10-14','2016-12-02')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',6,'2016-10-14','2016-12-02')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',7,'2016-11-16','2017-03-23')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','Y',8,'2017-07-06',Null)
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12345','N',9,'2018-04-06','2018-05-01')

insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',1,'5/1/2015','5/15/2015')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',2,'5/5/2015','6/1/2017')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',3,'6/1/2017','8/1/2017')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',4,'7/25/2017','1/1/2018')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','N',5,'12/31/2017','5/1/2018')
insert into #Hold (loannumber , Holdflag,holdsequence, HoldStartdate , HoldEnddate)
Values ('12346','Y',6,'4/25/2018',NULL)

Thanks.

Clue: Lead and lag (analytical functions)
Reference:https://www.google.co.in/amp/s/blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/amp/

Saravanan

drew.allen SSC Guru Points: 76776 More actions · Answer 2

saravanatn - Wednesday, August 1, 2018 11:47 AM

Clue: Lead and lag (analytical functions)
Reference:https://www.google.co.in/amp/s/blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/amp/

LEAD/LAG will not work here, because they require a specific number of records to look forward/back and the number of records here is varied, because of the overlaps.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

komal145 SSCrazy Eights Points: 9892 More actions · Answer 3

drew.allen - Wednesday, August 1, 2018 12:12 PM

saravanatn - Wednesday, August 1, 2018 11:47 AM

Clue: Lead and lag (analytical functions)
Reference:https://www.google.co.in/amp/s/blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/amp/

LEAD/LAG will not work here, because they require a specific number of records to look forward/back and the number of records here is varied, because of the overlaps.

Drew

yes, lead and lag will not work. trying to check if any work around 🙁

drew.allen SSC Guru Points: 76776 More actions · Answer 4

Here is a solution that works.

WITH C1 AS ( SELECT *, MAX(ISNULL(HoldEndDate, '9999-12-31')) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrvEnd FROM #Hold h ) , C2 AS ( SELECT *, SUM(IsStart) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS UNBOUNDED PRECEDING) AS grp FROM C1 CROSS APPLY ( VALUES( CASE WHEN HoldStartDate <= PrvEnd THEN NULL ELSE 1 END) ) AS A( isStart ) )

SELECT Loannumber, grp, MIN(HoldStartdate) AS HoldStartDate, DATEDIFF(DAY, MIN(HoldStartDate), GETDATE()) AS DaysOnHold
FROM C2
GROUP BY Loannumber, grp
HAVING MAX(ISNULL(HoldEnddate, '9999-12-31')) = '9999-12-31'
ORDER BY Loannumber

There are several issues with your data.
1) You're using the WRONG DATATYPE. Date/Time data should never be stored as a string. NEVER.
2) You're using NULL to represent an unknown future value. You should never use NULLs to represent the endpoints of an interval. NULL handling in intervals is more complex than NULL handling in single-value data types. It's much better to just pick one number to represent an unknown beginning value and a separate number to represent an unknown end value. For dates, the typical beginning value is 1900-01-01 and the typical end value is 9000-01-01, 9999-01-01, or 9999-12-31.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183686 More actions · Answer 5

saravanatn - Wednesday, August 1, 2018 11:47 AM

Clue: Lead and lag (analytical functions)
Reference:https://www.google.co.in/amp/s/blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/amp/

There, FTFY

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 6

It isn't clear on what you are trying to calculate - what are the expected results and how does the overlap figure into the results? It seems that you only have one row that has the HoldFlag set to Y for each loan.

I am guessing this is what you are looking for

Declare @hold Table ( Loannumber varchar(10) , Holdflag char(1) , Holdsequence int , HoldStartdate date , HoldEnddate date );



 Insert Into @Hold (Loannumber, HoldFlag, HoldSequence, HoldStartdate, HoldEnddate)
 Values ('12345', 'N', 1, '2015-08-10', '2015-09-25')
  , ('12345', 'N', 2, '2016-09-16', '2016-09-19')
  , ('12345', 'N', 3, '2016-10-14', '2016-12-02')
  , ('12345', 'N', 4, '2016-10-14', '2016-12-02')
  , ('12345', 'N', 5, '2016-10-14', '2016-12-02')
  , ('12345', 'N', 6, '2016-10-14', '2016-12-02')
  , ('12345', 'N', 7, '2016-11-16', '2017-03-23')
  , ('12345', 'Y', 8, '2017-07-06', Null)
  , ('12345', 'N', 9, '2018-04-06', '2018-05-01')
  , ('12346', 'N', 1, '2015-05-01', '2015-05-15')
  , ('12346', 'N', 2, '2015-05-05', '2017-06-01')
  , ('12346', 'N', 3, '2017-06-01', '2017-08-01')
  , ('12346', 'N', 4, '2017-07-25', '2018-01-01')
  , ('12346', 'N', 5, '2017-12-31', '2018-05-01')
  , ('12346', 'Y', 6, '2018-04-25', Null);

 Select * From @hold Order By Loannumber, Holdsequence;

Declare @minDate date = (Select min(h.HoldStartdate) From @hold h)
  , @maxDate date = (Select max(coalesce(h.HoldEnddate, getdate())) From @hold h);

With dateDim As ( Select StartDate = dateadd(day, -d.Number, @maxDate) From (Select row_number() over(Order By ac.[object_id]) - 1 From sys.all_columns ac) As d(Number) Where d.Number <= datediff(day, @minDate, @maxDate) ) , groupDates As ( Select * , GroupDate = dateadd(day, -dense_rank() over(Partition By h.Loannumber Order By d.StartDate) + 1, d.StartDate) From dateDim d Inner Join @hold h On d.StartDate Between h.HoldStartdate And coalesce(h.HoldEnddate, @maxDate) ) Select LoanNumber , MinHoldDate = min(StartDate) , MaxHoldDate = max(StartDate) , DaysHold = datediff(day, min(StartDate), max(StartDate)) From groupDates Group By LoanNumber , GroupDate

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

saravanatn SSCarpal Tunnel Points: 4531 More actions · Answer 7

drew.allen - Wednesday, August 1, 2018 12:12 PM

saravanatn - Wednesday, August 1, 2018 11:47 AM

Clue: Lead and lag (analytical functions)
Reference:https://www.google.co.in/amp/s/blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/amp/

LEAD/LAG will not work here, because they require a specific number of records to look forward/back and the number of records here is varied, because of the overlaps.

Drew

Sorry for pointing towards wrong direction

Saravanan

komal145 SSCrazy Eights Points: 9892 More actions · Answer 8

drew.allen - Wednesday, August 1, 2018 1:03 PM

Here is a solution that works.

WITH C1 AS ( SELECT *, MAX(ISNULL(HoldEndDate, '9999-12-31')) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrvEnd FROM #Hold h ) , C2 AS ( SELECT *, SUM(IsStart) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS UNBOUNDED PRECEDING) AS grp FROM C1 CROSS APPLY ( VALUES( CASE WHEN HoldStartDate <= PrvEnd THEN NULL ELSE 1 END) ) AS A( isStart ) )

SELECT Loannumber, grp, MIN(HoldStartdate) AS HoldStartDate, DATEDIFF(DAY, MIN(HoldStartDate), GETDATE()) AS DaysOnHold
FROM C2
GROUP BY Loannumber, grp
HAVING MAX(ISNULL(HoldEnddate, '9999-12-31')) = '9999-12-31'
ORDER BY Loannumber

There are several issues with your data.
1) You're using the WRONG DATATYPE. Date/Time data should never be stored as a string. NEVER.
2) You're using NULL to represent an unknown future value. You should never use NULLs to represent the endpoints of an interval. NULL handling in intervals is more complex than NULL handling in single-value data types. It's much better to just pick one number to represent an unknown beginning value and a separate number to represent an unknown end value. For dates, the typical beginning value is 1900-01-01 and the typical end value is 9000-01-01, 9999-01-01, or 9999-12-31.

Drew

Looks like you sent me code in teradata. The 'ROWS BETWEEN UNBOUNDED PRECEDING' do not work in tsql 🙁

saravanatn SSCarpal Tunnel Points: 4531 More actions · Answer 9

komal145 - Monday, August 6, 2018 12:43 PM

drew.allen - Wednesday, August 1, 2018 1:03 PM

Here is a solution that works.

WITH C1 AS ( SELECT *, MAX(ISNULL(HoldEndDate, '9999-12-31')) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrvEnd FROM #Hold h ) , C2 AS ( SELECT *, SUM(IsStart) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS UNBOUNDED PRECEDING) AS grp FROM C1 CROSS APPLY ( VALUES( CASE WHEN HoldStartDate <= PrvEnd THEN NULL ELSE 1 END) ) AS A( isStart ) )

SELECT Loannumber, grp, MIN(HoldStartdate) AS HoldStartDate, DATEDIFF(DAY, MIN(HoldStartDate), GETDATE()) AS DaysOnHold
FROM C2
GROUP BY Loannumber, grp
HAVING MAX(ISNULL(HoldEnddate, '9999-12-31')) = '9999-12-31'
ORDER BY Loannumber

There are several issues with your data.
1) You're using the WRONG DATATYPE. Date/Time data should never be stored as a string. NEVER.
2) You're using NULL to represent an unknown future value. You should never use NULLs to represent the endpoints of an interval. NULL handling in intervals is more complex than NULL handling in single-value data types. It's much better to just pick one number to represent an unknown beginning value and a separate number to represent an unknown end value. For dates, the typical beginning value is 1900-01-01 and the typical end value is 9000-01-01, 9999-01-01, or 9999-12-31.

Drew

Looks like you sent me code in teradata. The 'ROWS BETWEEN UNBOUNDED PRECEDING' do not work in tsql 🙁

Is it not working.? Can you kindly tell us what version sql server are you using?
Msdn manual says ROWS BETWEEN UNBOUNDED PRECEDING should works for sql server 2012 or above, :
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

I think ROWS BETWEEN UNBOUNDED PRECEDING also works in oracle and postgre sql.

Saravanan

drew.allen SSC Guru Points: 76776 More actions · Answer 10

komal145 - Monday, August 6, 2018 12:43 PM

drew.allen - Wednesday, August 1, 2018 1:03 PM

Here is a solution that works.

WITH C1 AS ( SELECT *, MAX(ISNULL(HoldEndDate, '9999-12-31')) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrvEnd FROM #Hold h ) , C2 AS ( SELECT *, SUM(IsStart) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS UNBOUNDED PRECEDING) AS grp FROM C1 CROSS APPLY ( VALUES( CASE WHEN HoldStartDate <= PrvEnd THEN NULL ELSE 1 END) ) AS A( isStart ) )

SELECT Loannumber, grp, MIN(HoldStartdate) AS HoldStartDate, DATEDIFF(DAY, MIN(HoldStartDate), GETDATE()) AS DaysOnHold
FROM C2
GROUP BY Loannumber, grp
HAVING MAX(ISNULL(HoldEnddate, '9999-12-31')) = '9999-12-31'
ORDER BY Loannumber

There are several issues with your data.
1) You're using the WRONG DATATYPE. Date/Time data should never be stored as a string. NEVER.
2) You're using NULL to represent an unknown future value. You should never use NULLs to represent the endpoints of an interval. NULL handling in intervals is more complex than NULL handling in single-value data types. It's much better to just pick one number to represent an unknown beginning value and a separate number to represent an unknown end value. For dates, the typical beginning value is 1900-01-01 and the typical end value is 9000-01-01, 9999-01-01, or 9999-12-31.

Drew

Looks like you sent me code in teradata. The 'ROWS BETWEEN UNBOUNDED PRECEDING' do not work in tsql 🙁

ROWS BETWEEN UNBOUNDED PRECEDING was added to T-SQL in SQL 2012. Since you posted in a SQL 2017 forum, you are implying that you are working with SQL 2017 and are looking for solutions that work in SQL 2017. What version of SQL are you actually using?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

komal145 SSCrazy Eights Points: 9892 More actions · Answer 11

saravanatn - Monday, August 6, 2018 1:08 PM

komal145 - Monday, August 6, 2018 12:43 PM

drew.allen - Wednesday, August 1, 2018 1:03 PM

Here is a solution that works.

WITH C1 AS ( SELECT *, MAX(ISNULL(HoldEndDate, '9999-12-31')) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrvEnd FROM #Hold h ) , C2 AS ( SELECT *, SUM(IsStart) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS UNBOUNDED PRECEDING) AS grp FROM C1 CROSS APPLY ( VALUES( CASE WHEN HoldStartDate <= PrvEnd THEN NULL ELSE 1 END) ) AS A( isStart ) )

SELECT Loannumber, grp, MIN(HoldStartdate) AS HoldStartDate, DATEDIFF(DAY, MIN(HoldStartDate), GETDATE()) AS DaysOnHold
FROM C2
GROUP BY Loannumber, grp
HAVING MAX(ISNULL(HoldEnddate, '9999-12-31')) = '9999-12-31'
ORDER BY Loannumber

There are several issues with your data.
1) You're using the WRONG DATATYPE. Date/Time data should never be stored as a string. NEVER.
2) You're using NULL to represent an unknown future value. You should never use NULLs to represent the endpoints of an interval. NULL handling in intervals is more complex than NULL handling in single-value data types. It's much better to just pick one number to represent an unknown beginning value and a separate number to represent an unknown end value. For dates, the typical beginning value is 1900-01-01 and the typical end value is 9000-01-01, 9999-01-01, or 9999-12-31.

Drew

Looks like you sent me code in teradata. The 'ROWS BETWEEN UNBOUNDED PRECEDING' do not work in tsql 🙁

Is it not working.? Can you kindly tell us what version sql server are you using?
Msdn manual says ROWS BETWEEN UNBOUNDED PRECEDING should works for sql server 2012 or above, :
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

I think ROWS BETWEEN UNBOUNDED PRECEDING also works in oracle and postgre sql.

I work in In Microsoft SQL server. The above functions do not work 🙁

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 12

komal145 - Monday, August 6, 2018 1:41 PM

saravanatn - Monday, August 6, 2018 1:08 PM

komal145 - Monday, August 6, 2018 12:43 PM

drew.allen - Wednesday, August 1, 2018 1:03 PM

Here is a solution that works.

WITH C1 AS ( SELECT *, MAX(ISNULL(HoldEndDate, '9999-12-31')) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrvEnd FROM #Hold h ) , C2 AS ( SELECT *, SUM(IsStart) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS UNBOUNDED PRECEDING) AS grp FROM C1 CROSS APPLY ( VALUES( CASE WHEN HoldStartDate <= PrvEnd THEN NULL ELSE 1 END) ) AS A( isStart ) )

SELECT Loannumber, grp, MIN(HoldStartdate) AS HoldStartDate, DATEDIFF(DAY, MIN(HoldStartDate), GETDATE()) AS DaysOnHold
FROM C2
GROUP BY Loannumber, grp
HAVING MAX(ISNULL(HoldEnddate, '9999-12-31')) = '9999-12-31'
ORDER BY Loannumber

There are several issues with your data.
1) You're using the WRONG DATATYPE. Date/Time data should never be stored as a string. NEVER.
2) You're using NULL to represent an unknown future value. You should never use NULLs to represent the endpoints of an interval. NULL handling in intervals is more complex than NULL handling in single-value data types. It's much better to just pick one number to represent an unknown beginning value and a separate number to represent an unknown end value. For dates, the typical beginning value is 1900-01-01 and the typical end value is 9000-01-01, 9999-01-01, or 9999-12-31.

Drew

Looks like you sent me code in teradata. The 'ROWS BETWEEN UNBOUNDED PRECEDING' do not work in tsql 🙁

Is it not working.? Can you kindly tell us what version sql server are you using?
Msdn manual says ROWS BETWEEN UNBOUNDED PRECEDING should works for sql server 2012 or above, :
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

I think ROWS BETWEEN UNBOUNDED PRECEDING also works in oracle and postgre sql.

I work in In Microsoft SQL server. The above functions do not work 🙁

What version of MS SQL Server are you running. ROWS BETWEEN UNBOUNDED PRECEDING was added in SQL Server 2012. You posted in a SQL Server 2017 forum.

andycadley SSCertifiable Points: 5295 More actions · Answer 13

komal145 - Monday, August 6, 2018 1:41 PM

I work in In Microsoft SQL server. The above functions do not work 🙁

Which version of Microsoft SQL Server? The ROW/RANGE clause for Window Functions is available in everything from 2012 onwards. If you're on an older version, it would help to know which one so people can suggest an alternate approach.

komal145 SSCrazy Eights Points: 9892 More actions · Answer 14

andycadley - Monday, August 6, 2018 1:56 PM

komal145 - Monday, August 6, 2018 1:41 PM

I work in In Microsoft SQL server. The above functions do not work 🙁

Which version of Microsoft SQL Server? The ROW/RANGE clause for Window Functions is available in everything from 2012 onwards. If you're on an older version, it would help to know which one so people can suggest an alternate approach.

currently my company is using 2008 r2 version 🙁