matthew-n

{
  function extractOptional(optional, index) {
    return optional ? optional[index] : null;
  }
  
  function optionalList(value) {
    return value !== null ? value : [];
  }
}

matthew-n

SELECT
	name
	-- added Days Back to use a fitler aginst the user input
	,DATEDIFF(DAY,CONVERT(DATE, calc.run_date),GETDATE()) AS DaysBack
	,CONVERT( DATETIME, calc.run_date + SPACE(1) + calc.run_time) AS start_time
	-- using seconds here as I had a problem with times over 24 hrs
	,DATEADD(SECOND, jh.run_duration, CONVERT( DATETIME, calc.run_date + SPACE(1) + calc.run_time)) AS end_time
	,run_status
	,instance_id
FROM msdb.dbo.sysjobhistory jh

matthew-n

/*
 Description: A T-SQL implementation of Sierpinski's Triangles, just to see if I could do it.
 Requires: SQL Server 2012
 Inspired by:  Alastair Aitchison SQLCLR implementation http://alastaira.wordpress.com/2012/03/06/drawing-fractals-with-sql-server-spatial/ 
 Author: Matthew Naul
*/
DECLARE 
 @itrations INT = 12,
 @seed geometry = geometry::STGeomFromText ('LINESTRING (9 12, 0 0, 18 0, 9 12)',0);
 

matthew-n

/*
 Descriptions: reference for all the xml shapping I've learned
 Author: Mattehw Naul

 Explanation: 
 for each person do a correlated query for phone numbers,
 format them mixing attributes and element values,
 serialize to xml with "TYPE" so that we return on column of type XML root tag (phonelist)
 name the cross apply result,
 when phonelist(col) is null add nil phonelist node

matthew-n

-- sparce matrix of (baz, biz, buz)
CREATE TABLE myLegacyBitFlagTbl(

	baz char(2) NOT NULL,
	biz char(2) NOT NULL,
	
	-- bit flag fields encode buz(int) values
	colA INT NOT NULL, --LSB in big-endian
	colB INT NOT NULL,

matthew-n

  /*

   -- EXAMPLE --

   SELECT
   * 
   FROM grid(array(SELECT
     (
       lat,

matthew-n

WITH fedex_rates_ip
("fid","Type","Weight","ZoneA","ZoneB","ZoneC","ZoneD","ZoneE","ZoneF","ZoneG",
 "ZoneH","ZoneI","ZoneJ","ZoneK","ZoneL","ZoneM","ZoneN","ZoneO","ZonePuertoRico")
as(
  select 
    *
  from (
    values
      (2,'IntlPriority',0,40.25,41.5,43,54.75,116.5,52,51.5,61.25,49.5,63.5,62.5,82,119.25,61,63.25,36.65),
      (3,'IntlPriority',-1,66.25,67.75,62.25,74.25,132,68,68.25,85.75,66.25,84.5,82.25,99.5,136.5,79.75,85.5,null),

matthew-n

End of §41.5.3 - INTO key word

When selecting into a variable it will emit under-select, and over selection if you use strict, as exception types.

SELECT _col_ into strict _v_something_
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE ...
  WHEN TOO_MANY_ROWS THEN
    RAISE ...

matthew-n

CREATE OR REPLACE FUNCTION dummy_function( col_pk int, col_b my_domain, col_fk text)
AS
$BODY$
declare
  v_msg text;
  v_constraint text;
BEGIN

  INSERT INTO some_table VALUES($1,$2,$3);
  RETURN TRUE;

matthew-n

/**
*   return: a table of all valid polygons found in the featue collection along with their properties
*   error: 
*      - will not stop execution geometry will be set to empty (should be null?)
*      - for all indexes with an error an object with the key `error` and an array 
*         of deatails will be appended
*      ```
*          {"error": [ {"reason":'cross over', "locaiton":'<WKT>'}
*                  ,{"reason:": "invalid type"}]}
*      ```