Contact Us

Use the form on the right to contact us.

You can edit the text in this area, and change where the contact form on the right submits to, by entering edit mode using the modes on the bottom right. 


Oak Ridge, TN, 37830
United States

Sql-Sangria

Filtering by Category: Methods

Count Decimal Places

Wade Cantley

The Decimal Splash

  • 1 1/2 oz. TY KU Liqueur
  • 1 oz. apple juice
  • Splash of sugar-free cranberry juice

I needed a way to see if a Latitude and Longitude value was long enough to be precise.  To be precise it needs to be at least 6 decimal places long so I needed to count the decimals.  

Here is what I found.

select latitude, longitude
from TableWithGeoLocations
where 

LEN(CAST(REVERSE(SUBSTRING(STR(latitude, 13, 11), CHARINDEX('.', STR(latitude, 13, 11)) + 1, 20)) AS decimal)) <= 6
or 
LEN(CAST(REVERSE(SUBSTRING(STR(longitude, 13, 11), CHARINDEX('.', STR(longitude, 13, 11)) + 1, 20)) AS decimal)) <= 6

This worked great to filter out the records that needed better geolocation data.

SQL Break Points

Wade Cantley

Break Point Punch

3 ounces of pineapple juice
2 ounces of orange juice
1 ounce dark rum, plus 1/2 ounce to splash on top
1 ounce coconut rum
splash of grenadine
lime slice for garnish

So, maybe you have some code you want to run, and then code you want to skip over for later, and THEN code you want to run.  This can be easily chopped up using the below code.

-- Top of the test code
/**/set noexec off

-- (Stuff here will run )
-- This is the code that you want to run and check.

/**/set noexec on

-- (stuff here gets skipped )
-- This is the code that you have yet to get to


/**/set noexec off

-- (stuff here will run)
-- This is good for final temp table drops.

Re-Order on Order Field

Wade Cantley

The Orange Re-Order

  • 1 c. tequila
  • 1 c. freshly squeezed orange juice
  • ½ c. Licor 43
  • ¼ c. freshly squeezed lime juice
  • splash of pure vanilla extract
  • simple syrup or agave nectar, optional (to taste)

We will either put it on the end (append) which is indicated by an order of 0, or we will put it into an existing order, which will require incrementing everything after the given number.  And then reordering to get rid of holes.

SET @Order = 3

	-- If order number = 0, Generate actual order number to put it at the bottom
  IF @Order = 0
    BEGIN
      SELECT TOP 1 @Order = ATMPTC.ContentOrder + 1
      FROM cms.AscTblMountPointTblContent ATMPTC
      WHERE ATMPTC.MountPointId = @MountPointId
        -- some unique id that groups together items for ordering
      ORDER BY ATMPTC.ContentOrder DESC

      -- It is possible that this is the first record,
      -- in which case, the return would be 0.
      -- If thats the case, then set the number to 1

      IF @Order = 0
      BEGIN
        SET @order = 1
      END
  END


	-- If order number is not 0 make room for new ordernumber by incrementing everything after the order number
  ELSE
    BEGIN
      UPDATE cms.AscTblMountPointTblContent
      SET ContentOrder = ContentOrder + 1
      WHERE MountPointId = @MountPointId
      -- some unique id that groups together items for ordering
      AND contentOrder >= @order
    END

  -- Re-Order - Editting a ContentOrder number can cause a number gap so this cleans up a number gap.
SELECT ROW_NUMBER() OVER (order BY ATMPTC.ContentOrder ASC) AS NewRowId
, ATMPTC.ContentOrder
, ATMPTC.ContentId
FROM  CMS.AscTblMountPointTblContent ATMPTC
WHERE ATMPTC.MountPointId = @mountPointId
ORDER BY ATMPTC.ContentOrder ASC

Re-Order Method

Wade Cantley

The Re-Ordered Russian

3/4 ounce green Creme de Menthe
3/4 ounce Creme de Cacao
3 ounces milk
Swirl the martini glass with chocolate syrup
Shake over ice, strain into a martini glass
Garnish with chopped Andes mints or chocolate shavings


The goal is to re-order a list based on the new position and the old position.

-- incoming vars
DECLARE @InventoryPackageFeatureId varchar(36)
, @OrderNumber INT
, @InventoryPackageId VARCHAR(36)
, @oldOrderNumber INT

SET @InventoryPackageFeatureId = '4029D0E0-8188-4F2F-A9D5-1446BF0FDC3C'
SET @OrderNumber = 2


SELECT @oldOrderNumber = InventoryPackageFeatureOrder
FROM InventoryPackageFeatures
WHERE InventoryPackageFeatureId = @InventoryPackageFeatureId

-- Depending on the direction a record will go, the orderNumber may need to increment for the rest
-- of the processes to work right.
IF @oldOrderNumber < @OrderNumber
  BEGIN
    SET @OrderNumber = @OrderNumber + 1
  END


-- Get the InventoryPackageId
SELECT @InventoryPackageId = TIPF.InventoryPackageId
FROM InventoryPackageFeatures TIPF
WHERE TIPF.InventoryPackageFeatureId = @InventoryPackageFeatureId


-- Increase Order for all numbers greater than the one being changed.
UPDATE InventoryPackageFeatures
SET InventoryPackageFeatureOrder = InventoryPackageFeatureOrder + 1
WHERE InventoryPackageId = @InventoryPackageId
AND InventoryPackageFeatureOrder >= @orderNumber

  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder


-- Now that the order has made room for the new entry, set the new order number for the target record
UPDATE InventoryPackageFeatures
SET InventoryPackageFeatureOrder = @OrderNumber
WHERE InventoryPackageFeatureId = @InventoryPackageFeatureId

-- At this point, depending on how many are in the list, there may be a hole in the order.
-- particularly if your moving an existing the middle of order list


  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder

-- Use this new resulting order within a table update
UPDATE TIPF
    SET InventoryPackageFeatureOrder = TIPF2.newOrder
    FROM InventoryPackageFeatures TIPF
	  INNER JOIN (-- This creates an order number field
						SELECT ROW_NUMBER() OVER(ORDER BY TIPF1.InventoryPackageFeatureOrder) AS newOrder,
		        * FROM InventoryPackageFeatures TIPF1
            WHERE TIPF1.InventoryPackageId = @InventoryPackageId
						) TIPF2
		ON TIPF.InventoryPackageFeatureId = TIPF2.InventoryPackageFeatureId





  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder

Test Time Difference

Wade Cantley

Peppermint Spiral of Time

  • 1 shot White Godiva liqueur
  • 1 shot White Crème de Menthe
  • 0.50 shot Green Crème de Menthe
  • chocolate syrup

Sometimes we just need to know how long something takes.  This is what I use to test sections of code.

-- BEGIN TIMED TEST --
DECLARE @timeTest DATETIME
SET @timeTest = GETDATE()
-- BEGIN TIMED TEST --


-- Code to test for length of time


-- END TIMED TEST
SELECT DATEDIFF(millisecond, @timeTest, GETDATE()) AS [in Milliseconds]
, DATEDIFF(millisecond, @timeTest, GETDATE()) / 1000 AS [in Seconds]
-- END TIMED TEST