-- Prepare sample data DECLARE@Sample TABLE ( ID INT, col INT ) INSERT@Sample SELECT0, 1 UNION ALL SELECT0, 1 UNION ALL SELECT0, 2 UNION ALL SELECT1, 1 UNION ALL SELECT1, 2 UNION ALL SELECT1, 3 UNION ALL SELECT2, 5 UNION ALL SELECT2, 5 UNION ALL SELECT2, 5 UNION ALL SELECT3, 6 UNION ALL SELECT3, 6 UNION ALL SELECT5, 8 UNION ALL SELECT5, 9 UNION ALL SELECT4, 7 -- Pivot the source data SELECTID, 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) ELSE NULL END AS col3 FROM@Sample GROUPBY ID ORDERBY ID
Legacy Comments
dineshrajan
2010-08-10
re: Easy sorted numeric pivoting with maximum three columns Good Post.
Read more →
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
Read more →
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.
Read more →
IFEXISTS(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
Read more →
SELECT* FROM::fn_dblog(DEFAULT, DEFAULT) AS l INNERJOIN sysobjects AS so ON so.name = l.[transaction name] SELECTso.name AS ObjectName, so.type AS ObjectType, MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime FROM::fn_dblog(DEFAULT, DEFAULT) l innerjoin sysobjects so on so.
Read more →
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
Legacy Comments
Uri Dimant
2008-11-12
re: Stripping out all non-numeric characters from a string declare @string varchar(200)
Read more →
SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly
Legacy Comments
Bill Curnow
2008-11-12
re: Getting date or time only from a Datetime value SELECT GETDATE() AS fullDateTime,
Read more →
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.
Read more →
DECLARE@s VARCHAR(100) SET@s = 'aardddvaaaarrkkkk' -- Highest frequency of same character SELECTTOP 1 WITH TIES [char], COUNT(*) AS cnt FROM ( SELECT SUBSTRING(@s, 1 + Number, 1) [char] FROM master.
Read more →
SELECTnumber, 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 FROMmaster.
Read more →
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
Read more →
This is a piece of code I use to create a resultset from and display in Outlook calendar.
CREATEPROCEDURE dbo.uspGetScheduleTimes ( @startDate DATETIME, @endDate DATETIME ) AS /* This code is blogged here http://weblogs.
Read more →
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.
Read more →
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
Read more →
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 VARCHAR(13), @Temp VARCHAR(8000), @Password VARCHAR(8000), @i SMALLINT, @c VARCHAR(1), @v TINYINT – Set the default items in each group of characters SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', @LowerCase = 'abcdefghijklmnopqrstuvwxyz', @Numbers = '0123456789', @Special = '!
Read more →
-- Prepare sample data DECLARE@Keywords TABLE ( FaqID INT, Keyword VARCHAR(200) ) INSERT@Keywords SELECT1, 'help' UNION ALL SELECT1, 'resolve' UNION ALL SELECT1, 'issue' UNION ALL SELECT2, 'Red Herring' DECLARE@Faq TABLE ( FaqID INT, Question VARCHAR(MAX), Answer VARCHAR(MAX) ) INSERT@Faq SELECT1, 'This is stupid question', 'This is a stupid answer' DECLARE@Question AS VARCHAR(200) -- Prepare user supplied parameter SET@Question = 'How can we help resolve your issue' -- Show the expected result SELECTf.
Read more →
CREATEFUNCTION dbo.fnExtractPostCodeUK ( @Data VARCHAR(8000) ) RETURNSVARCHAR(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] %', ' ' + @Data + ' '), 0), 6), -- Special case GIR 0AA SUBSTRING(@Data, NULLIF(PATINDEX('% GIR 0AA %', ' ' + @Data + ' '), 0), 7) ) END Here are some sample data to play with declare@TestTab Table (postcode varchar(50) not null) Insert@TestTab values('SK13 8LY') --Valid Insert@TestTab values('M1 1AA') --Valid Insert@TestTab values('M60 1NW') --Valid Insert@TestTab values('GIR 0AA') --Valid Insert@TestTab values('CR2 6XH') --Valid Insert@TestTab values('DN55 1PT') --Valid Insert@TestTab values('W1A 1HQ') --Valid Insert@TestTab values('EC1A 1BB') --Valid Insert@TestTab values('India') --Invalid Insert@TestTab values('12345') --Invalid Insert@TestTab values('Glossop SK13 8LY') --Invalid but contains a valid postcode INSERT@TestTab VALUES('XA1 1AA') --WHAT WILL THE FUNCTION SAY ABOUT THIS?
Read more →
CREATEFUNCTION dbo.fnValidatePostCodeUK ( @PostCode VARCHAR(8) ) RETURNSBIT AS BEGIN RETURN CASE -- Special case GIR 0AA WHEN @PostCode LIKE 'GIR 0AA' THEN 1 -- Current postcode prefixes WHENLEFT(@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', 'NR', 'NW', 'OL', 'OX', 'PA', 'PE', 'PH', 'PL', 'PO', 'PR', 'RG', 'RH', 'RM', 'SA', 'SE', 'SG', 'SK', 'SL', 'SM', 'SN', 'SO', 'SP', 'SR', 'SS', 'ST', 'SW', 'SY', 'TA', 'TD', 'TF', 'TN', 'TQ', 'TR', 'TS', 'TW', 'UB', 'WA', 'WC', 'WD', 'WF', 'WN', 'WR', 'WS', 'WV', 'YO', 'ZE') OR WHEN LEFT(@Postcode, 1) NOT IN('B', 'E', 'G', 'L', 'N', 'S', 'W') THEN 0 -- AANN NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- AANA NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- ANN NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- AAN NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- ANA NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- AN NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- Not a valid postcode ELSE 0 END END Updated to reflect current valid postcodes only.
Read more →
– 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 ) SELECTCOUNT(), Qty FROM( SELECT 899 AS Qty UNIONALL SELECT 100 UNION ALL SELECT 95 UNIONALL 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 SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 250 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 90 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 350 UNION ALL SELECT 450 UNION ALL SELECT 450 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 1 UNION ALL SELECT 10 UNION ALL SELECT 1 ) AS d GROUPBY Qty ORDERBY Qty DESC – Declare some control variables DECLARE@CurrentSum INT, @BestUnder INT, @BestOver INT, @RecID INT – If exact single wanted sum, select that item!
Read more →
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.
Read more →