Tag Info

Hot answers tagged

7

Store duration in seconds as an integer; then average is quite easy. Right now you're trying to take an average of a string converted to a float converted to a datetime converted to a time. If that doesn't sound wrong to you, read it again. Then consider that time represents a point time. What is the average of 3:12 AM and 4:57 PM? Meet in the middle? ...


6

This is not possible. The INSERT statement takes a single destination object as per the syntax (BOL reference): [ WITH <common_table_expression> [ ,...n ] ] INSERT { [ TOP ( expression ) [ PERCENT ] ] [ INTO ] { <object> | rowset_function_limited [ WITH ( <Table_Hint_Limited> [ ...n ] ) ] } ...


5

I assume you must have invalid data stored in your nvarchar field. I created a simple test to obtain average duration, which works: DECLARE @T TABLE ( duration NVARCHAR(8) ); INSERT INTO @T VALUES ('00:00:05'); INSERT INTO @T VALUES ('00:01:04'); INSERT INTO @T VALUES ('00:02:03'); INSERT INTO @T VALUES ('03:00:02'); INSERT INTO @T VALUES ('04:00:01'); ...


3

As ypercube commented No, if the query is what you show, he is totally wrong. It's pretty sargable as it is. You can verify this by: Creating a simple test table with a [Date] column. Insert a large number of rows with varying dates. NOTE: In the above "large number" and "varying dates" is a precaution to ensure that your query is selective enough. ...


3

There is no inherent order inside a table. There is no pre-stablished order when you insert rows. If run for example the following query: SELECT A.A, B.B FROM tblA A JOIN tblB B ON A.id = B.id Without an ORDER BY, then no particular order will be used. If you want some particular sorted results, you must use an ORDER BY clause. You could use a ...


3

If you are on SQL Server 2012+ you can use CONCAT. This treats NULL as an empty string. declare @o int = 10000000/1000000; declare @i int = 0; declare @s nvarchar(200); while @i < @o begin set @s = CONCAT(@s, 'hello', cast(@i as nchar(4))); set @i += 1; end select @s;


2

Like the comment under your question suggests, concatenating a value to NULL will always yield NULL. To get the value you are looking for, you need to initialise @s to a value: declare @s nvarchar(200) = ''; The full query is like so: declare @o int = 10000000/1000000; declare @i int = 0; declare @s nvarchar(200) = ''; while @i < @o begin set ...


2

Try using dynamic sql: SET @createDatabase = 'CREATE DATABASE [test] ON PRIMARY CONTAINMENT = NONE ( NAME = N''test'', FILENAME = ''' + @filenamedata + ''', SIZE = 51200KB , FILEGROWTH = 10240KB ) LOG ON ( NAME = N''test_log'', FILENAME = ''' + @filenamelog + ''' , SIZE = 5120KB , FILEGROWTH = 5120KB )' EXEC sp_executesql @createDatabase Another member ...


2

This assumes that your week starts on a Sunday, and that you only have data from the past. If you need to collect data for this week/month/year/today and exclude data from the future, you need to calculate a start and end range instead of a single cutoff. CREATE PROCEDURE dbo.GetHighScores -- always use schema prefix! @scope VARCHAR(5) -- why an int? AS ...


2

First of all, what you are about to design is probably a VERY bad idea. A much better solution would be to have a dynamic schema where you add new tables and have the application understand how to query those table (you could place them in a schema). This largely avoids all the locking and query plan issues you are bound to run into with this model. There is ...


2

The column max_length is the maximum column length in bytes. For the National-Character set string types (NCHAR and NVARCHAR), each character requires two-bytes, so NCHAR(10) would have a max_length value of 20.


2

--Use Script to Shrink Log files of all databases other than the system DBs. USE MASTER GO SET QUOTED_IDENTIFIER ON GO SET ARITHABORT ON GO DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200) DECLARE DatabaseList CURSOR FOR SELECT name,recovery_model_desc FROM sys.databases ...


2

Simple answer for this is: SELECT mt.Id , mt.TranDate , mt.TranType , CASE WHEN mt.TranType = 1 THEN mt.TranAmount ELSE NULL END AS DepAmt , CASE WHEN mt.TranType = 0 THEN mt.TranAmount ELSE NULL END AS WitAmt FROM dbo.MyTable mt ORDER BY mt.Id;


1

You can calculate the Delay in a cross apply and use dateadd() to add it to st_date in the main query. select D1.Split3_ID, D1.CU_ID, D1.order_id, D1.st_date, D1.sku, D1.Priority, D1.Delay, dateadd(day, D2.Delay, cast(D1.st_date as date)) as CourseDate from dbo.set_dates as D1 cross apply ( ...


1

Comment from @ypercube is correct -- in this case, the syntax error you're encountering is because the previous line did not end in a semicolon: Declare RowID, CountAll INT DEFAULT 0 Should be: Declare RowID, CountAll INT DEFAULT 0; In MySQL's stored procedure language, you have access to two different types of variables. The first you declare with ...


1

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a ...


1

As Thomas points out, if your goal is to ensure that the operation is atomic (both inserts either work or fail in unison) then what you are looking for is transactions like so: BEGIN TRANSACTION INSERT INTO <table1> ... INSERT INTO <table1> ... COMMIT TRANSACTION The official documentation for this is at ...


1

You can use an INSERT trigger and select the value from the other table as the value for this column. This will be done for each insert so this is at least a performance issue. I'm afraid this is not a very good solution because this is intransparent. There would be better solutions with a better architecture. Maybe this is rather a client software task.


1

If you can't afford to refactor this field as an integer right now but you need better performance or simplicity for aggregate calculations, you may want to consider a computed field: ALTER TABLE timetable ADD DurationSeconds AS DATEDIFF(SECOND, '00:00:00', Duration); You can then index this field if needed. Based on a comment below, here's an ...


1

You have an order of operations problem here. You are expecting SQLCMD (which runs first) to use variables you define in T-SQL (which doesn't run until after SQLCMD is finished). Try a simpler script to demonstrate this: DECLARE @x VARCHAR(32) = 'string x'; :setvar x @x :setvar y "string y" SELECT '$(x)','$(y)'; Result: @x string y Basically, ...


1

1 Select unique site names which have been referred to at some point. Get rows from site where site_id exists in referrals. This assumes site name is unique in site. If not you can add a distinct. select s.name from dbo.site as s where s.deleted = 0 and exists ( select * from dbo.referrals as r where ...


1

This might be the cleaner approach you're after. Basically, check if the variable has been initialized yet. If it hasn't, set it to the empty string, and append the first city (no leading comma). If it has, then append a comma, then append the city. DECLARE @col NVARCHAR(MAX); SELECT @col = COALESCE(@col + ',', '') + city FROM tbl where state = ...


1

You can use EXEC to run dynamic SQL in a different database or different server. Also please try to use the catalog views directly instead of all of these messy metadata functions. DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.[object_id] = t.[object_id] WHERE ...


1

SELECT CTE1.CONSUMERID, CTE1.CHILDID, CTE1.APPLICATIONUSAGESTARTDATE, CTE1.WEEKNUMBER, -- a.CHILDID , AVG(CONVERT(DECIMAl,DATEDIFF ( minute , b.TIMESTAMP , a.TIMESTAMP))) as CURRENTWEEKTIMERRESTART FROM CTE2 a LEFT JOIN CTE2 b on a.CHILDID = b.CHILDID and a.row = b.row+1 LEFT JOIN CTE1 on CTE1.CHILDID = a.CHILDID and ...


1

Check out OPENQUERY (BOL reference) OPENQUERY is specifically designed for executing queries directly on the Linked Server. The first parameter is the name of your Linked Server, whilst the second is the query that you want to execute against it. I'm not entirely sure of the syntax of your query, but something like this should work. SELECT ipo FROM ...



Only top voted, non community-wiki answers of a minimum length are eligible