How to Get the Digits After the Decimal Point

I have spent this Sunday afternoon to work on solutions for this simple request.
I found about ten ways to get the result before dinner. I keep thinking to get another two at the end of this post.
-Topic #9
–how to get the digits after the decimal point (ex: 3.589 returns 589, etc).

DECLARE @d DECIMAL(10, 3)=10.589

–Cast the number to a varchar(50) string
DECLARE @s VARCHAR(50)=Cast(@d AS VARCHAR(50))

SELECT

/*
Solution 1: Use the original number minus an integer converted from the original number to an integer
and then conver the decimal number to a varchar (50) to use the STUFF function to remove the ‘0.’
part from the decimal number.
*/
Stuff(Cast(@d – Cast(@d AS INT) AS VARCHAR(50)), 1, 2, ”)

/*
Solution 2: Remove the “0.” part from the above calculation with a substring function
*/
,Substring(Cast(@d – Cast(@d AS INT) AS VARCHAR(50)), 3, Len(@d))

/*
Solution 3: Use right function to remove the leading “0.” from the string
*/

, RIGHT(Cast(@d – Cast(@d AS INT) AS VARCHAR(50)), Len(Cast(@d – Cast(@d AS INT) AS VARCHAR(50))) – 2)

/*
Solution 4: Use Parsename function to get the value after the first dot.
*/
,Parsename(@s, 1)
/*
Solution 5: Use charindex to get the postion of . and use a substring function to get the value after the decimal
*/
,Substring(@s, Charindex(‘.’, @s) + 1, Len(@s) – Charindex(‘.’, @s))

/*
Solution 6: Find the . position using charindex and stuff everythin before decimal and the deciaml.
*/
,Stuff(@s, 1, Charindex(‘.’, @s), ”)
,Stuff(@s, 1, Charindex(0X2E, Cast(Ltrim(@s) AS VARBINARY(50)), 1), ”)

/*
Solution 7: Reverse the string and find the position of . and use LEF function to get the substring and reverse the substring back.
*/
,Reverse(LEFT(Reverse(@s), Charindex(‘.’, Reverse(@s)) – 1))

/*
Solution 8: Remove everthing before dot with a STUFF function with empty string.
*/
, Stuff(@s, 1, Charindex(‘.’, @s), ”)
/*
Solution 9: CAST the string as an XML type and retrieve the value from the XML node
*/
,Cast (N”
+ Replace(@s, ‘.’, ”)
+ ” AS XML).value(‘(/root/row)[2]’, ‘nvarchar(50)’)
/*
Solution 10: Use the number to MODE the the number converted to an integer and remove the “0.” part afterwards.
Use a case statement to handle the original number smaller than 1.
*/
,Replace(Cast(@d%(CASE WHEN Cast(@d AS INT)=0 THEN 1 END) AS VARCHAR(50)), ‘0.’, ”)

/*
Solution 11: Replace the whole digit part if ther is any and the decimal point.
*/

,Replace(@s,cast(Cast(@d AS INT) as varchar(50))+’.’,”)
/*
Solution 12: Get the value of decimal part and use 10’s power to remove the decimal points
*/

,CAST((@d – Cast(@d AS INT))* POWER(10,len(Cast(@d – Cast(@d AS INT) AS VARCHAR(50)))-2) as int)
,CAST((@d – Cast(@d AS INT))* POWER(10,len(@d – Cast(@d AS INT))-2) as int)

UNPIVOT with Table Value Constructor

Here is a sample to compare function UNPIVOT with Table Value Constructor in SQL Server 2008 and above.

CREATE TABLE Dates (id INT IDENTITY(1, 1),date1 DATETIME,date2 DATETIME,date3 DATETIME)

INSERT INTO Dates
(date1,date2,date3)
VALUES ('1/1/2012','1/2/2012','1/3/2012'),
('1/1/2012',NULL,'1/13/2012'),
('1/1/2012','1/2/2012',NULL),
('8/30/2012','9/10/2012','1/1/2013')

--Table Value
Select ID, MyDate, Date123
FROM Dates A
Cross Apply ( Values (Date1,'Date1'), (Date2,'Date2'), (Date3,'Date3')) B(MyDate, Date123)

