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 →
SELECT@@SERVERNAME AS SqlServerInstance, db.name AS DatabaseName, SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize, SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize, SUM(af.
Read more →
SELECTdb.name AS DatabaseName, bf.logical_name AS LogicalName, CASE bs.[type] WHEN 'D' THEN 'Database' WHEN 'I' THEN 'Differential database' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'File or filegroup' WHEN 'G' THEN 'Differential file' WHEN 'P' THEN 'Partial' WHEN 'Q' THEN 'Differential partial' ELSE 'Unknown' END AS BackupType, CASE bf.
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 →
-- 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 →
I come across a question today if SQL Server 2008 MERGE command call table triggers one per hit, or one per statement as it normally does. The short conclusion is that the MERGE command splits the source data into three “streams” and internally executes INSERT, UPDATE and DELETE statements.
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 →