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.
August 1, 2018 at 11:47 am
komal145 - Wednesday, August 1, 2018 10:28 AMHi,
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
August 1, 2018 at 12:12 pm
saravanatn - Wednesday, August 1, 2018 11:47 AMClue: 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
August 1, 2018 at 12:56 pm
drew.allen - Wednesday, August 1, 2018 12:12 PMsaravanatn - Wednesday, August 1, 2018 11:47 AMClue: 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 🙁
August 1, 2018 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 1, 2018 at 1:44 pm
saravanatn - Wednesday, August 1, 2018 11:47 AMClue: 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
August 1, 2018 at 3:41 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 2, 2018 at 1:27 am
drew.allen - Wednesday, August 1, 2018 12:12 PMsaravanatn - Wednesday, August 1, 2018 11:47 AMClue: 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
August 6, 2018 at 12:43 pm
drew.allen - Wednesday, August 1, 2018 1:03 PMHere 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 LoannumberThere 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 🙁
August 6, 2018 at 1:08 pm
komal145 - Monday, August 6, 2018 12:43 PMdrew.allen - Wednesday, August 1, 2018 1:03 PMHere 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 LoannumberThere 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
August 6, 2018 at 1:18 pm
komal145 - Monday, August 6, 2018 12:43 PMdrew.allen - Wednesday, August 1, 2018 1:03 PMHere 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 LoannumberThere 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
August 6, 2018 at 1:41 pm
saravanatn - Monday, August 6, 2018 1:08 PMkomal145 - Monday, August 6, 2018 12:43 PMdrew.allen - Wednesday, August 1, 2018 1:03 PMHere 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 LoannumberThere 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-2017I 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 🙁
August 6, 2018 at 1:55 pm
komal145 - Monday, August 6, 2018 1:41 PMsaravanatn - Monday, August 6, 2018 1:08 PMkomal145 - Monday, August 6, 2018 12:43 PMdrew.allen - Wednesday, August 1, 2018 1:03 PMHere 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 LoannumberThere 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-2017I 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.
August 6, 2018 at 1:56 pm
komal145 - Monday, August 6, 2018 1:41 PMI 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.
August 6, 2018 at 2:41 pm
andycadley - Monday, August 6, 2018 1:56 PMkomal145 - Monday, August 6, 2018 1:41 PMI 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 🙁
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply