In order to avoid a “Division by zero” error we have programmed it like this: Select Case when divisor=0 then null Else dividend / divisor End ,,, But here is a much nicer way of doing it: Select dividend / NULLIF(divisor, 0) … Now the only problem is to remember the NullIf bit, if I … Read more
127.0.0.1,6283 Add a comma between the ip and port
If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause is where you use INCLUDE. The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it’s not part of the tree INCLUDE columns … 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.
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
;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
I cannot reproduce the error. Perhaps I’m not understanding the problem. The following works fine for me in SQL Server 2005, with the extra “foo” column appearing in the second select result: IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL DROP TABLE #Results GO CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT ) GO select company, … Read more
UPDATE im SET mf_item_number = gm.SKU –etc FROM item_master im JOIN group_master gm ON im.sku = gm.sku JOIN Manufacturer_Master mm ON gm.ManufacturerID = mm.ManufacturerID WHERE im.mf_item_number like ‘STA%’ AND gm.manufacturerID = 34 To make it clear… The UPDATE clause can refer to an table alias specified in the FROM clause. So im in this case … Read more
For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version. To check if a table exists use: IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘TheSchema’ AND TABLE_NAME = ‘TheTable’)) BEGIN –Do Stuff END