SQL help with date comaprisons

• komal145

  SSCrazy Eights

  Points: 9915

  More actions

  August 6, 2018 at 2:42 pm

  #2000509

  Lynn Pettis - Monday, August 6, 2018 1:55 PM

  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.

  Ahh ..i just realised . Sorry , my bad.

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  August 6, 2018 at 3:48 pm

  #2000516

  I think that'll do it:
  
  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
   Select * From #Hold H1
   Outer Apply
   (
   Select Max(HoldEndDate) As PrvEnd From #Hold H2 Where H1.Loannumber = H2.Loannumber
    And H2.holdsequence < H1.holdsequence
   ) X
  )
  , C2 AS
  (
   --SELECT *, SUM(IsStart) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS UNBOUNDED PRECEDING) AS grp
   --FROM C1
  Select * From C1
  Outer Apply
  (
  Select Sum(CASE WHEN HoldStartDate <= PrvEnd THEN null ELSE 1 END) As grp From C1 As C1X Where C1.Loannumber = C1x.Loannumber
    And C1X.HoldSequence <= C1.holdsequence
  ) X
  )
  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

• komal145

  SSCrazy Eights

  Points: 9915

  More actions

  August 8, 2018 at 12:13 pm

  #2000884

  andycadley - Monday, August 6, 2018 3:48 PM

  I think that'll do it:
  
  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
   Select * From #Hold H1
   Outer Apply
   (
   Select Max(HoldEndDate) As PrvEnd From #Hold H2 Where H1.Loannumber = H2.Loannumber
    And H2.holdsequence < H1.holdsequence
   ) X
  )
  , C2 AS
  (
   --SELECT *, SUM(IsStart) OVER(PARTITION BY LoanNumber ORDER BY HoldStartDate, HoldEndDate, HoldSequence ROWS UNBOUNDED PRECEDING) AS grp
   --FROM C1
  Select * From C1
  Outer Apply
  (
  Select Sum(CASE WHEN HoldStartDate <= PrvEnd THEN null ELSE 1 END) As grp From C1 As C1X Where C1.Loannumber = C1x.Loannumber
    And C1X.HoldSequence <= C1.holdsequence
  ) X
  )
  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

  Thank you. It's working but need more testing.I will let you know.