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.
Read more →
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 ALL SELECT'2009-01-14 22:39', '2009-01-14 22:39' UNION ALL SELECT'2009-01-14 22:41', '2009-01-14 22:41' UNION ALL SELECT'2009-01-14 22:43', '2009-01-14 22:44' UNION ALL SELECT'2009-01-14 22:52', '2009-01-14 22:52' UNION ALL SELECT'2009-01-14 22:53', '2009-01-14 22:53' UNION ALL SELECT'2009-01-14 22:53', '2009-01-14 22:53' UNION ALL SELECT'2009-01-14 22:56', '2009-01-14 22:57' UNION ALL SELECT'2009-01-14 22:57', '2009-01-14 22:57' UNION ALL SELECT'2009-01-14 22:58', '2009-01-14 22:58' UNION ALL SELECT'2009-01-14 22:58', '2009-01-14 22:59' UNION ALL SELECT'2009-01-14 22:59', '2009-01-14 22:59' DECLARE@From INT, @To INT SELECT@From = MIN(DATEDIFF(MINUTE, 0, CreateDate)), @To = MAX(DATEDIFF(MINUTE, 0, DeleteDate)) FROM@Data SELECTw.
Read more →
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.
Read more →
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.
Read more →
-- 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 →
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 →
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 →
-- 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 →
– 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 →
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.
Read more →
CREATEPARTITION FUNCTION pfOrderDate ( DATETIME ) ASRANGE RIGHT FOR VALUES ( '20000101', '20010101', '20020101' ) GO CREATEPARTITION SCHEME psYak ASPARTITION pfOrderDate ALLTO ([PRIMARY]) GO CREATETABLE Orders ( OrderID INT NOT NULL, CustomerID VARCHAR(15) NOT NULL, OrderDate DATETIME NOT NULL ) ONpsYak(OrderDate) GO CREATECLUSTERED INDEX IX_OrderID ONOrders ( OrderID ) CREATENONCLUSTERED INDEX IX_OrderDate ONOrders ( OrderDate ) INCLUDE( OrderID, CustomerID ) GO INSERTOrders ( OrderID, CustomerID, OrderDate ) SELECT1, 'Peso', '20011225' UNIONALL SELECT2, 'Jennie', '20020314' SELECTOrderID, OrderDate FROMOrders WHEREOrderDate = '20011225' SELECTOrderID, OrderDate FROMOrders WHEREOrderID = 1 DROPTABLE Orders DROPPARTITION SCHEME psYak DROPPARTITION FUNCTION pfOrderDate
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
-- Prepare sample data DECLARE@Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11)) INSERT@Accounts SELECT'100-000-000', NULLUNION ALL SELECT'100-001-000', '100-000-000' UNIONALL SELECT'100-002-000', '100-000-000' UNIONALL SELECT'100-002-001', '100-002-000' UNIONALL SELECT'100-002-002', '100-002-000' DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY) INSERT@Transactions SELECT'100-001-000', 1000.
Read more →
Let’s play with the new OUTPUT operator!<o:p></o:p>
-- Setup TableA & TableB
CREATETABLE#TableA
(
i INT
)
CREATETABLE#TableB
(
i INT
)
CREATETABLE#TableC
Read more →