Back to OIM Explorer

dbo.QBM_FTDateTimeGaps

Inline Table FunctionSQL_INLINE_TABLE_VALUED_FUNCTIONSandbox DB

Inline Table Function.

Source: sandbox-db sys.sql_modules

Source size: 893 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FGIDate source text reference
  • references source dbo.QBM_FGIDateTimeSpanOverlap source text reference

References

Referenced By

Complete Source

SQL40 lines
1CREATE FUNCTION dbo.QBM_FTDateTimeGaps(2  @ExistingTimeSpans QBM_YDateTimePair READONLY3) RETURNS TABLE4RETURN(5  WITH ist6AS7(8SELECT9  e.FromDate, e.ToDate10FROM @ExistingTimeSpans e11UNION all12SELECT13  '1899-12-31', getutcdate())14SELECT15  l.FromDate AS GapStart,16  l.ToDate AS GapEnd17FROM ist i CROSS18JOIN(19SELECT20  x.FromDate, x.ToDate21FROM(22SELECT23  getutcdate() AS FromDate, dateadd(mi, -1, MIN(i.FromDate)) AS ToDate24FROM ist i25UNION all26SELECT27  dateadd(ms, 10, i1.ToDate), min(dateadd(mi, -1, i2.FromDate))28FROM ist i129JOIN ist i230  ON dateadd(mi, -1, i2.FromDate) > dateadd(ms, 10, i1.ToDate)31GROUP BY dateadd(ms, 10, i1.ToDate)32UNION all33SELECT34  dateadd(mi, 1, Max(ToDate)), '2200-01-01'35FROM ist) AS x36WHERE37  x.FromDate <= ToDate) AS l38GROUP BY l.FromDate,39l.ToDate40HAVING SUM(convert(int, dbo.QBM_FGIDateTimeSpanOverlap(i.FromDate, i.ToDate, l.FromDate, l.ToDate, 0))) = 0)
Open raw exported source
SQL ยท Raw7 lines
1create function dbo.QBM_FTDateTimeGaps( @ExistingTimeSpans QBM_YDateTimePair readonly ) returns table  return ( with ist as (select e.FromDate , e.ToDate2 from @ExistingTimeSpans e union all   select '1899-12-31', getutcdate() )  select l.FromDate as GapStart, l.ToDate as GapEnd from ist i cross join ( select3 x.FromDate, x.ToDate from (  select getutcdate() as FromDate, dateadd(mi, -1, MIN(i.FromDate)) as ToDate from ist i  union all  select dateadd(ms, 10,4 i1.ToDate), min(dateadd(mi, -1, i2.FromDate)) from ist i1 join ist i2 on dateadd(mi, -1, i2.FromDate) > dateadd(ms, 10, i1.ToDate) group by dateadd(ms5, 10, i1.ToDate)   union all select dateadd(mi, 1, Max(ToDate)), '2200-01-01' from ist ) as x where x.FromDate <= ToDate )  as l group by l.FromDate, l.ToDate6 having SUM(convert(int, dbo.QBM_FGIDateTimeSpanOverlap(i.FromDate, i.ToDate, l.FromDate, l.ToDate, 0))) = 0  ) 7