The first value is the precision and the second is the scale, so 18,0 is essentially 18 digits with 0 digits after the decimal place. If you had 18,2 for example, you would have 18 digits, two of which would come after the decimal… example of 18,2: 1234567890123456.12 There is no functional difference between numeric … Read more
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
Why it’s not recommended to use subqueries? Database Optimizer (regardless of what database you are using) can not always properly optimize such query (with subqueries). In this case, the problem to the optimizer is to choose the right way to join result sets. There are several algorithms for joining two result sets. The choice of … Read more
May be this will help you. SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX(“https://stackoverflow.com/”, myColumn) WHEN 0 THEN LEN(myColumn) ELSE CHARINDEX(“https://stackoverflow.com/”, myColumn) – 1 END) AS FirstName ,SUBSTRING(myColumn, CASE CHARINDEX(“https://stackoverflow.com/”, myColumn) WHEN 0 THEN LEN(myColumn) + 1 ELSE CHARINDEX(“https://stackoverflow.com/”, myColumn) + 1 END, 1000) AS LastName FROM MyTable
Add one more condition in where clause SELECT * FROM product WHERE pdate >= DATEADD(day,-30,GETDATE()) and pdate <= getdate() Or use DateDiff SELECT * FROM product WHERE DATEDIFF(day,pdate,GETDATE()) between 0 and 30
You can try solution from these questions1 or questions2 and questions3. Or please try these steps as below: Enable IntelliSense: For all query windows, please go to Tools >> Options >> Text Editor >> Transact-SQL >> IntelliSense, and select Enable IntelliSense. For each opening query window, please go to Query >> Intellisense Enabled. Enable statement … 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
Syntax for the CTE in table valued function would be: CREATE FUNCTION GetDistributionTable ( @IntID int, @TestID int, @DateFrom datetime, @DateTo datetime ) RETURNS TABLE AS RETURN ( WITH cte AS ( SELECT ROUND(Result – AVG(Result) OVER(), 1) Result FROM RawResults WHERE IntID = @IntID AND DBTestID = @TestID AND Time >= @DateFrom AND Time … Read more
SELECT name, definition, type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE type_desc like ‘%function%’
This happens when you try to edit the top 200 rows of a SQL Server 2012 database using the 2008 R2 SQL Server Management Studio. Upgrade SQL Server Management Studio to 2012 or edit the rows using a SQL query.