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
*/