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 →
-- Prepare sample data DECLARE@Sample TABLE ( GalaxyID INT, ObjectID VARCHAR(16), ObjectType VARCHAR(5), ObjectTitle VARCHAR(200) ) INSERT@Sample SELECT1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL SELECT1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL SELECT1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL SELECT1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL SELECT2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL SELECT2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL SELECT2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL SELECT2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL SELECT3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL SELECT3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL SELECT3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL SELECT3, 'T011820001524538', 'Topic', 'Drivers of Change & Growth' -- Display the XML SELECTs.
Read more →
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
Legacy Comments
Uri Dimant
2009-01-14
re: Easy script for calculating weekday or weekend for a date Hi Peter.
Read more →
CREATEFUNCTION dbo.fnResolveFractionals ( @data VARCHAR(20) ) RETURNSFLOAT 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.
Read more →
DECLARE@Sample TABLE ( StudentID INT, Class VARCHAR(20) ) INSERT@Sample SELECT1, 'Maths' UNION ALL SELECT1, 'English' UNION ALL SELECT1, 'Science' UNION ALL SELECT2, 'Maths' UNION ALL SELECT2, 'English' UNION ALL SELECT2, 'Science' UNION ALL SELECT2, 'History' UNION ALL SELECT3, 'English' UNION ALL SELECT3, 'Maths' UNION ALL SELECT3, 'Science' UNION ALL SELECT3, 'RE' UNION ALL SELECT4, 'Science' UNION ALL SELECT4, 'Maths' UNION ALL SELECT4, 'English' UNION ALL SELECT4, 'History' UNION ALL SELECT4, 'French' ;WITHYak(ClassName, ClassPath, Combinations) AS ( SELECT Class, CAST(Class AS VARCHAR(MAX)), CAST(1 AS INT) FROM @Sample GROUP BY Class UNION ALL SELECT s.
Read more →
I had a strange scenario today and I can't reproduce it. I changed current database to adventureworks and ran following code DECLARE@SQL NVARCHAR(200) SET@SQL = 'SELECT DB_NAME()' EXECsp_executesql@SQL EXEC (@SQL) The sp_executesql statement returned "master" and exec statement returned "adventureworks".
Read more →
SELECTcpu_count AS [Logical CPUs], cpu_count / hyperthread_ratio AS [Physical CPUs] FROMsys.dm_os_sys_info
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 →
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?
Read more →
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.
Read more →
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
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 →
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 →
-- Prepare sample data DECLARE@Master TABLE ( ID INT, Name VARCHAR(20) ) INSERT@Master SELECT1, 'Peso' UNION ALL SELECT2, 'SQLTeam' UNION ALL SELECT3, 'SQL' DECLARE @Child TABLE ( MasterID INT, ID INT, Value VARCHAR(20) ) INSERT@Child SELECT1, 1, 'Row 1 for Peso' UNION ALL SELECT1, 2, 'Row 2 for Peso' UNION ALL SELECT2, 3, 'Row 1 for SQLTeam' -- Display the XML SELECTePurchaseOrder.
Read more →