--UNPIVOT
SELECT ID, MyDate ,date123 FROM (SELECT id,date1,date2, date3 FROM Dates) src
UNPIVOT (MyDate
FOR date123 IN ([date1],
[date2],
[date3])) unpvt

A Sample with Table Value Constructor (Transact-SQL)

There are a few ways to find maximum value from multiple columns:
http://blogs.msdn.com/b/samlester/archive/2012/09/10/tsql-solve-it-your-way-finding-the-max-value-from-multiple-columns.aspx

Steven has a good example with Table Value Constructor:
Select ID, Date1, Date2, Date3, MaxDate
FROM Dates A Cross Apply (Select Max(MyDate) As MaxDate
From ( Values (Date1), (Date2), (Date3)) B(MyDate) ) C

http://technet.microsoft.com/en-us/library/dd776382.aspx

Timestamp Update — A Sample

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7c2985fc-1ede-43e4-bd40-9b66f3bf7fda

DECLARE @s TABLE ( Startdt DATETIME,Hours DECIMAL(6, 2))

INSERT INTO @s
VALUES (‘2012-08-09 23:00:00.000’,8.00),
(‘2012-08-09 07:00:00.000’,8.00),
(‘2012-08-09 15:00:00.000’,0.80),
(‘2012-08-09 15:00:00.000’,7.00),
(‘2012-08-09 15:00:00.000’,0.20),
(‘2012-08-10 23:00:00.000’,5.40),
(‘2012-08-10 23:00:00.000’,2.60);

WITH mycte
AS (SELECT startdt,Hours,ROW_NUMBER() OVER(Partition BY startdt ORDER BY startdt) rn FROM @s)

,mycte2
AS (SELECT startdt,MAX([1]) AS c1,MAX([2]) AS c2,MAX([3]) AS c3
FROM (SELECT startdt, Hours, rn FROM mycte) src
PIVOT (MAX(hours) FOR rn IN ([1], [2], [3])) pvt
GROUP BY startdt)

SELECT CASE WHEN col = ‘c1’ THEN startdt
WHEN col = ‘c2’ THEN e1
WHEN col = ‘c3’ THEN e2
END AS stardt,
Hours,
CASE WHEN col = ‘c1’ THEN e1
WHEN col = ‘c2’ THEN e2
WHEN col = ‘c3’ THEN e3
END AS enddt
FROM (SELECT startdt,c1,c2,c3,DATEADD(minute, c1 * 60, startdt) e1,
DATEADD(minute,( c1 + c2 ) * 60, startdt) e2,DATEADD(minute,( c1 + c2 + c3 ) * 60, startdt) e3 FROM mycte2)src
UNPIVOT(Hours FOR col IN (c1, c2, c3)) unpvt

–Result
/*

2012-08-09 07:00:00.000 8.00000 2012-08-09 15:00:00.000
2012-08-09 15:00:00.000 0.80000 2012-08-09 15:48:00.000
2012-08-09 15:48:00.000 7.00000 2012-08-09 22:48:00.000
2012-08-09 22:48:00.000 0.20000 2012-08-09 23:00:00.000
2012-08-09 23:00:00.000 8.00000 2012-08-10 07:00:00.000
2012-08-10 23:00:00.000 5.40000 2012-08-11 04:24:00.000
2012-08-11 04:24:00.000 2.60000 2012-08-11 07:00:00.000

*/

Check Rows’ NULL Percentage For a Given Table –T-SQL

–USE YourDB
–GO
DECLARE @colsCast NVARCHAR(4000)
, @cols NVARCHAR(4000)
, @sql1 NVARCHAR(4000)
, @sql2 NVARCHAR(4000)
, @Schema_Name NVARCHAR(4000) =’dbo’
, @TABLE_NAME NVARCHAR(4000) =’test1′
,@num NVARCHAR(4000)

SELECT @colsCast = COALESCE(@colsCast + ‘, ‘, ”) + ‘CAST(‘+ Quotename(column_Name)+’ AS NVARCHAR(4000)) AS ‘+ Quotename(column_Name)
, @cols = COALESCE(@cols + ‘, ‘, ”) + Quotename(column_Name) , @num=count(*) over()
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
print @cols

