I don’t believe SQL Server has a built-in split function, so other than a UDF, the only other answer I know is to hijack the PARSENAME function: SELECT PARSENAME(REPLACE(‘Hello John Smith’, ‘ ‘, ‘.’), 2) PARSENAME takes a string and splits it on the period character. It takes a number as its second argument, and … Read more
DELETE TableA FROM TableA a INNER JOIN TableB b ON b.Bid = a.Bid AND [my filter condition] should work
The decision to use 1st January 1753 (1753-01-01) as the minimum date value for a datetime in SQL Server goes back to its Sybase origins. The significance of the date itself though can be attributed to this man. Philip Stanhope, 4th Earl of Chesterfield. Who steered the Calendar (New Style) Act 1750 through the British … Read more
Count all the DISTINCT program names by program type and push number SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type] FROM cm_production WHERE push_number=@push_number GROUP BY program_type DISTINCT COUNT(*) will return a row for each unique count. What you want is COUNT(DISTINCT <expression>): evaluates expression for each row in a group and returns the number … Read more
DELETE FROM TABLENAME DBCC CHECKIDENT (‘DATABASENAME.dbo.TABLENAME’, RESEED, 0) Note that this isn’t probably what you’d want if you have millions+ of records, as it’s very slow.
Strictly, method a is the least resource intensive: a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time? I saw a similar test elsewhere … Read more
Your syntax almost works in SQL Server 2008 (but not in SQL Server 20051): CREATE TABLE MyTable (id int, name char(10)); INSERT INTO MyTable (id, name) VALUES (1, ‘Bob’), (2, ‘Peter’), (3, ‘Joe’); SELECT * FROM MyTable; id | name —+——— 1 | Bob 2 | Peter 3 | Joe 1 When the question was … Read more
That format requires you to use either: CASE ebv.db_no WHEN 22978 THEN ‘WECS 9500’ WHEN 23218 THEN ‘WECS 9500’ WHEN 23219 THEN ‘WECS 9500’ ELSE ‘WECS 9520’ END as wecs_system Otherwise, use: CASE WHEN ebv.db_no IN (22978, 23218, 23219) THEN ‘WECS 9500’ ELSE ‘WECS 9520’ END as wecs_system
SELECT t1.name FROM table1 t1 LEFT JOIN table2 t2 ON t2.name = t1.name WHERE t2.name IS NULL Q: What is happening here? A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such … Read more
;WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn FROM DocumentStatusLogs ) SELECT * FROM cte WHERE rn = 1 If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead As for normalised or … Read more