DZone Snippets: Jokeyxero's Code Snippets

Pull Date-Effective Records that Overlap Each Other

jokeyxero (xero) — Wed, 04 Jun 2008 19:54:15 GMT

Basic framework and examples of how to pull records that overlap each other from a temporal (aka date-effective) database.



-- PK is a placeholder for the Primary Key column(s)

-- SoftPK is a placeholder for columns that need to match for overlap, but are not the PK

-- Sometimes SoftPK is the PK, in which case you remove the <> PK line and match the SoftPK.

-- The idea is to check for overlap on all rows except for yourself, since that always overlaps.

--   This snippet assumes the EndDate column has a NULL to represent infinity.

--   There are alternate syntaxes for this (compare or EndDate is null) but I've found

--   the Coalesce to provide the best query plan usually. The constant itself is debatable and

--   implementation dependent.

select outer.*

from DateEffectiveTable as outer

where exists (

  select 1

  from DateEffectiveTable as inner

  where inner.PK <> outer.PK

    -- and inner.SoftPK = outer.SoftPK

    and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')

    and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate

)





-- Example Usage

-- Assume we have a table containing the address history for a person

-- A person may have multiple addresses at once (own multiple houses).

-- A person may move away then back (same address, different time ranges).

-- ie, multiple simultaneous unique records are possible, but not

-- multiple simultaneous non-unique records (no self-overlap).

-- Yes, it is usually an odd design to have an AddressID, but go with it.

select outer.*

from AddressHistory as outer

where exists (

  select 1

  from AddressHistory as inner

  where outer.AddressID <> inner.AddressID

    and outer.PersonID = inner.PersonID

    and outer.Street = inner.Street

    and outer.Zip = inner.Zip

    and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')

    and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate





-- Same example, but assume PK is a more "appropriate" one of

-- { PersonID, Street, Zip, StartDate }

select outer.*

from AddressHistory as outer

where exists (

  select 1

  from AddressHistory as inner

  where 

    not (

     outer.PersonID = inner.PersonID

     and outer.Street = inner.Street

     and outer.Zip = inner.Zip

     and outer.StartDate = inner.StartDate

    )

    and outer.PersonID = inner.PersonID

    and outer.Street = inner.Street

    and outer.Zip = inner.Zip

    and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')

    and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate

Pull Date-Effective Records that Intersect A Given Range

jokeyxero (xero) — Fri, 16 May 2008 14:50:51 GMT

Basics of how to pull records that intersect a date range from a temporal (aka date-effective) database.



-- @rangeStart is the start of the date range of interest

-- @rangeEnd is the end of the date range of interest

-- If you use null to signify infinity, then use the commented line instead of the one above it.

select *

from DateEffectiveTable

where StartDate < @rangeEnd

  and EndDate >= @rangeStart

  -- and coalesce(endDate, '9999-12-31') >= @rangeStart

Calculate Last Day of Last Month

jokeyxero (xero) — Fri, 01 Feb 2008 22:51:12 GMT

VB/VBA/VB.NET one-liner to calculate the end of last month. Useful for SSRS/RDL Expressions and Excel/Office Formulas. Note that it does not use string parsing, which can cause localization problems.



DateAdd("d", -1.0 * DatePart("d", Today), Today)

Calculate First Day of Current Month

jokeyxero (xero) — Fri, 01 Feb 2008 22:48:45 GMT

VB/VBA/VB.NET one-liner to calculate the start of the current month. Useful for SSRS/RDL Expressions and Excel/Office Formulas. Note that it does not use string parsing, which can cause localization problems.



DateAdd("D", -1.0 * DatePart("D", Today) + 1, Today)

Calculate First Day of Last Month

jokeyxero (xero) — Fri, 01 Feb 2008 22:47:11 GMT

VB/VBA/VB.NET one-liner to calculate the start of the previous month. Useful for SSRS/RDL Expressions and Excel/Office Formulas. Note that it does not use string parsing, which can cause localization problems.



DateAdd("D", -1.0 * DatePart("D", Today) + 1, DateAdd("m", -1, Today))

Read Multidimensional C char Array of Unknown Length

jokeyxero (xero) — Thu, 17 May 2007 00:56:20 GMT

Reading a multidimensional C char array (aka C String array) containing char 0 and terminated with literal zero without knowing length.



char *a[] = { "11", "23", "", "44", "11", "", "16", "36", "", "51", "71", "", "46", "26", "", "14", "68", 0};

int sentinel = 0;

int i = 0;

while ( *(a + i++) != sentinel ) { /* counting away */ }

int aLength = i; // 18, length

int aUbound = i - 1; // 17, index of last element

int aMaxIdx = i - 2; // 16, index of last element where a[i] doesn't cause seg fault

// You now have the lengths so read like normal

PRNG In SQL Select

jokeyxero (xero) — Thu, 17 May 2007 00:39:32 GMT

Simple example of a PRNG (pseudo-random number generator) written into a SQL statement

Example is in T-SQL, but it ports well

Actual application should use either a better random algorithm, or the output be used with randomized seeds. This is definitely not cryptographically secure. It's very handy if you need a simple random number with your recordset though.



-- Setup some vars we'll need

DECLARE @prng TABLE (seed BIGINT, rnum nchar(10))

DECLARE @seeds TABLE (seed BIGINT)

DECLARE @seed BIGINT

DECLARE @C1 BIGINT, @C2 BIGINT, @C3 BIGINT

SET @seed = 0

SET @C1 = 1664525

SET @C2 = 4294967296

SET @C3 = 1013904223



-- Create a seed table so we can have some data to use

WHILE @seed < 10

BEGIN

    INSERT INTO @seeds (seed) VALUES (@seed)

    SET @seed = @seed + 1

END



-- Create our PRNG (inserts into table for illustrative purposes)

-- prng(seed) ::= ((((C1 * seed) % C2) + C3) % C2) / C2

-- Then convert prng(seed) into a string

-- of 10 chars, 8 of which are decimal places

INSERT INTO @prng

SELECT

    seed,

    REPLACE(

        STR(

            ( CAST((((@C1*seed)%@C2)+@C3)%@C2 AS FLOAT) )

            / ( CAST(@C2 AS FLOAT)),

            10, 8

        ),

        ' ', '0') AS rnum

FROM @seeds



-- Let's take a look at what we created

SELECT * FROM @prng

Trim Template for XSLT

jokeyxero (xero) — Thu, 17 May 2007 00:35:37 GMT

Common Trim function for XSLT (as a template)

Normalize Space and Keep Line Breaks in XSTL

jokeyxero (xero) — Thu, 17 May 2007 00:30:40 GMT

XSLT function pattern to normalize space but keep line breaks, without using a template.

It replaces both line break characters with non-whitespace placeholders, normalizes, then puts the line breaks back in, nothing fancy. Note that it will handle all four types of line breaks (LF, CR, CRLF, LFCR) even though only LF is XML standard. In our case, the placeholders are characters 160 and 173, the hard space and soft hyphen respectively. They're used, but generally only by programmers and Office documents.

For reference, pretend "FieldValue/." is the path to a foo element.



translate(

 normalize-space(

  translate(FieldValue/., '
', ' ')

 ), ' ', '
'

)