–You can modify the WHERE clause to filter your original table

SET @sql1=’IF OBJECT_ID(N”dbo.tmp”,N”U”) IS NOT NULL
Begin
DROP Table dbo.tmp
END

Begin
SELECT * INTO tmp
FROM ‘+ Quotename(@TABLE_NAME) + ‘ WHERE 1=1
END’

EXEC sp_executesql @sql1

SET @sql2=’WITH mycte AS
(
SELECT rn, col, val FROM (SELECT ‘+ @colsCast+ ‘, row_number() OVER(Order by ‘ + @cols+’ ) rn FROM tmp) src
UNPIVOT (val For col IN ( ‘+ @cols+ ‘)) unpvt )
, mycte2 AS ( SELECT rn, col, val, COUNT(val) OVER(Partition by rn) cnt , ‘+@num+’ as cntAll FROM mycte )
, mycte3 AS (SELECT rn, col, val, row_number() OVER(Order by cnt DESC ) rn2 , CAST(cnt*1.0/ cntALL*100 as decimal(6,2)) as NullPercentForRow FROM mycte2)

SELECT ‘+ @cols+ ‘, NullPercentForRow from (select rn, col, val, NullPercentForRow FROM mycte3
–WHERE rn=(select rn from mycte3 WHERE rn2=1 )
) src
PIVOT (max(val) FOR col IN ( ‘+ @cols+ ‘)) pvt ‘

print @sql2

EXEC sp_executesql @sql2

Pivot Table with T-SQL Dynamically– Another Sample

CREATE TABLE [dbo].[Test1](
[id] [int] IDENTITY(1,1) NOT NULL,
[PersonName] [varchar](50) NULL,
[PaymentDate] [datetime] NULL,
[Paymentamount] [int] NULL
) ON [PRIMARY]

INSERT[dbo].[Test1]
VALUES (‘A’,’1/1/2012′,100),
(‘B’,’1/2/2012′,200),
(‘C’,’1/3/2012′,300),
(‘D’,’1/4/2012′,400),
(‘E’,’1/2/2012′,500),
(‘A’,’2/1/2012′,500),
(‘B’,’2/2/2012′,300),
(‘C’,’2/3/2012′,100),
(‘D’,’2/4/2012′,200),
(‘E’,’2/5/2012′,400),
(‘A’,’3/1/2012′,200),
(‘B’,’3/3/2012′,100),
(‘C’,’3/3/2012′,300),
(‘D’,’3/4/2012′,500),
(‘E’,’3/5/2012′,400)

–Code
DECLARE @cols NVARCHAR(4000), @sql NVARCHAR(4000)

SELECT @cols = COALESCE(@cols + ‘,’, ” )+ ‘[‘+shortname+’]’ FROM (
SELECT PaymentDate, STUFF(CONVERT(VARCHAR(15), PaymentDate, 107), 4, 7, ‘-‘) AS shortName FROM test1 ) t GROUP BY shortname Order by MIN(PaymentDate)

–PRINT @cols

SET @sql = ‘SELECT PersonName ,’ +@cols + ‘ FROM ( SELECT PersonName,Paymentamount, STUFF(CONVERT(VARCHAR, [PaymentDate], 107), 4, 7, ”-”) AS shortname FROM Test1) src
PIVOT( SUM(Paymentamount) FOR shortname IN (‘ + @cols + ‘)) pvt ORDER BY PersonName ‘

EXEC sp_executesql @sql

Find the Longest Repeated Substring with T-SQL

Find the longest repeated substring with T-SQL

Step 1: Create a number table based on the stirng length but it would be better to use an auxiliary numbers table;
Step 2: Cross join Numbers table to create positions and steps for all substring combinations;
Step 3: Calculate the occurrence of substrings in various lengths, the length for each substring and a sequence within a substring based on the step. The WHERE condition is to eliminate double count for any substrings after the last full step increaments;
Step 4: Get all substrings with at least two occurrrences;
Step 5: Remove repeating substring with overlapping and get the ranking order for the longest substrings;
Step 6: Retrieve the longest repeating substring(s) with two options. The option1 is to get longest string with tie breaker (first occurrence) by using rnLen=1 and the second option is to get longest strings without tie breaker by using rnkLen=1;

