|
|
Algorithms
DECLARE @Sample TABLE
(
x INT NOT NULL,
y INT NOT NULL
)
INSERT @Sample
VALUES (3, 9),
(2, 7),
(4, 12),
(5, 15),
(6, 17)
;WITH cteSource(x, xAvg, y, yAvg, n)
AS (
SELECT 1E * x,
AVG(1E * x) OVER (PARTITION BY (SELECT NULL)),
1E * y,
AVG(1E * y) OVER (PARTITION BY (SELECT NULL)),
COUNT(*) OVER (PARTITION BY (SELECT NULL))
FROM @Sample
)
SELECT SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)]
FROM cteSource
CREATE FUNCTION dbo.fnConvertUtf8Ansi
(
@Source VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Value SMALLINT = 160,
@Utf8 CHAR(2),
@Ansi CHAR(1)
IF @Source NOT LIKE '%[ÂÃ]%'
RETURN @Source
WHILE @Value <= 255
BEGIN
SELECT @Utf8 = CASE
WHEN @Value BETWEEN 160 AND 191 THEN CHAR(194) + CHAR(@Value)
WHEN @Value BETWEEN 192 AND 255 THEN CHAR(195) + CHAR(@Value - 64)
ELSE NULL
END,
@Ansi = CHAR(@Value)
WHILE CHARINDEX(@Source, @Utf8) > 0
SET @Source = REPLACE(@Source, @Utf8, @Ansi)
SET @Value += 1
END
RETURN @Source
END
For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden.
My part will be the technical details of the forecasting application now when our former DBA has left our company.
Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these.
One function I stumled across is very simple. All it does is to add a timepart from current execution time to...
A tale from a Stalker who licked his wounds and got back 9 months later...
CREATE FUNCTION dbo.fnIsOnMonthEdge
(
@theDate DATETIME
)
RETURNS SMALLINT
AS
BEGIN
RETURN CASE @theDate
WHEN '99991231' THEN 1
ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, 1, @theDate))
END +
CASE @theDate
WHEN '17530101' THEN -1
ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, -1, @theDate))
END
END
Finally!
Today I finished my presentation about finding a unified algorithm for Relational Division, which should work for all types of division; singlerecord and multirecord, singlecolumn and multicolumn and both exact division and with remainder.
Optionally it should work with single and multiple divisor sets. That's 16 permutations of relational division kinds.
A bonus point is that the algorithm I've found work across multiple platforms with standard SQL language elements.
Also, I have performance tested the algorithm with the sample data from Mr Celko here.
For such small sample set, my algorithm is in the top queries, but the real performance kicks in when you...
Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes.
This is because SQL Server add one byte that holds the precision for the datetime2 value.
Start with this very simple repro
declare @now datetimeoffset(7) = '2010-12-15 21:04:03.6934231 +03:30'
select cast(cast(@now as datetimeoffset(0)) as binary(9)),
cast(cast(@now as datetimeoffset(1)) as binary(9)),
cast(cast(@now as datetimeoffset(2)) as binary(9)),
cast(cast(@now as datetimeoffset(3)) as binary(10)),
cast(cast(@now as datetimeoffset(4)) as binary(10)),
cast(cast(@now as datetimeoffset(5)) as binary(11)),
cast(cast(@now as datetimeoffset(6)) as binary(11)),
...
Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes.
This is because SQL Server add one byte that holds the precision for the datetime2 value.
Start with this very simple repro
declare @now datetime2(7) = '2010-12-15 21:04:03.6934231'
select cast(cast(@now as datetime2(0)) as binary(7)),
cast(cast(@now as datetime2(1)) as binary(7)),
cast(cast(@now as datetime2(2)) as binary(7)),
cast(cast(@now as datetime2(3)) as binary(8)),
cast(cast(@now as datetime2(4)) as binary(8)),
cast(cast(@now as datetime2(5)) as binary(9)),
cast(cast(@now as datetime2(6)) as binary(9)),
cast(cast(@now...
SELECT [Now],
BinaryFormat,
SUBSTRING(BinaryFormat, 1, 2) AS DayPart,
SUBSTRING(BinaryFormat, 3, 2) AS TimePart,
CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT) AS [Days],
DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT), 0) AS [Today],
SUBSTRING(BinaryFormat, 3, 2) AS [Ticks],
DATEADD(MINUTE, CAST(SUBSTRING(BinaryFormat, 3, 2) AS SMALLINT), 0) AS Peso
FROM (
SELECT CAST(GETDATE() AS SMALLDATETIME) AS [Now],
CAST(CAST(GETDATE() AS SMALLDATETIME) AS BINARY(4)) AS BinaryFormat
) AS d
SELECT [Now],
BinaryFormat,
SUBSTRING(BinaryFormat, 1, 4) AS DayPart,
SUBSTRING(BinaryFormat, 5, 4) AS TimePart,
CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT) AS [Days],
DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT), 0) AS [Today],
CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT) AS [Ticks],
DATEADD(MILLISECOND, 1000.E / 300.E * CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT), 0) AS Peso
FROM (
SELECT GETDATE() AS [Now],
CAST(GETDATE() AS BINARY(8)) AS BinaryFormat
) AS d
DECLARE @Year SMALLINT = 2011,
@NumberOfYears TINYINT = 3
;WITH cteCalendar(FirstOfMonth, LastOfMonth)
AS (
SELECT DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth,
DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth
FROM master..spt_values
WHERE TYPE = 'P'
AND number BETWEEN 1 AND 12 * @NumberOfYears
)
SELECT DATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday
FROM cteCalendar
Or ask yourself, "How much of the database has changed since last backup?".
Here is a simple script that will tell you how much (in percent) have changed in the database since last backup.
-- Prepare staging table for all DBCC outputs
DECLARE @Sample TABLE
(
Col1 VARCHAR(MAX) NOT NULL,
Col2 VARCHAR(MAX) NOT NULL,
Col3 VARCHAR(MAX) NOT NULL,
Col4 VARCHAR(MAX) NOT NULL,
Col5 VARCHAR(MAX)
)
-- Some intermediate variables for controlling loop
DECLARE @FileNum BIGINT = 1,
@PageNum BIGINT = 6,
@SQL VARCHAR(100),
@Error INT,
@DatabaseName SYSNAME = 'Yoda'
-- Loop all files to the very end
WHILE 1 = 1
BEGIN
BEGIN TRY
-- Build the...
CREATE FUNCTION dbo.fnGetEasterDate
(
@Year SMALLINT
)
RETURNS DATE
AS
BEGIN
RETURN (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CONVERT(DATETIME, CAST(@Year AS CHAR(4)) + BaseDate, 112)) / 7 * 7, 6)
FROM (
SELECT CASE @Year % 19
WHEN 0 THEN '0415'
WHEN 1 THEN '0404'
WHEN 2 THEN '0324'
WHEN 3 THEN '0412'
WHEN 4 THEN '0401'
WHEN 5 THEN '0419'
WHEN 6 THEN '0409'
WHEN 7 THEN '0329'
WHEN 8 THEN '0417'
WHEN 9 THEN '0406'
WHEN 10 THEN '0326'
WHEN 11 THEN '0414'
WHEN 12 THEN '0403'
WHEN 13 THEN '0323'
WHEN 14 THEN '0411'
WHEN 15 THEN '0331'
WHEN 16 THEN '0418'
WHEN...
First create a table type like this
CREATE TYPE dbo.MyXirrTable AS TABLE
(
theValue DECIMAL(19, 9) NOT NULL,
theDate DATETIME NOT NULL
)
GO
And then you create a function like this
CREATE FUNCTION dbo.XIRR
(
@Sample MyXirrTable READONLY,
@Rate DECIMAL(19, 9) = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN
DECLARE @LastRate DECIMAL(19, 9),
@RateStep DECIMAL(19, 9) = 0.1,
@Residual DECIMAL(19, 9) = 10,
@LastResidual DECIMAL(19, 9) = 1,
@i TINYINT = 0
IF @Rate IS NULL
SET @Rate = 0.1
SET @LastRate = @Rate
WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.00000001
BEGIN
SELECT @LastResidual = @Residual,
@Residual = 0
SELECT @Residual = @Residual + theValue / POWER(1 + @Rate, theDelta / 365.0E)
FROM (
SELECT theValue,
DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta
FROM @Sample
) AS d
SET @LastRate = @Rate
If @Residual >= 0
SET...
The built-in CHECKUM function in SQL Server is built on a series of 4 bit left rotational xor operations. See here in a previous forum post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832 for more explanation.
Today, I wanted to see how often a collision (or false positive) can occur.
Let's take a very simple CHECKSUM value, for example 123. Decimal 123 is "01111011" in binary representation.
Since CHECKSUM function rotates the iterative checksum value 4 bits to the left (same thing as multiplying by 16), how many permutations of two characters returns the same CHECKSUM value of 123? The answer is 16 permutations.
Let's investigate by writing down the solution of this...
This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant.
I've come up with a solution that need only 125 bytes of storage. All other solutions covered in Mr Celko's book has at least 1,000 bytes of storage.
Here is my solution, complete with all procedures to assign and release seats, together with views to display current status of each and one seat.
-- Setup sample data
CREATE TABLE dbo.Restaurant
(
Seats BINARY(125) NOT NULL
)
-- Initialize an empty restaurant
INSERT dbo.Restaurant
(
Seats
)
SELECT 0x
GO
-- Create procedure for handling seat assignment
CREATE...
This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog.
Consider this sample data
DECLARE @Sample TABLE
(
ID INT,
Data VARCHAR(100)
)
INSERT @Sample
VALUES (1, 'Peso & Performance SQL'),
(1, 'MVP'),
(2, 'Need help <? /> -- '),
(2, 'With XML string concatenation ?')
The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out.
So here is the final query.
SELECT i.ID,
STUFF(f.Content.value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM (
SELECT ID
FROM @Sample
GROUP...
I got an email from Mr Celko and he correctly stated that my previous solution was not truly working with sets, so he posted me some solutions and proper sample data.
With this information at my hand, I started to investigate what really is needed to get this Relational Division to work properly with two sets; Dividend and Divisor.
Some of you know me well, and know I am not satisfied with just solving the problem. There have to be some tweaks, and I did that too with this solution. Not only is it only touching the Dividend table once and...
I came across an interesting post on Microsoft SQL Server forum this afternoon. It was a question about Relational algebra and the poster wanted to have an efficient query to solve his problem.
The problem could be solved with relational division, but there is no such operator in SQL Server. Maybe there will be some day.
For a fully working solution, see http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx
But for now there is no such operator, so we as developers have to find our own ways.
First prepare and populate some sample data
-- Prepare sample data
DECLARE @Sample TABLE
(
ParentID INT NOT NULL,
Keyword VARCHAR(25) NOT NULL,
UNIQUE (ParentID, Keyword)
)
-- Populate sample data
INSERT ...
This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML.
First, create some sample data like this
-- Prepare sample data
DECLARE @Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Expense SMALLMONEY NOT NULL
)
-- Populate sample data
INSERT @Sample
(
Expense
)
VALUES (12.51),
(45.63),
(66.35),
(92.66),
(65.46),
(54.01),
(32.23),
(27.16),
(78.92),
(14.58)
Next, we need to create a variable to hold the user's wanted total sum.
-- Prepare user supplied parameter
DECLARE @WantedSUM SMALLMONEY = 111.09
And we also need to create a temporary staging table to hold the valid combinations
-- Prepare temporary staging table
DECLARE @Temp TABLE
(
CombID INT...
Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel.
DECLARE @Value BIGINT = 8839,
@Base TINYINT = 26
;WITH cteSequence(Position, Value, Chr)
AS (
SELECT CAST(LOG(@Value - @Value / @Base) / LOG(@Base) AS INT),
CAST(@Value - 1 AS BIGINT),
CAST(CHAR(65 +(@Value - 1) % @Base) AS VARCHAR(MAX))
UNION ALL
SELECT Position - 1,
Value / @Base - 1,
CHAR(65 +(Value / @Base - 1) % @Base) + Chr
FROM cteSequence
WHERE Position > 0
)
SELECT Chr
FROM cteSequence
WHERE Position = 0
Today I answered a question where OP wanted to convert a 64-bit filetime value to a system time. After a little reading about FILETIME, I learnt that the number is a value for 100 ns passed since January 1st 1601. This gets little tricky since the normal date zero for SQL Server is January 1st 1900.
But with a little arithmetic things worked out. The most cumbersome part was the INT limit for passing parameters to DATEADD function, but that was easy to overcome too. However, I am not convinced the actual number for the nanosecond value is returned "reversed" from...
This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx.
The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week.
Or you can use the function blogged here http://weblogs.sqlteam.com/peterl/archive/2009/12/01/How-to-get-a-date-from-Year-week-and-weekday.aspx.
CREATE FUNCTION dbo.fnISOMonday
(
@theYear SMALLINT,
@theWeek TINYINT
)
RETURNS DATETIME
AS
BEGIN
RETURN (
SELECT DATEADD(DAY, 7 * @theWeek - 7, CurrentYear)
FROM (
SELECT 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, @theYear - 1900, 3) AS Jan4
WHERE @theYear BETWEEN 1900 AND 9999
...
Joe Celko has posted a new Stumper - The Class Scheduling Problem
here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/
Here is one suggestion to solve the problem. It's linear in time so it should be very fast. It's based on the "Descending order" approach, and the "paths" columns are used to store valid room and classes.
-- Initialize and find the valid combinations
DECLARE @HowManySeatsFree INT = 0 -- Set to zero for maximum seating, and to 1 for letting in late pupils.
DECLARE @Source TABLE
(
room_nbr CHAR(2),
class_nbr CHAR(2),
recID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
)
INSERT @Source
(
room_nbr,
class_nbr
)
SELECT r.room_nbr,
c.class_nbr
FROM dbo.Rooms AS r
INNER JOIN dbo.Classes AS...
This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx.
The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. I wanted do display that there are other way to write a query to make it more efficient. Adam's SQLCLR version runs in 0.5 seconds.
DECLARE @Interval INT = 7
;WITH cteSingle(ProductID, TransactionDate, recID)
AS (
SELECT ProductID,
TransactionDate,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID
FROM Production.TransactionHistory
), cteLower(ProductID, StartDate, recID)
AS (
SELECT s.ProductID,
s.TransactionDate AS EndDate,
ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID
FROM ...
This is one way to create permutations of a word. It is certainly not the fastest way (there are hardwired and specialized approaches, and there are other algorithms such as Fike) but this is very simple.
DECLARE @Word VARCHAR(10) = 'Peter'
;WITH cteYak(Word, Letters)
AS (
SELECT CAST(SUBSTRING(@Word, Number, 1) AS VARCHAR(10)) AS Word,
STUFF(@Word, Number, 1, '') AS Letters
FROM dbo.TallyNumbers
WHERE Number BETWEEN 1 AND LEN(@Word)
UNION ALL
SELECT CAST(Word + SUBSTRING(y.Letters, n.Number, 1) AS VARCHAR(10)) AS Word,
STUFF(y.Letters, n.Number, 1, '') AS Letters
FROM cteYak AS y
INNER JOIN dbo.TallyNumbers AS n ON n.Number BETWEEN 1 AND LEN(y.Letters)
)
SELECT DISTINCT
Word
FROM cteYak
WHERE LEN(Word) = LEN(@Word)
This is currently for ISO weeks only, but if you have the week number, the weekday and year and you want that date in return, you can use this function below. And according to ISO, Monday is 1 and Sunday is 7.
CREATE FUNCTION dbo.fnGetDateFromYearWeekWeekday
(
@Year INT,
@Week INT,
@Weekday INT
)
RETURNS DATETIME
AS
BEGIN
RETURN CASE
WHEN @Year < 1900 OR @Year > 9999 THEN NULL
WHEN @Week < 1 OR @Week > 53 THEN NULL
WHEN @Weekday < 1 OR @Weekday > 7 THEN NULL
WHEN @Year = 9999 AND @Week = 52 And @Weekday > 5 THEN NULL
WHEN DATEPART(YEAR, DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, 4, DATEADD(YEAR, @Year - 1900, 7)) / 7...
I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year.
See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx
This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards. If you pass a positive number for @Nth you will return the Nth weekday within the specified period, starting forwards from @FromDate. If you pass a negative number for @Nth, you will return the Nth weekday starting backwards from @ToDate.
CREATE FUNCTION dbo.fnWeekdayOfPeriod
(
@FromDate AS DATETIME,
@ToDate AS DATETIME,
@WeekDay TINYINT,
@Nth INT
)
RETURNS DATETIME
AS
BEGIN
RETURN (
SELECT DATEADD(DAY,
CASE
WHEN @Weekday < theFrom %...
Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs.
Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records.
My rewrite is two-part
1) The functions accepts a Starting point and an Ending point.
2) The functions accepts a Stepping point. You may want only odd or even numbers? Or only every 7th day?
Here are the functions. Have fun with them!
I haven't checked performance compared to MVJ's F_NUMBER_TABLE function, but they should be at least equally fast.
CREATE FUNCTION dbo.fnGetNumbers
(
@FromNum INT,
@ToNum...
I recommend you read it. Next part will contain a brilliant solution for calculating concurrent sessions in a linear algorithm. The math involved is very good indeed.
First part is found here http://www.sqlmag.com/articles/index.cfm?articleid=102734
//Peso
Yesterday Joe Celko posted on microsoft.public.sqlserver.programming about how to write an elegant query for Weighted Median. He managed to get the correct results but always ended up with ugly code.
Joe had a feeling an elegant query existed but was not seeing it. Anyway, since Celko's books have helped me in the past, I thought I should help him now.
Consider this sample data
DECLARE @Foo TABLE
(
x INT NOT NULL
)
INSERT @Foo
VALUES (1),
(2),
(2),
(3),
(3),
(3)
The most common approach to calculate the median value I have seen is
SELECT AVG(1.0E * x)
FROM (
SELECT x,
ROW_NUMBER() OVER (ORDER BY x DESC) AS a,
ROW_NUMBER() OVER (ORDER BY x) AS...
For more information about my SQLCLR, see
http://www.developerworkshop.net/software.html
The biggest difference from Beta2 stage is that I now only target SQL Server 2008 and later.
//Peso
Hi!
I am between session of 24 hour of PASS right now, so I just post this piece of code I helped out with on a forum.
The original poster was amazed that
Col1 <> 'Some value'
didn't return same records as
Col1 NOT IN ('Some value')
See this example code to understand the implications of fiddling with SET ANSI_NULLS option setting.
declare @sample table
(
d varchar(200)
)
Insert @sample
select 'cancelled' union all
select null
-- Try 1
set ansi_nulls off
select d as [Try 1, <>, ANSI_NULLS off]
from @sample
where d <> 'cancelled'
select d as [Try 1, NOT IN, ANSI_NULLS off]
from @sample
where d not in ('cancelled')
-- Try 2
set ansi_nulls on
select d as [Try...
The answer to your question is "Yes, I am having a slow day today."
;WITH Fibonacci(n, f, f1)
AS (
SELECT CAST(1 AS BIGINT),
CAST(0 AS BIGINT),
CAST(1 AS BIGINT)
UNION ALL
SELECT n + 1,
f + f1,
f
FROM Fibonacci
WHERE n < 93
)
SELECT n,
f AS Number
FROM Fibonacci
Phew! Now it's finally done.
I haven't coded outside SQL Server since 2007 and that was with VB version 6.0.
Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. To start easy, I coded a "least square regression" routine and you can download it from this location
Regression.dll http://regression.developerworkshop.net/dws.Regression.dll
Install.sql http://regression.developerworkshop.net/Install.sql
Sample.sql http://regression.developerworkshop.net/Sample.sql (good linear regression)
Sample2.sql http://regression.developerworkshop.net/Sample2.sql (better linear regression)
Sample3.sql http://regression.developerworkshop.net/Sample3.sql (polynomial regression)
Update:
* I have created a homepage for this SQLCLR function http://regression.developerworkshop.net
For the Sample2 above, the result for c: drive on Server1 look like this
<dws bestfit="linear" r2="0.99" type="least square...
declare @t table (Id int, dt datetime, value int)
set dateformat 'dmy'
insert into @t
select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8
SELECT Id,
MIN(dt) AS Startdt,
MAX(dt) AS Enddt,
MIN(value) AS Value
FROM (
SELECT Id,
dt,
value,
ROW_NUMBER() OVER (PARTITION BY Id,...
Today I was involved in an interesting topic about how to check if a text string really is integer or not.
This is what I finally suggested.
CREATE FUNCTION dbo.fnIsINT
(
@Data NVARCHAR(11)
)
RETURNS INT
AS
BEGIN
RETURN CASE
WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL
WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL
WHEN @Data IN('-', '+') THEN NULL
WHEN CAST(@Data AS BIGINT) NOT BETWEEN -2147483648 AND 2147483647 THEN NULL
ELSE CAST(@Data AS INT)
END
END
And the BIGINT alternative
CREATE FUNCTION dbo.fnIsBIGINT
(
@Data NVARCHAR(20)
)
RETURNS BIGINT
AS
BEGIN
RETURN CASE
WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL
WHEN SUBSTRING(@Data, 1, 1) NOT LIKE...
It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do.
But this will work, and work fast!
DECLARE @Sample TABLE
(
Col1 INT,
Col2 INT,
Col3 INT,
Col4 INT,
Col5 INT,
Col6 INT,
Col7 INT,
Col8 DATETIME
)
INSERT @Sample
SELECT 43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:00' UNION ALL
SELECT 43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:05' UNION ALL
SELECT 43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:10' UNION ALL
SELECT 43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:15' UNION ALL
SELECT 43, 12345, 99887, 0, 0, 0,...
It has been some years since this article was posted http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data and things has evolved since then. So I thought about using XML to solve the case. If the number of records are large, maybe this approach http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Calculating-Running-Streak-over-many-records.aspx will be better for you?
Below, I am using the same sample data as the original.
DECLARE @Sample TABLE
(
GameDate DATETIME,
Result CHAR(1)
)
INSERT @Sample
SELECT '1/1/2000', 'W' UNION ALL
SELECT '1/12/2000', 'L' UNION ALL
SELECT '1/15/2000', 'W' UNION ALL
SELECT '1/17/2000', 'W' UNION ALL
SELECT '1/22/2000', 'W' UNION ALL
SELECT '2/1/2000', 'L' UNION ALL
SELECT '2/5/2000', 'W' UNION ALL
SELECT '2/8/2000', 'L' UNION ALL
SELECT '2/16/2000', 'W' UNION ALL
SELECT '2/19/2000', 'L' UNION ALL
SELECT '2/25/2000', 'L' UNION ALL
SELECT '2/28/2000', 'L' UNION ALL
SELECT '3/15/2000', 'L' UNION ALL
SELECT '3/19/2000', 'W' UNION ALL
SELECT '3/25/2000', 'W'
For this to...
This algorithm requires an existing Prime numbers table. You can easily create one of your own or importing the primes ranging from 2 to 3,037,000,493 from the Internet. If you only is interested in primes with INT range {2..2,147,483,647} you only need the first 4,792 primes {2..46,337}
DECLARE @Number BIGINT
SET @Number = 2020208534430421
SELECT Prime AS Number,
CAST(1 AS TINYINT) AS Items
INTO #Temp
FROM Primes
WHERE Prime <= SQRT(@Number)
AND @Number % Prime = 0
SELECT @Number = @Number / Number
FROM #Temp
WHILE @@ROWCOUNT > 0
UPDATE #Temp
SET Items = Items + 1,
@Number = @Number / Number
WHERE @Number % Number = 0
SELECT Number,
Items
FROM #Temp
UNION ALL
SELECT @Number,
1
WHERE @Number >...
Today I am going to show a statement available with SQL Server 2008 and later, the MERGE command, and how it will be possible to insert into two tables within same statement. Right now, I can't see a practical use, but that might change in the future. As long as I remember this blog post exists...
DECLARE @Source TABLE
(
a INT,
b INT
)
INSERT @Source
SELECT 11, 21 UNION ALL
SELECT 12, 22
DECLARE @PrimaryTarget TABLE
(
a INT
)
DECLARE @SecondaryTarget TABLE
(
b INT
)
MERGE @PrimaryTarget AS pt
USING @Source AS s ON 1 = 1
WHEN NOT MATCHED BY TARGET
THEN INSERT (
a
)
VALUES (
s.a
)
OUTPUT s.b
INTO @SecondaryTarget;
SELECT 'Source' AS TableName,
a,
b
FROM ...
Here is a simple base converter that manages [from and to] base 2 to 16.
CREATE FUNCTION dbo.fnBaseConvert
(
@Value VARCHAR(8),
@FromBase TINYINT,
@ToBase TINYINT
)
RETURNS VARCHAR(32)
AS
BEGIN
RETURN (
SELECT SUBSTRING('0123456789abcdef', 1 +(x.theValue % CAST(POWER(CAST(@ToBase AS FLOAT), v.Number + 1) AS BIGINT)) / CAST(POWER(CAST(@ToBase AS FLOAT), v.Number) AS BIGINT), 1)
FROM (
SELECT SUM((CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef') - 1) * CAST(POWER(CAST(@FromBase AS FLOAT), Number) AS BIGINT)) AS theValue
FROM master..spt_values
WHERE Type = 'P'
AND Number < LEN(@Value)
HAVING MIN(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) > 0
AND MAX(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) <= @FromBase
) AS x
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE x.theValue >= 0
AND v.Number < CEILING(0.00000005 + LOG(COALESCE(NULLIF(x.theValue,...
Late this evening I stumbled across a post where OP wanted to get all IDENTITY values for the latest INSERT statement, and correctly OP stated that SCOPE_IDENTITY() only returns last IDENTITY value of the batch.
Well, since OUTPUT is not an option for SQL Server 2000, and if you don't want to include temp tables or other means, this is one way how to solve this issue.
The algorithm relies in implicit transaction; if SPID 60 inserts 5000 records exactly the same time as SPID 61 inserts another 7500 records, the identity values for each spid doesn't get interleaved.
CREATE TABLE #Sample
(
...
Since this blog post http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx I have kept the technique in similar situations and perhaps never bothered to rethink my prerogative.
Well, today I have. Recently I blogged here http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx about finding records in one table which are missing in another table. In that blog post, I noticed that NOT EXISTS were the better choice for all scenarios, and have learnt to adopt the NOT EXISTS approach.
Why haven't I extended that knowledge to the "Certain status" scenario? I can't tell. And that is a good thing.
Because being active on a good forum now and then makes you rethink your positions. As I did today.
Here is the test case from which...
Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx is a specialized version for a month, and here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx is a specialized version for a year.
This code below calculates the number of weekdays for any given period.
CREATE FUNCTION dbo.fnPeriodWeekdays
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
RETURN (
DATEDIFF(DAY, @StartDate, @EndDate) + 1
+ (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2
- (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2
- CASE DATEDIFF(DAY, '17530101', @StartDate) % 7
WHEN 6 THEN 1
ELSE 0
END
- CASE DATEDIFF(DAY, '17530101', @EndDate) % 7
WHEN 5 THEN 1
ELSE 0
END
)
END
Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period.
CREATE FUNCTION dbo.fnMonthWeekDays
(
@Year SMALLINT,
@Month TINYINT
)
RETURNS TINYINT
AS
BEGIN
RETURN (
SELECT 20 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)
FROM (
SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -1) AS dt
WHERE ISDATE(10000 * @Year + 100 * @Month + 31) = 1
UNION ALL
SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -2)
WHERE ISDATE(10000 * @Year + 100 * @Month + 30) = 1
UNION ALL
SELECT CASE ISDATE(10000 * @Year + 100 * @Month + 29)
WHEN 1 THEN DATEADD(MONTH,...
This function calculates the number of weeksdays in a year, and has error-checking for invalid years.
It is also language independant.
Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period.
CREATE FUNCTION dbo.fnYearWeekDays
(
@Year SMALLINT
)
RETURNS SMALLINT
AS
BEGIN
RETURN (
SELECT 260 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)
FROM (
SELECT DATEADD(YEAR, @Year - 1899, -1) AS dt
UNION ALL
SELECT DATEADD(YEAR, @Year - 1899, -2)
WHERE ISDATE(10000 * @Year + 229) = 1
) AS d
WHERE @Year BETWEEN 1753 AND 9999
)
END
It seems JDEdwards dates are stored NUMERIC(6, 0) in this format 107299, where 107 is the number of years after 1900, and 299 is the 299th day of the year.
So how to convert JDEdwards to DATETIME?
DECLARE @jde NUMERIC(6, 0)
SET @jde = 107299
SELECT @jde,
DATEADD(DAY, @jde % 1000, DATEADD(YEAR, @jde / 1000, -1))
And how to go from DATETIME to JDEdwards?
DECLARE @Date DATETIME
SET @Date = '20041117'
SELECT @Date,
1000 * DATEDIFF(YEAR, 0, @Date) + DATEPART(DAYOFYEAR, @Date)
Recently I posted a function which returned the Nth weekday of a month, either from the beginning of month or from the end of month. Function is found here http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx
I have fiddled around with it and have now extended the function to find the Nth weekday not only for a month, but also for a quarter or a year. Below is the function.
CREATE FUNCTION dbo.fnGetNthWeekdayOfPeriod
(
@theDate DATETIME,
@theWeekday TINYINT,
@theNth SMALLINT,
@theType CHAR(1)
)
RETURNS DATETIME
BEGIN
RETURN (
SELECT theDate
FROM (
SELECT DATEADD(DAY, theDelta +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', theFirst) % 7) % 7, theFirst) AS theDate
FROM (
SELECT CASE UPPER(@theType)
WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')
WHEN 'Q'...
You call this function with a date. The function returns a table with one record and 3 columns.
First column is Weekday; Monday = 1, Tuesday = 2, Wednesday = 3; Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.
Second column is number of occurencies of that date since beginning of selected period type.
Third columns is number of occurencies left of that period type.
CREATE FUNCTION dbo.fnGetWeekdayAndNths
(
@theDate DATETIME,
@theType CHAR(1)
)
RETURNS TABLE
AS
RETURN ( SELECT 1 + DATEDIFF(DAY, -53690, @theDate) % 7 AS theWeekday,
1 +(theDelta - 1) / 7 AS Beginning,
DATEDIFF(DAY, DATEADD(DAY, -1, thePeriod), @theDate) / 7 - 1...
You call this function with three parameters:
1. Any date of the month in question
2. The weekday to calculate; Monday 1, Tuesday 2, Wednesday 3, Thursday 4, Friday 5, Saturday 6 and Sunday 7
3. The choice of weekday count; a positive number means from the beginning of month and a negative number means from the end of month
If a valid date cannot be calculated, NULL is returned. For an extended version which, besides month, also handles quarter and year, see new blog post here
http://weblogs.sqlteam.com/peterl/archive/2009/06/18/Extended-Get-Nth-Weekday-of-period.aspx
CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth
(
@theDate DATETIME,
@theWeekday TINYINT,
@theNth SMALLINT
)
RETURNS DATETIME
BEGIN
RETURN (
SELECT theDate
FROM (
SELECT DATEADD(DAY, 7 * @theNth...
I did some tests today to measure the different approaches for finding records present in one table but not in another. The results of CPU and Duration are presented below with some help from SQL Profiler.
Number of reads are equal between methods but different depending how many record in #TableB.
If there are other methods I haven't included, please let me know.
Method TableA TableB CPU Duration
---------- ------- ------- --- --------
GROUP BY 1000000 1000000 748 754
LEFT JOIN 1000000 1000000 328 321
NOT EXISTS 1000000 1000000 265 288
NOT IN 1000000 1000000 296 293
EXCEPT 1000000 1000000 312 288
GROUP BY 1000000 500000 577 2984
LEFT JOIN 1000000 500000...
For some time ago, I posted an algorithm how to get the XML structure automatically. Today I stumbled across another approach which seems to be faster. Reservations though I haven't tested this against large xml data yet.
However, the previous algorithm relied on a WHILE loop here
http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx
but this new algorithm doesn't. It's all xml internal thingies going on.
DECLARE @Nodes TABLE
(
NodeID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ParentNodeName NVARCHAR(64),
NodeName NVARCHAR(64)
)
DECLARE @Data XML
SET @Data = '
<root>
<elementGroup>
<element>
<stuff>
<comment>Stuff comment</comment>
</stuff>
<comment>Element comment</comment>
</element>
<comment>Element group comment</comment>
</elementGroup>
<comment>Root comment</comment>
</root>'
INSERT @Nodes
(
ParentNodeName,
NodeName
)
SELECT e.value('local-name(..)[1]', 'VARCHAR(MAX)') AS ParentNodeName,
...
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
)...
I was browsing some of my older solutions, and I stumbled across this interesting piece.
The object was to mark any employees with Duty 'B' for those employees having both Duty 'O' and Duty 'D'.
DECLARE @Roles TABLE (Person VARCHAR(5), [Role] VARCHAR(1))
INSERT @Roles
SELECT 'Smith', 'O' UNION ALL
SELECT 'Smith', 'D' UNION ALL
SELECT 'Jones', 'O' UNION ALL
SELECT 'White', 'D' UNION ALL
SELECT 'Brown', 'X'
SELECT Person,
SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty
FROM @Roles
WHERE [Role] IN ('D', 'O')
GROUP BY Person
This approach is not dependent on the 32-level recursion as most other algorithms are for this problem.
CREATE FUNCTION dbo.fnGCD
(
@a INT,
@b INT
)
RETURNS INT
AS
BEGIN
DECLARE @c INT
IF @a IS NULL OR @b IS NULL OR (@a = 0 AND @b = 0)
RETURN NULL
IF @a = 0 OR @b = 0
RETURN ABS(@a) + ABS(@b)
IF ABS(@a) < ABS(@b)
SELECT @c = ABS(@a),
@a = ABS(@b),
@b = @c
ELSE
SELECT @a = ABS(@a),
@b = ABS(@b)
SET @c = @a % @b
WHILE @c > 0
SELECT @a = @b,
@b = @c,
@c = @a %...
Today I am going to show you one way to calculate a decimal year. This is not a easy as it sounds because some years (leap years) include a leap day so the number of days in a year is not consistent.
The longest defined consistent time period there is, is a week. A week is seven days or 168 hours. However, a week can be defined to have different start weekday in different regions of the world. Enough said about that.
First, set up a test environment by declaring and setting two datetime variables like this
declare @f datetime...
In same cases, truncation of a long string is necessary. In most cases you just need to truncate it at the spot and have every section be exactly the same length, like this:
DECLARE @String VARCHAR(MAX),
@Size TINYINT
SELECT @String = 'Hello my name is Jeff. I need some help on a project because ',
@String = @String + 'right now this is how the application i am working ',
@String = @String + 'with displays data.',
@Size = 32
SELECT 1 + Number AS Part,
SUBSTRING(@String, Number * @Size, @Size)
FROM master..spt_values
WHERE Type = 'P'...
CREATE FUNCTION dbo.DATEDIFF360 ( @source DATETIME, @target DATETIME, @style BIT = 0 ) RETURNS INT AS BEGIN RETURN CASE @style -- European style WHEN 1 THEN CASE WHEN DATEPART(DAY, @target) = 31...
I previously posted a solution for one variable here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx and here is a solution for using two variables.
CREATE FUNCTION dbo.fnErf
(
@z1 FLOAT,
@z2 FLOAT,
@MaxIterations TINYINT = 10
)
RETURNS FLOAT
AS
BEGIN
IF @z1 IS NULL OR @z2 IS NULL
RETURN NULL
DECLARE @n TINYINT,
@s1 FLOAT,
@s2 FLOAT,
@p1 FLOAT,
@p2 FLOAT,
@a1 FLOAT,
@a2 FLOAT
SELECT @n = 1,
@p1 = 1,
@p2 = 1,
@a1 = @z1,
@a2 = @z2,
@MaxIterations = COALESCE(ABS(@MaxIterations), 10)
WHILE @p1 <> 0.0E AND @p2 <> 0.0E AND @n <= @MaxIterations
SELECT @s1 = @z1 /(2.0E * @n + 1.0E),
@s2 = @z2 /(2.0E *...
This Excel ERF clone works for with SQL Server 2000 and later. I have found that 10 iterations will give enough acccuracy (maximum float accuracy) in most cases, so you can call the function with
SELECT dbo.fnErf(0.35, DEFAULT)
However, if you feel the need to more accuracy, replace second parameter with a value of your choice.
Here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone-for-two-variables.aspx is also a solution for two variables.
CREATE FUNCTION dbo.fnErf
(
@z FLOAT,
@MaxIterations TINYINT = 10
)
RETURNS FLOAT
AS
BEGIN
IF @z IS NULL
RETURN NULL
DECLARE @n TINYINT,
@s FLOAT,
@p FLOAT,
@a FLOAT
SELECT @n = 1,
@p = 1,
@a = @z,
@MaxIterations = COALESCE(ABS(@MaxIterations), 10)
WHILE @p <> 0.0E AND @n <= @MaxIterations
SELECT @s = @z /(2.0E * @n...
Today I am going to talk about how to efficiently reuse identity values in a column, even if this is something that normally not should be bothered. The reason for this solution was a request for help from a member here on SQLTeam, who was near run out of identity values.
I did some reasearch first to see which was the most common method to deal with this situation and not surprisingly the method of iterating all records from start to end was used.
That method is not efficient. What if you have 1 million records and there is only 1 gap at...
Today I am going to write about how to extract the XML structure from a file. The basic idea is to bulk read the file from disk, place the content in an XML variable and traverse elements in the variable and ultimately output a resultset showing the structure of xml file.
I often use this to determine what kind of XML the file is by comparing the returned resultset with a lookup-table.
See comment in code to understand what happens
CREATE PROCEDURE dbo.uspGetFileStructureXML
(
@FileName NVARCHAR(256)
)
AS
-- Prevent unwanted resultsets back to client
SET NOCOUNT ON
-- Initialize...
CREATE FUNCTION dbo.fnIsLeapYear
(
@Year SMALLINT
)
RETURNS BIT
AS
BEGIN
RETURN CASE DATEPART(DAY, DATEADD(YEAR, @Year - 1904, '19040229'))
WHEN 29 THEN 1
ELSE 0
END
END
Some time ago, I wrote this article about how DATEDIFF works.
http://www.sqlteam.com/article/datediff-function-demystified
At the end I suggested two functions to calculate the number of months according to how human mind works.
At the discussion later, a person notified me that it calculated the wrong number of months if you tried January 29th 2009 to February 28th 2009.
The day is still greater, but you cannot have more days in february 2009 than 28.
These are improved functions that also deals with those situations.
CREATE FUNCTION [dbo].[fnMonthsApart]
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CASE
WHEN @ToDate < DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1
ELSE DATEDIFF(MONTH,...
In the past I have given the advice to break down all date intervals into the smallest part, most often minutes.
Then OP should group by the minute.
-- Prepare sample data
DECLARE @Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
CreateDate DATETIME,
DeleteDate DATETIME
)
-- Populate sample data
INSERT @Data
(
CreateDate,
DeleteDate
)
SELECT '2009-01-14 22:33', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:33', '2009-01-14 22:33' UNION ALL
SELECT '2009-01-14 22:34', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:35', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:35', '2009-01-14 22:36' UNION ALL
SELECT '2009-01-14 22:37', '2009-01-14 22:37' UNION ALL
SELECT '2009-01-14 22:39', '2009-01-14 22:39' UNION ALL
SELECT '2009-01-14 22:38', '2009-01-14 22:38' UNION...
-- Prepare sample data
DECLARE @Sample TABLE
(
GalaxyID INT,
ObjectID VARCHAR(16),
ObjectType VARCHAR(5),
ObjectTitle VARCHAR(200)
)
INSERT @Sample
SELECT 1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL
SELECT 1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL
SELECT 1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL
SELECT 1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL
SELECT 2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL
SELECT 2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL
SELECT 2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL
SELECT 2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL
SELECT 3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL
SELECT 3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL
SELECT 3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL
SELECT 3, 'T011820001524538', 'Topic', 'Drivers...
SELECT d.theDate,
DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend,
1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday
FROM (
SELECT CAST('20081124' AS SMALLDATETIME) AS theDate UNION ALL
SELECT '20081125' UNION ALL
SELECT '20081126' UNION ALL
SELECT '20081127' UNION ALL
SELECT '20081128' UNION ALL
SELECT '20081129' UNION ALL
SELECT '20081130'
) AS d
CREATE FUNCTION dbo.fnResolveFractionals
(
@data VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
RETURN CASE
WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.0E * SUBSTRING(@data, CHARINDEX(' ', @data) + 1, CHARINDEX('/', @data) - CHARINDEX(' ', @data) - 1) / NULLIF(RIGHT(@data, LEN(@data) - CHARINDEX('/', @data)), 0)
WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 0 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 0 THEN CAST(@data AS FLOAT)
ELSE NULL
END
END
GO
DECLARE @Sample TABLE
(
data VARCHAR(20)
)
INSERT @Sample
SELECT '5 3/16' UNION ALL
SELECT '7' UNION ALL
SELECT '2 /' UNION ALL
SELECT ...
DECLARE @Sample TABLE
(
StudentID INT,
Class VARCHAR(20)
)
INSERT @Sample
SELECT 1, 'Maths' UNION ALL
SELECT 1, 'English' UNION ALL
SELECT 1, 'Science' UNION ALL
SELECT 2, 'Maths' UNION ALL
SELECT 2, 'English' UNION ALL
SELECT 2, 'Science' UNION ALL
SELECT 2, 'History' UNION ALL
SELECT 3, 'English' UNION ALL
SELECT 3, 'Maths' UNION ALL
SELECT 3, 'Science' UNION ALL
SELECT 3, 'RE' UNION ALL
SELECT 4, 'Science' UNION ALL
SELECT 4, 'Maths' UNION ALL
SELECT 4, 'English' UNION ALL
SELECT 4, 'History' UNION ALL
SELECT 4, 'French'
;WITH Yak(ClassName, ClassPath, Combinations)
AS (
SELECT Class,
CAST(Class AS VARCHAR(MAX)),
CAST(1 AS INT)
FROM @Sample
GROUP BY Class
UNION ALL
SELECT s.Class,
y.ClassPath + '-' + s.Class,
y.Combinations + 1
FROM Yak AS y
INNER JOIN @Sample AS s ON s.Class > y.ClassName
...
Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table.
The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources.
And today I saw this type of question again (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114970) and I started to think if there actually were an alternative to IDENTITY column for their cases. I found a solution. I can't tell if it's elegant or not, but it work 100%.
If you are using SQL Server 2005 or later, you can stop reading here because Michael Valentine Jones has...
Today I come across an interesting approach to a networking algorithm. The question was about how to use recursive to expand a network and still avoid circular reference.
See topic here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115290
You can’t do that in a recursive CTE because you can only reference the CTE once in the recursive part. Then I thought about a “recursive csv string”. And I gave it a try.
Here is the result.
DECLARE @Stations TABLE
(
stationID INT,
name VARCHAR(255)
)
INSERT @Stations
SELECT 1, 'Glasgow' UNION ALL
SELECT 2, 'Edinburgh' UNION ALL
SELECT 3, 'York' UNION ALL
SELECT 4, 'London' UNION ALL
SELECT 5, 'Aberdeen' UNION ALL
SELECT 6, 'Bjuv'
DECLARE @Links TABLE
...
If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously.
What if you want to wait for the job to finished?
Try this
CREATE PROCEDURE dbo.usp_Start_And_Wait_For_Job
(
@jobName SYSNAME
)
AS
SET NOCOUNT ON
DECLARE @jobID UNIQUEIDENTIFIER,
@maxID INT,
@status INT,
@rc INT
IF @jobName IS NULL
BEGIN
RAISERROR('Parameter @jobName have no value.', 16, 1)
RETURN -100
END
SELECT @jobID = job_id
FROM msdb..sysjobs
WHERE name = @jobName
IF @@ERROR <> 0
BEGIN
RAISERROR('Error when returning jobID for job %s.', 18, 1, @jobName)
RETURN -110
END
IF @jobID IS NULL
BEGIN
RAISERROR('Job %s does not exist.', 16, 1, @jobName)
RETURN -120
END
SELECT @maxID...
Yesterday I came across this question on another forum.
I am trying to come up with a way to identify the half hour impact from
several exceptions across multiple days. I have access to SQL 2000 and SQL 2005.
The fields that are used would be a start moment ( 9/3/2008 3:45:00 PM),
stop moment (9/3/2008 4:30:00 PM), and total minutes (45). I have hundreds of exceptions with the above data and I need to identify the half hourly impact.
When I have one exception with a start time of 7:15 and an end time of 8:20,
I would like to see...
A good article about finding streaks in your data is this article by MVP Jeff Smith http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data.
Also see http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx
And this is an alternative way to find your streaks
-- Prepare sample data
SET NOCOUNT ON
DECLARE @GameResults TABLE
(
gameID INT,
homeScore INT,
awayScore INT
)
INSERT @GameResults
SELECT 1, 2, 1 UNION ALL
SELECT 2, 4, 1 UNION ALL
SELECT 3, 4, 3
DECLARE @Program TABLE
(
gameID INT,
gameDate DATETIME,
homeID INT,
awayID INT
)
INSERT @Program
SELECT 1, '2008-05-12', 101, 102 UNION ALL
SELECT 2, '2008-05-20', 106, 101 UNION ALL
SELECT 3, '2008-05-14', 107, 101
-- Prepare staging data
DECLARE @Stage TABLE
(
teamID INT,
gameDate DATETIME,
outcome CHAR(3),
streak INT,
...
-- Prepare sample data
DECLARE @Sample TABLE
(
ID INT,
col INT
)
INSERT @Sample
SELECT 0, 1 UNION ALL
SELECT 0, 1 UNION ALL
SELECT 0, 2 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 5, 8 UNION ALL
SELECT 5, 9 UNION ALL
SELECT 4, 7
-- Pivot the source data
SELECT ID,
MIN(col) AS col1,
CASE COUNT(*)
WHEN 1 THEN NULL
WHEN 2 THEN MAX(col)
ELSE SUM(col) - MIN(col) - MAX(col)
END AS col2,
CASE COUNT(*)
WHEN 3 THEN MAX(col)
...
See his blog entry here http://www.sqlmag.com/Article/ArticleID/100884/sql_server_100884.html
And this older http://www.sqlmag.com/Article/ArticleID/95734/sql_server_95734.html
With respect to my old algorithm found here http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx which is about how to sum up an unknown number of items, I have come up with a new algorithm.
This new algorithm is about finding all possible sums and how many combinations you have of each sum.
Have fun!
DECLARE @Data TABLE
(
faceValue MONEY,
maxItems INT,
permCount INT
)
INSERT @Data
(
faceValue,
maxItems
)
SELECT faceValue,
1 + COUNT(*)
FROM (
SELECT 899 AS faceValue UNION ALL
SELECT 100 UNION ALL
SELECT 95 UNION ALL
SELECT 50 UNION ALL
SELECT 55 UNION ALL
SELECT 40 UNION ALL
SELECT 5 UNION ALL
SELECT 100 UNION...
IF EXISTS(SELECT * FROM YourTable WHERE Number = 0)
SELECT 0.0E
ELSE
SELECT CASE IsNegativeProduct
WHEN 1 THEN -EXP(theSum)
ELSE EXP(theSum)
END
FROM (
SELECT SUM(LOG(ABS(Number))) AS theSum,
SUM(CASE WHEN Number < 0 THEN 1 ELSE 0 END) % 2 AS IsNegativeProduct
FROM YourTable
) AS d
SELECT *
FROM ::fn_dblog(DEFAULT, DEFAULT) AS l
INNER JOIN sysobjects AS so ON so.name = l.[transaction name]
SELECT so.name AS ObjectName,
so.type AS ObjectType,
MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime
FROM ::fn_dblog(DEFAULT, DEFAULT) l
inner join sysobjects so on so.name = l.[transaction name]
--where so.type = 'u'
GROUP BY so.name,
so.type
ORDER BY so.name,
so.type
DECLARE @Value NVARCHAR(200)
SET @Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff'
WHILE @Value LIKE '%[^0-9]%'
SET @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '')
SELECT @Value
SELECT GETDATE() AS theFullDateTime,
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly,
DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly
SELECT GETDATE() AS theFullDateTime,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly,
DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly
Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item.
And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking.
Well, have you looked a Bayesian Estimate?
DECLARE @Sample TABLE
(
userID INT,
vote INT
)
INSERT @Sample
SELECT 3, 40 UNION ALL
SELECT 3, 60 UNION ALL
SELECT 0, 100 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 1, 100 UNION ALL
SELECT...
DECLARE @s VARCHAR(100)
SET @s = 'aardddvaaaarrkkkk'
-- Highest frequency of same character
SELECT TOP 1 WITH TIES
[char],
COUNT(*) AS cnt
FROM (
SELECT SUBSTRING(@s, 1 + Number, 1) [char]
FROM master..spt_values
WHERE Number < DATALENGTH(@s)
AND type = 'P'
) AS q
GROUP BY [char]
ORDER BY COUNT(*) DESC
-- Longest sequence of same character
SELECT TOP 1 WITH TIES
[char],
CASE [seq]
WHEN 0 THEN DATALENGTH(@s) - Number
ELSE [seq]
END AS [seq]
FROM (
SELECT SUBSTRING(@s, 1 + Number, 1) [char],
Number,
PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) AS [seq]
FROM master..spt_values
WHERE Number < DATALENGTH(@s)
AND type = 'P'
)...
SELECT number,
CASE
WHEN number % 100 IN (11, 12, 13) THEN 'th'
WHEN number % 10 = 1 THEN 'st'
WHEN number % 10 = 2 THEN 'nd'
WHEN number % 10 = 3 THEN 'rd'
ELSE 'th'
END AS Ordinal
FROM master..spt_values
WHERE type = 'p'
ORDER BY number
Today I helped a guy out with a special request. His goal was to remove all records that are subsets or parts of another record, a superset record.
See this sample data
DECLARE @Sample TABLE
(
recID INT IDENTITY(1, 1),
col1 VARCHAR(1),
col2 VARCHAR(2),
col3 VARCHAR(3),
userID INT
)
INSERT @Sample
SELECT 'A', 'B', 'C', 1 UNION ALL
SELECT 'A', 'B', ' ', 1 UNION ALL
SELECT 'A', ' ', 'C', 1 UNION ALL
SELECT 'F', ' ', 'C', 1 UNION ALL
SELECT ' ', 'M', ' ', 2 UNION ALL
SELECT 'T', 'M', 'O', 2 UNION ALL
SELECT ' ', 'M', 'O', 2 UNION ALL
SELECT 'X', 'M', 'O', 2...
This is a piece of code I use to create a resultset from and display in Outlook calendar.
CREATE PROCEDURE dbo.uspGetScheduleTimes
(
@startDate DATETIME,
@endDate DATETIME
)
AS
/*
This code is blogged here
http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx
*/
SET NOCOUNT ON
-- Create a tally table. If you already have one of your own please use that instead.
CREATE TABLE #tallyNumbers
(
num SMALLINT PRIMARY KEY CLUSTERED
)
DECLARE @index SMALLINT
SET @index = 1
WHILE @index <= 8640
BEGIN
INSERT #tallyNumbers
(
num
)
VALUES (
@index
)
SET @index = @index + 1
END
-- Create a staging table for jobschedules
CREATE TABLE #jobSchedules
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
serverName SYSNAME NOT NULL,
...
This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008.
For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to.
With SQL Server 2005 SP2, the new VARDECIMAL datatype arrived, with a few stored procedures to calculate eventual savings. This turned out to be a subset for the ROW and PAGE compressions available in SQL Server 2008, Enterprise Edition. You can have ROW compression only if you want, but if you choose PAGE compression you...
DECLARE @a TABLE (pk INT)
DECLARE @b TABLE (fk INT, i INT)
DECLARE @c TABLE (fk INT, j INT)
INSERT @a
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
INSERT @b
SELECT 1, 1 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 8 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 1
INSERT @c
SELECT 1, 11 UNION ALL
SELECT 1, 13 UNION ALL
SELECT 2, 14 UNION ALL
SELECT 2, 18 UNION ALL
SELECT 2, 60 UNION ALL
SELECT 3, 11
-- Wrong way
SELECT a.pk,
SUM(b.i) AS SumAct,...
CREATE PROCEDURE dbo.uspCreatePassword ( @UpperCaseItems SMALLINT, @LowerCaseItems SMALLINT, @NumberItems SMALLINT, @SpecialItems SMALLINT ) AS SET NOCOUNT ON -- Initialize some variables DECLARE @UpperCase VARCHAR(26), @LowerCase VARCHAR(26), @Numbers VARCHAR(10), @Special...
-- Prepare sample data DECLARE @Keywords TABLE ( FaqID INT, Keyword VARCHAR(200) ) INSERT @Keywords SELECT 1, 'help' UNION ALL SELECT 1, 'resolve' UNION ALL SELECT 1, 'issue' UNION ALL ...
CREATE FUNCTION dbo.fnExtractPostCodeUK
(
@Data VARCHAR(8000)
)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN COALESCE(
-- AANN NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),
-- AANA NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),
-- ANN NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),
-- AAN NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),
-- ANA NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),
-- AN NAA
SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' '...
CREATE FUNCTION dbo.fnValidatePostCodeUK
(
@PostCode VARCHAR(8)
)
RETURNS BIT
AS
BEGIN
RETURN CASE
-- Special case GIR 0AA
WHEN @PostCode LIKE 'GIR 0AA' THEN 1
-- Current postcode prefixes
WHEN LEFT(@Postcode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP',...
-- Initialize the search parameter
DECLARE @WantedValue INT
SET @WantedValue = 221
-- Stage the source data
DECLARE @Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1
)
-- Aggregate the source data
INSERT @Data
(
MaxItems,
FaceValue
)
SELECT COUNT(*),
Qty
FROM (
SELECT 899 AS Qty UNION ALL
SELECT 100 UNION ALL
SELECT 95 UNION ALL
SELECT 50 UNION ALL
SELECT 55 UNION ALL
SELECT 40 UNION ALL
SELECT 5 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
...
This problem originated here
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y
and I post the solution here for two reasons.
1) The forum above does not support code tags
2) The common interest is high enough
-- Prepare sample data
DECLARE @Sample TABLE
(
HoleID CHAR(8),
mFrom SMALLMONEY,
mTo SMALLMONEY,
Result SMALLMONEY,
PRIMARY KEY CLUSTERED
(
HoleID,
mFrom
),
Seq INT
)
INSERT @Sample
(
HoleID,
mFrom,
mTo,
Result
)
SELECT 'TWDD0004', 1 , 2 , 0.86
UNION ALL
SELECT 'TWDD0004', 3 , 4 , 8.93
UNION ALL
SELECT 'TWDD0004', 4 , 5 , 2.78
UNION ALL
SELECT 'TWDD0004', 8 , ...
SELECT Number,
1 - SIGN(Number & (Number - 1))
FROM master..spt_values
WHERE Type = 'P'
AND Number > 0
Other way is
CREATE FUNCTION dbo.isPowerOf2
(
@i INT
)
RETURNS BIT
AS
BEGIN
DECLARE @x FLOAT
SET @x = LOG(Number) / LOG(2)
RETURN CASE
WHEN FLOOR(@x) = CEILING(@x) THEN 1
ELSE 0
END
END
I started out with typing
SELECT @@VERSION
and got the result as
Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86)
Dec 8 2007 18:51:32
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
So far so good. Now I created some sample data like this
-- Prepare sample data
DECLARE @Sample TABLE (RecNo TINYINT, ID TINYINT, EventDate SMALLDATETIME, OrderStatus VARCHAR(9))
INSERT @Sample
SELECT 1, 10, '7/5/2008', 'Opened' UNION ALL
SELECT 2, 11, '7/5/2008', 'Closed' UNION ALL
SELECT 3, 12, '7/5/2008', 'Closed' UNION ALL
SELECT 4, 13, '7/6/2008', 'Opened' UNION ALL
SELECT 4, 14, '7/6/2008', 'Opened' UNION ALL
SELECT 4, 15, '7/6/2008', 'Closed' UNION ALL
SELECT 1, 16, '7/7/2008',...
CREATE FUNCTION dbo.fnGetSQLServerAuthenticationMode
(
)
RETURNS INT
AS
BEGIN
DECLARE @InstanceName NVARCHAR(1000),
@Key NVARCHAR(4000),
@LoginMode INT
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
N'MSSQLSERVER',
@InstanceName OUTPUT
IF @@ERROR <> 0 OR @InstanceName IS NULL
RETURN NULL
SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'LoginMode',
@LoginMode OUTPUT
RETURN @LoginMode
END
CREATE PROCEDURE dbo.uspSetSQLServerAuthenticationMode
(
@MixedMode BIT
)
AS
SET NOCOUNT ON
DECLARE @InstanceName NVARCHAR(1000),
@Key NVARCHAR(4000),
@NewLoginMode INT,
@OldLoginMode INT
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
N'MSSQLSERVER',
@InstanceName OUTPUT
IF @@ERROR <> 0 OR @InstanceName IS NULL
BEGIN
RAISERROR('Could not read SQL Server instance name.', 18, 1)
RETURN -100
END
SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'LoginMode',
@OldLoginMode OUTPUT
IF @@ERROR <> 0
BEGIN
RAISERROR('Could not read login mode for SQL Server instance %s.', 18, 1, @InstanceName)
RETURN -110
END
IF @MixedMode IS NULL
BEGIN
RAISERROR('No change to authentication mode was made. Login mode is %d.', 10, 1, @OldLoginMode)
RETURN -120
END
IF...
DECLARE @Stage TABLE
(
RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
Data VARCHAR(90),
Section INT
)
INSERT @Stage
(
Data
)
EXEC xp_cmdshell 'ipconfig /all'
DECLARE @Section INT
SET @Section = 0
UPDATE @Stage
SET @Section = Section = CASE
WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1
ELSE @Section
END
SELECT MAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header,
MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName,
MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState,
MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END)...
The last two days I have been involved in a rather interesting discussion.
The original poster wanted a fast way to get missing date ranges in a series of date pairs.
Naturally I posted the link to the Script Library topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88422
Traditional T-SQL proved to be very inefficient. Even when using the CTE approch which proved to be second fastest and still 100 to 1,600 times slower!
I started out with creating 1,000 date pairs with following code
-- Prepare sample data
CREATE TABLE #ProcessCellAllocation
(
AllocationID INT IDENTITY(1, 1) NOT NULL,
ProcessCell VARCHAR(50) NOT NULL,
DateFrom DATETIME NOT NULL,
DateTo DATETIME,
Seq INT
)
INSERT #ProcessCellAllocation
(
...
I just played around with some different techniques to fetch relevant data from XML content.
DECLARE @XMLString XML,
@Search VARCHAR(50)
SELECT @XMLString = '
<Customers>
<Customer>
<FirstName>Kevin</FirstName>
<LastName>Goff</LastName>
<City type="aca">Camp Hill</City>
</Customer>
<Customer>
<FirstName>Steve</FirstName>
<LastName>Goff</LastName>
<City type="acb">Philadelphia</City>
</Customer>
</Customers>',
@Search = 'Camp Hill'
-- Get all customers living in Camp Hill
SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,
cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,
cust.coldef.value('City[1]','VARCHAR(20)') AS City,
cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE cust.coldef.exist('City/text()[.= sql:variable("@Search")]') = 1
-- Get all customers living in a City containing the text "adel"
SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,
cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,
cust.coldef.value('City[1]','VARCHAR(20)') AS City,
cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE cust.coldef.exist('City [contains(.,"adel")]') = 1
-- Get...
This is an updated version for SQL 2005 and later to search all code for a specific keyword
SELECT p.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM (
SELECT OBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROM SYSOBJECTS AS so
WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHERE p.Body LIKE '%YourKeyWordHere%'
The types are
C = CHECK constraint
D = Default or DEFAULT constraint
FN = Scalar function
IF = In-lined table-function...
This is what I pondered about today. Maybe I also will have some time to test it.
CREATE PROCEDURE dbo.uspPaginate
(
@PageNumber INT,
@RecordsPerPage TINYINT = 50
)
AS
SET NOCOUNT ON
DECLARE @MaxRows INT
SET @MaxRows = @PageNumber * @RecordsPerPage
SELECT SomeColumns
FROM (
SELECT TOP (@RecordsPerPage)
SomeColumns
FROM (
SELECT TOP (@MaxRows)
SomeColumns
FROM YourTable
ORDER BY SomeCase ASC/DESC
)
ORDER BY SomeCase DESC/ASC
)
ORDER BY SomeCase ASC/DESC
Topic is here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97550
I worked with this topic recent weekend and posted the final functions here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454
The general idea is to have a generic purge functionality.
After a good nights sleep when almost all pieces fit together here
weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx
I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint.
It seems that identity values are assigned to complete insert-set before checking for constraints such us unique indexes.
DECLARE @Items TABLE (i INT IDENTITY(1, 1), j INT PRIMARY KEY)
INSERT @Items
SELECT 1 UNION ALL
SELECT 2
SELECT *
FROM @Items
INSERT @Items
SELECT 1
INSERT @Items
SELECT 2
INSERT @Items
SELECT 3
SELECT *
FROM @Items
CREATE FUNCTION dbo.fnSplitType
(
@Data VARCHAR(200),
@PartSize TINYINT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000),
@Alpha TINYINT,
@OldPosition SMALLINT,
@NewPosition SMALLINT
SELECT @Result = '',
@Alpha = 1,
@OldPosition = 1,
@NewPosition = 1
IF @Data LIKE '[0-9]%'
SELECT @Result = REPLICATE(' ', @PartSize),
@Alpha = 0
WHILE @NewPosition < LEN(@Data)
SELECT @NewPosition = CASE @Alpha
WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000))
ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000))
END,
@NewPosition = CASE @NewPosition
WHEN 0 THEN LEN(@Data)
ELSE @OldPosition + @NewPosition - 2
END,
@Result = @Result + CASE @Alpha
WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize)
ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize)
END,
@Alpha = 1 - @Alpha,
@OldPosition = @NewPosition + 1
RETURN RTRIM(@Result)
END
Here is...
Today, I was involved in an interesting discussion.
Someone asked for a moving average solution. I joined the discussion late.
The previous solutions and mine were all set-based and very slow. Then Jezemine come up with a CURSOR solution that looked fine and fast.
After some trial-and-errors I finally posted a set-based solution that seems to be the fastest solution yet.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911
So it still seems that [proper implemented] set-based solutions are the fastest, compared to CURSORs.
Below is also an implementation of a SQL Server 2005 approach.
DECLARE @Sample TABLE (dt DATETIME, Rate FLOAT)
INSERT @Sample
SELECT CURRENT_TIMESTAMP - 10, 1 UNION ALL
SELECT CURRENT_TIMESTAMP - 9, 2 UNION ALL
SELECT CURRENT_TIMESTAMP - 8, 4...
In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 I gave a suggestion how to get a more detailed error message when working with OPENROWSET.
But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods.
The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors.
-- Using this code for a file with no appropriate permissions throws a general error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\datastorage\excel\book2.xls', 'select * from [Sheet1$a1:q50]')
If you have some error and you don't understand why, try using MSDASQL provider temporarily.
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\datastorage\excel\book2.xls',
...
DECLARE @Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200))
INSERT @Sample
SELECT '123', '125,124,126' UNION ALL
SELECT '124', '127,21,245'
--SELECT Col1,
-- Data
--FROM @Sample
--CROSS APPLY fnParseList(',', Col3)
SELECT a.Col1,
SUBSTRING(',' + a.Col3 + ',', n.Number + 1, CHARINDEX(',', ',' + a.Col3 + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM @Sample AS a
INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.Col3 + ',', n.Number, 1) = ','
WHERE n.Type = 'p'
AND n.Number > 0
AND n.Number < LEN(',' + a.Col3 + ',')
-- Prepare sample data
DECLARE @Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11))
INSERT @Accounts
SELECT '100-000-000', NULL UNION ALL
SELECT '100-001-000', '100-000-000' UNION ALL
SELECT '100-002-000', '100-000-000' UNION ALL
SELECT '100-002-001', '100-002-000' UNION ALL
SELECT '100-002-002', '100-002-000'
DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY)
INSERT @Transactions
SELECT '100-001-000', 1000.00 UNION ALL
SELECT '100-002-001', 500.00 UNION ALL
SELECT '100-002-002', 300.00
-- Setup staging expression
;WITH Yak (AccountNumber, Amount)
AS (
SELECT AccountNumber,
SUM(Amount) AS Amount
FROM @Transactions
GROUP BY AccountNumber
UNION ALL
SELECT a.ParentAccountNumber,
y.Amount
FROM @Accounts AS a
INNER JOIN Yak AS y ON y.AccountNumber = a.AccountNumber
)
-- Show the expected resultset
SELECT COALESCE(AccountNumber, 'All accounts') AS AccountNumber,
SUM(Amount) AS Amount
FROM Yak
WHERE AccountNumber IS NOT NULL
GROUP BY AccountNumber
ORDER BY CASE
WHEN AccountNumber...
Let’s play with the new OUTPUT operator!
-- Setup TableA & TableB
CREATE TABLE #TableA
(
i INT
)
CREATE TABLE #TableB
(
i INT
)
CREATE TABLE #TableC
(
iOld INT,
iNew INT
)
-- Check TableA and TableB
SELECT 'A' AS [Table], * FROM #TableA
UNION ALL
SELECT 'B' AS [Table], * FROM #TableB
-- Insert into TableA
INSERT #TableA
OUTPUT inserted.i
INTO #TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
-- Check TableA and TableB
SELECT 'A' AS [Table], * FROM #TableA
UNION ALL
SELECT 'B' AS [Table], * FROM #TableB
-- Delete from TableA
DELETE a
OUTPUT 10 * deleted.i + 49
INTO #TableB
FROM #TableA AS a
WHERE i = 2
-- Check TableA and TableB
SELECT 'A'...
Sometimes you have a denormalized table with several BIT columns used as flags.
Say you want to select every row that has at least one flag set. That's easy.
SELECT * FROM Table1 WHERE Flag1 = 1 OR Flag2 = 1 OR Flag3 = 1...
But how to easy select all records where all flags are not set?
SELECT * FROM Table1 WHERE Flag1 = 0 AND Flag2 = 0 AND Flag3 = 0...
That can be the way you normally write, and it can get very long!
But if you write like this instead to get all records where at least one flag is set
SELECT * FROM...
I was involved in a discussion today about the ISNUMERIC() function
Someone proposed a nested solution like this
SELECT Column_Name
from (
SELECT Column_Name
FROM (
select 'staff' as Column_Name union all
select '234000' as Column_Name union all
select '12d1' as Column_Name union all
select '45e0' as Column_Name union all
select '$123.45' as Column_Name union all
select '$12,345' as Column_Name
) as Table_Name
WHERE ISNUMERIC(Column_Name) = 1
) as d
where CAST(Column_Name AS INT) <= 1000000
But is does not work. I suggested an alternative method that seems to work.
SELECT d.Column_Name
from (
SELECT x.Column_Name
FROM (
select 'staff' as Column_Name union all
select '234000' union all
select '12d1' union...
Consider this test data
CREATE TABLE #Temp (ID INT, Directory TEXT)
INSERT #Temp
SELECT 1, 'Sports' UNION ALL
SELECT 2, 'Sports/Football' UNION ALL
SELECT 3, 'Sports/Football/American' UNION ALL
SELECT 4, 'Sports/Football/American/College_and_University' UNION ALL
SELECT 5, 'Sports/Football/American/College_and_University/NCAA_Division_III' UNION ALL
SELECT 6, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference' UNION ALL
SELECT 7, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference/Frostburg_State' UNION ALL
SELECT 8, 'Sports/Darts' UNION ALL
SELECT 9, 'Sports/Darts/Organizations' UNION ALL
SELECT 10, 'Sports/Darts/Organizations/United_States' UNION ALL
SELECT 11, 'Sports/Darts/Organizations/United_States/Alabama'
Say you want to return all records that are at least three directories down, ie having at least 2 dividers.
This is the most common way I have encountered
SELECT ID,
Directory
FROM #Temp
WHERE LEN(Directory) - LEN(REPLACE(Directory, '/', '')) >= 2
This works but have one drawback. It does not work on TEXT columns!
This is what I came up with today in this topic Is this possible?
SELECT...
|