Useful SQL code, often use in Finance Group

SET NOCOUNT ON;

--*******************************************
-- Problem: Numerically Sort AlphaNumeric Data
-- 1        NULL first, Then Alpha, Then Numeric
--*******************************************

DECLARE @MyTable table
   (  RowID    int         IDENTITY,
      MyData   varchar(12)
   );

INSERT INTO @MyTable VALUES ( '95' );
INSERT INTO @MyTable VALUES ( '2' );
INSERT INTO @MyTable VALUES ( 'N/A' );
INSERT INTO @MyTable VALUES ( 'Bill' );
INSERT INTO @MyTable VALUES ( '14' );
INSERT INTO @MyTable VALUES ( '$25' );
INSERT INTO @MyTable VALUES ( '2.5' );
INSERT INTO @MyTable VALUES ( '0.5' );
INSERT INTO @MyTable VALUES ( NULL );

SELECT
   RowID,
   MyData
FROM @MyTable
ORDER BY
   CASE WHEN MyDATA IS NULL THEN 1 ELSE 2 END,
   CASE WHEN MyData LIKE '%[^$.0-9]%' THEN 1 ELSE 2 END,
   CASE WHEN MyData NOT LIKE '%[^$.0-9]%' THEN cast( MyData AS money ) END;

/*
RowID       MyData
----------- ------------
7           NULL
3           N/A
2           2
6           2.5
4           14
5           $25
1           95
*/

--*********************************************
-- Problem: Table Recursion using CTE
-- 2        Find all Paths related to INPUT
--       show query plan spooling
--    SQL 2008+ can do this with Hierarchy datatype
--*********************************************

DECLARE @Ancestor varchar(10);

DECLARE @Family table
   (  Parent varchar(10) ,
      Child  varchar(10)
   );

INSERT INTO @Family VALUES( 'X', 'Y' );
INSERT INTO @Family VALUES( 'X', 'Z' );
INSERT INTO @Family VALUES( 'Y', 'A' );
INSERT INTO @Family VALUES( 'Y', 'B' );
INSERT INTO @Family VALUES( 'B', 'C' );
INSERT INTO @Family VALUES( 'B', 'D' );
INSERT INTO @Family VALUES( 'C', 'E' );
INSERT INTO @Family VALUES( 'C', 'F' );
INSERT INTO @Family VALUES( 'F', 'G' );

SET @Ancestor = 'Y'

;WITH CTE
   (  Parent,
      Child,
      Level,
      Lineage
   )
AS
   (  SELECT
         Parent,
         Child,
         1 AS [Level],
         cast(Parent + '/' + Child as varchar(100)) AS [Lineage]
      FROM @Family
      WHERE Parent = @Ancestor

      UNION ALL

      SELECT
         Data.Parent,
         Data.Child,
         Level + 1,
         cast( Lineage + '/' + Data.Child AS varchar(100) )
      FROM @Family Data
         JOIN CTE
            ON Data.Parent = CTE.Child
   )

SELECT
   Parent,
   Child,
   Lineage,
   Level
FROM CTE
ORDER BY Lineage;

/*
Parent Child Lineage        Level
------ ----- -------------- -----
Y      A     Y/A            1
Y      B     Y/B            1
B      C     Y/B/C          2
C      E     Y/B/C/E        3
C      F     Y/B/C/F        3
F      G     Y/B/C/F/G      4
B      D     Y/B/D          2
*/

--********************************************************
-- Problem: Delete All BUT the MOST RECENT Row for each
-- 3        Client Record
--********************************************************

SET NOCOUNT ON;

DECLARE @MyTable table
   (  [ID]               int IDENTITY,
      MemberID           int,
      [Name]             varchar(20),
      SurName            varchar(20),
      DateOfAssessment   datetime
   );

INSERT INTO @MyTable VALUES ( 2, 'Bill', 'Jones', '2007/05/25' );
INSERT INTO @MyTable VALUES ( 3, 'Mary', 'Smith', '2007/05/26' );
INSERT INTO @MyTable VALUES ( 4, 'Susy', 'Williams', '2007/05/22' );
INSERT INTO @MyTable VALUES ( 2, 'Bill', 'Jones', '2007/05/27' );
INSERT INTO @MyTable VALUES ( 2, 'Bill', 'Jones', '2007/04/26' );
INSERT INTO @MyTable VALUES ( 3, 'Mary', 'Smith', '2007/05/21' );
INSERT INTO @MyTable VALUES ( 3, 'Mary', 'Smith', '2007/04/30' );

SELECT *
FROM   @MyTable;

DELETE @MyTable  --proprietery TSQL syntax - beware of incorrect data, especially on UPDATE FROM ...
   FROM @MyTable m
    JOIN (SELECT  --another possible solution is using ROW_NUMBER
            MemberID,
            DateOfAssessment = max( DateOfAssessment )
         FROM @MyTable
         GROUP BY MemberID ) dt
      ON (   m.MemberID = dt.MemberID
         AND m.DateOfAssessment <> dt.DateOfAssessment
         );

SELECT *
FROM   @MyTable
ORDER BY MemberID;

/*
ID     MemberID  Name      SurName     DateOfAssessment
------ --------- --------- ----------- ------------------------
4      2         Bill      Jones       2007-05-27 00:00:00.000
2      3         Mary      Smith       2007-05-26 00:00:00.000
3      4         Susy      Williams    2007-05-22 00:00:00.000
*/

--********************************************************
-- Problem: Display TOP 5 Amounts and Total of All Remaining Rows
-- 10
--********************************************************

SET NOCOUNT ON;

DECLARE @MyDataTable table
   (  [ID]     int          IDENTITY,
      [Amount] decimal(10,2)
   );

DECLARE @MyResultsTable table
   (  RowID    int          IDENTITY,
      [ID]     varchar(10),
      [Amount] decimal(10,2)
   );

INSERT INTO @MyDataTable VALUES ( 5 );
INSERT INTO @MyDataTable VALUES ( 200 );
INSERT INTO @MyDataTable VALUES ( 15 );
INSERT INTO @MyDataTable VALUES ( 325 );
INSERT INTO @MyDataTable VALUES ( 12 );
INSERT INTO @MyDataTable VALUES ( 37 );
INSERT INTO @MyDataTable VALUES ( 121 );
INSERT INTO @MyDataTable VALUES ( 32 );
INSERT INTO @MyDataTable VALUES ( 31 );
INSERT INTO @MyDataTable VALUES ( 177 );

-- First, get the TOP 5
INSERT INTO @MyResultsTable
   SELECT TOP 5
      [ID],
      [Amount]
   FROM @MyDataTable
   ORDER BY [Amount] DESC;

-- Then, Calculate the Remaining Total
INSERT INTO @MyResultsTable ( [ID], [Amount] )
   SELECT 'Remainder',
          ((  SELECT sum( Amount )
             FROM @MyDataTable
           )
          -
           (  SELECT sum( [Amount] )
             FROM   @MyResultsTable
           )
          );

-- Return the data
SELECT
   [ID],
   [Amount]
FROM   @MyResultsTable
ORDER BY RowID;

/*
ID         Amount
---------- ------------
4          325.00
2          200.00
10         177.00
7          121.00
6          37.00
Remainder  95.00
*/