In order to change the default recursion limit from 100 to no limit for CTEs, the query hints MAXRECURSION is set to 0.

/************************************************************************************/
Declare @MyString varchar(max)
–Plain DNA Sequence Sample
–http://www.genomatix.de/online_help/help/sequence_formats.html

Set @MyString =’ACAAGATGCCATTGTCCCCCGGCCTCCTGCTGCTGCTGCTCTCCGGGGCCACGGCCACCGCTGCCCTGCC
CCTGGAGGGTGGCCCCACCGGCCGAGACAGCGAGCATATGCAGGAAGCGGCAGGAATAAGGAAAAGCAGC
CTCCTGACTTTCCTCGCTTGGTGGTTTGAGTGGACCTCCCAGGCCAGTGCCGGGCCCCTCATAGGAGAGG
AAGCTCGGGAGGTGGCCAGGCGGCAGGAAGGCGCACCCCCCCAGCAATCCGCGCGCCGGGACAGAATGCC
CTGCAGGAACTTCTTCTGGAAGACCTTCTCCTCCTGCAAATAAAACCTCACCCATGAATGCTCACGCAAG’

— Set @MyString = ‘3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117067982148086513282306647093844609550582231725359408128481117450284102701938521105559644622948954930381964428810975665933446128475648233’

;WITH Numbers AS
(
SELECT 1 AS i
UNION ALL
SELECT i + 1 AS i FROM Numbers WHERE i = Step
)

,mycte3 as
(
SELECT Step,StartPos, SubSeq, maxLen, rnSubSeq
FROM mycte2
WHERE cntSubSeq>1
)

,mycte4 as
(
SELECT a.SubSeq, a.StartPos, a.maxLen
,RANK() OVER (order by a.maxLen DESC) AS rnkLen
,Row_Number() OVER (order by a.maxLen DESC,a.Step) AS rnLen
FROM mycte3 a
LEFT JOIN mycte3 b ON a.SubSeq=b.SubSeq and (a.rnSubSeq=b.rnSubSeq-1 OR b.rnSubSeq-1 = 0)
WHERE (a.StartPos+a.Step-1) < b.StartPos

)

SELECT SubSeq as [LognestRepeatingSubstring] FROM mycte4
WHERE rnkLen=1
Order by maxLen DESC, StartPos

OPTION (maxrecursion 0)

Running Total Made Easy with SQL Server 2012

With SQL Server 2012, it is easy to get a running total with the enhanced window function. Here is a solution for a quetion from MSDN T-SQL forum:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/c7ce7da5-904b-42ca-9cd1-7872635b9de2
–SQL Server 2012
declare @InvBal table (
WeekNumber int,
ProductLineCode char(4),
BrandCode char(4),
ProjectedSalesQty int,
PlannedReceptionsQty int,
ClosingStockQty int,
primary key (WeekNumber, ProductLineCode, BrandCode)
)

declare @i int = 1

while @i<28
begin
insert into @InvBal values (@i, 'L001', 'B001', 10*RAND(), 100*RAND(), null)
insert into @InvBal values (@i, 'L001', 'B002', 10*RAND(), 100*RAND(), null)
insert into @InvBal values (@i, 'L002', 'B003', 10*RAND(), 100*RAND(), null)
insert into @InvBal values (@i, 'L002', 'B004', 10*RAND(), 100*RAND(), null)

set @i = @i + 1;
end

update @InvBal set ClosingStockQty = 1000*RAND() where WeekNumber = 1;

–Query
SELECT WeekNumber,ProductLineCode,BrandCode,ProjectedSalesQty,PlannedReceptionsQty,
SUM(ISNULL(ClosingStockQty, (PlannedReceptionsQty – ProjectedSalesQty )))
OVER ( Partition BY ProductLineCode, BrandCode ORDER BY WeekNumber) AS ClosingStockQty
FROM @InvBal
Order by ProductLineCode, BrandCode, WeekNumber;