NULL is already ignored so you can use NULLIF to turn 0 to NULL. Also you don’t need DISTINCT and your WHERE on ActualTime is not sargable. SELECT AVG(cast(NULLIF(a.SecurityW, 0) AS BIGINT)) AS Average1, AVG(cast(NULLIF(a.TransferW, 0) AS BIGINT)) AS Average2, AVG(cast(NULLIF(a.StaffW, 0) AS BIGINT)) AS Average3 FROM Table1 a WHERE a.ActualTime >= ‘20130401’ AND a.ActualTime … Read more
Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS): sp_who sp_lock Also, in SSMS, you can view locks and processes in different ways: Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu … Read more
Depending on your needs, here are a couple of ways: IF EXISTS (SELECT * FROM TABLE WHERE COLUMN = ‘SOME VALUE’) –INSERT SOMETHING ELSE –INSERT SOMETHING ELSE Or a bit longer DECLARE @retVal int SELECT @retVal = COUNT(*) FROM TABLE WHERE COLUMN = ‘Some Value’ IF (@retVal > 0) BEGIN –INSERT SOMETHING END ELSE BEGIN … Read more
Here’s how I solved it: Working SQL Fiddle First I have create a function which splits the string value i.e. ‘1,2,4,5’ Split function: CREATE FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ‘ ‘) RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value varchar(8000) ) AS BEGIN DECLARE @index int SET @index = -1 WHILE (LEN(@text) … Read more
You cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source. If you want an output of 0 from the SUM and COUNT functions, then you should not use GROUP BY. The reason is that when you have no records, the GROUP BY clause has … Read more
It’s hard to state this more succinctly than SQL Server MVP Brad McGehee: As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases–such as an identity column, or some other column where the value is increasing–and is unique. … Read more
Actually you’re close to your goal, you just need to use nodes() method to split your rows and then get values: select s.SqmId, m.c.value(‘@id’, ‘varchar(max)’) as id, m.c.value(‘@type’, ‘varchar(max)’) as type, m.c.value(‘@unit’, ‘varchar(max)’) as unit, m.c.value(‘@sum’, ‘varchar(max)’) as [sum], m.c.value(‘@count’, ‘varchar(max)’) as [count], m.c.value(‘@minValue’, ‘varchar(max)’) as minValue, m.c.value(‘@maxValue’, ‘varchar(max)’) as maxValue, m.c.value(‘.’, ‘nvarchar(max)’) as Value, … Read more
Updated For MS SQL Server 2012 and above USE [master]; DECLARE @kill varchar(8000) = ”; SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), session_id) + ‘;’ FROM sys.dm_exec_sessions WHERE database_id = db_id(‘MyDB’) EXEC(@kill); For MS SQL Server 2000, 2005, 2008 USE master; DECLARE @kill varchar(8000); SET @kill=””; SELECT @kill = @kill + ‘kill ‘ … Read more
Because when you declare, default varchar length is 1. So @MyVarchar ends up being ‘A’. This is different to cast(something as varchar), where default length is 30. The right thing is DECLARE @MyVarchar varchar(10) = ‘ABCDEF’; where 10 is the length of the column in the table.
Try something like: SELECT id, NewsHeadline as news_headline, NewsText as news_text, state CreatedDate as created_on FROM News WHERE CreatedDate >= DATEADD(day,-7, GETDATE())