|
|
May 2009 Blog Posts
CREATE FUNCTION dbo.fnCheckDanSSN
(
@SSN CHAR(10)
)
RETURNS BIT
AS
BEGIN
IF @SSN NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
RETURN 0
DECLARE @x CHAR(6)
SET @x = SUBSTRING(@SSN, 5, 2) + SUBSTRING(@SSN, 3, 2) + SUBSTRING(@SSN, 1, 2)
IF SUBSTRING(@SSN, 7, 1) IN('0', '1', '2', '3') AND ISDATE('19' + @x) = 0
RETURN 0
IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '36' AND ISDATE('20' + @x) = 0
RETURN 0
IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '37' AND '99' AND ISDATE('19' + @x) = 0
RETURN 0
IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2)...
CREATE FUNCTION dbo.fnCalculateFinSSN
(
@SSN CHAR(10)
)
RETURNS CHAR(11)
AS
BEGIN
IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9]'
RETURN NULL
IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0
RETURN NULL
IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0
RETURN 0
DECLARE @Digits INT
SET @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31
RETURN @@SSN + SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1)
END
CREATE FUNCTION dbo.fnCheckFinSSN
(
@SSN CHAR(11)
)
RETURNS BIT
AS
BEGIN
IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9][0-9a-y]'
RETURN 0
IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0
RETURN 0
IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0
RETURN 0
DECLARE @Digits INT
SET @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31
IF SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1) <> LOWER(RIGHT(@SSN, 1))
SET @Digits = -1
RETURN @Digits + 1
END
Wow! This seems to be blog post number 100.
Today I stumbled across an interesting problem where OP had a set of records. Every record were "typed" and "grouped".
Now OP wanted to get distinct group id's for those groups having at least one record typed 33, but not having a record typed 11.
This can be done in a number of ways, but I wanted to write an efficiently running query.
DECLARE @Sample TABLE
(
MeetingID INT,
TicketID INT
)
INSERT @Sample
SELECT 1, 11 UNION ALL
SELECT 1, 22 UNION ALL
SELECT 1, 33 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 33 UNION ALL
SELECT 3, 11 UNION ALL
SELECT ...
CREATE FUNCTION dbo.fnISOWEEK
(
@theDate DATETIME
)
RETURNS TINYINT
AS
BEGIN
RETURN (
SELECT CASE
WHEN @theDate >= '99990104' THEN (DATEPART(DAYOFYEAR, @theDate) - 4) / 7
WHEN @theDate >= '99990101' THEN 52
WHEN NextYear <= @theDate THEN 0
WHEN CurrentYear <= @theDate THEN DATEDIFF(DAY, CurrentYear, @theDate) / 7
ELSE DATEDIFF(DAY, PreviousYear, @theDate) / 7
END + 1
FROM (
SELECT DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, -1, Jan4)) / 7) * 7, '17530101') AS PreviousYear,
DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
FROM (
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @theDate), 3) AS Jan4
) AS x
)...
|