Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

learn more… | top users | synonyms (1)

0
votes
2answers
19 views

TSQL Delete All dates and stores data

This might sound like a stupid question but I can't seem to find a better way. It's a posting system where stores send us info on a daily basis (can be for multiple days). That info is placed in ...
1
vote
2answers
13k views

how to calculate difference of first row and last row fields value in each group

I have table with structure like this: +-------+------------------+ | Value | Date | +-------+------------------+ | 10 | 10/10/2010 10:00 | | 11 | 10/10/2010 10:15 | | 15 | ...
1
vote
3answers
34 views

How do I handle multiple rows with variables defined by a query?

I am trying to write a query that uses variables to feed into a sub query. Query: DECLARE @Code VARCHAR(5) DECLARE @Sys INT DECLARE @Key INT SET @Key = 12345 SET @Code = (SELECT Code FROM ...
-1
votes
0answers
35 views

T-SQL Window over Window?

Ok, I already know window over window doesn't work, at least not on the same select. But I have this, almost a puzzle, to solve: (btw, that's a print in pt-BR, "soma" = "sum".) Columns N and O ...
1
vote
1answer
29 views

Concatenate data in a single row [duplicate]

I have the following table: TicketID Type Code 44899B82-41C9-4020-9C2B-007C6000D283 ARTI 22150012 44899B82-41C9-4020-9C2B-007C6000D283 ...
4
votes
2answers
129 views

How to optimize this tricky SQL self-join?

I want to perform a complex self-join on a table. I know that this could in theory be done very efficiently (see below), but I have trouble getting SQL (on Microsoft SQL Server) to do so. My question ...
15
votes
1answer
1k views

Why 0 is equal to empty string?

I need some help in finding why the following T-SQL statement returns 1 (true): SELECT IIF( 0 = '', 1, 0) I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is ...
8
votes
2answers
1k views

Is there a way to force Deferred Name Resolution even if the table exists when creating a sproc?

When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that ...
0
votes
0answers
20 views

Issues with the @query parameter in sp_send_dbmail

When I use the following: select * from table A where repID in ( select repID from table B where id = 5 ) as the string I'm assigning to the @Query parameter. ...
1
vote
3answers
48 views

Get Column Value from Subquery in Stored Procedure

In my stored procedure I got code like @Select = '... (Select ...) AS NAME ... AS AGE ... AS CLASS ' @Where = 'CLASS = ...' I can make validation ...
6
votes
3answers
276 views

The fastest way to get the most recent records

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition ...
3
votes
2answers
60 views

Most recent order from multi join query

Here's the CTE version of the query, but it is returning multiple rows for the same customer. Not just the row containing the most recent open order date: with LatestOPenOrderDate_CTE AS ( Select ...
0
votes
2answers
51 views

SQL With Multiple Condition and Column

I have 3 tables: Employee +----+--------------+--------------------+----------------+ | Id | EmployeeName | EmployeePermission | Position | ...
0
votes
1answer
18 views

Perform Sum In Update Statement [on hold]

I am trying to run this update statement, but it keeps giving me the error of (3 row(s) affected) Msg 157, Level 15, State 1, Line 9 An aggregate may not appear in the set list of an UPDATE ...
2
votes
0answers
45 views

TSQL Generic Row number theoretical question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next) ...
2
votes
1answer
24 views

Getting Dates In Range Into Table

I have a calendar table that shows all dates from 01/01/2015 to 12/31/2016, essentially the calendar shows the date and isworkday. I need to be able to pass a begindate and enddate to a stored ...
4
votes
4answers
551 views

Delete into new table (like select into)

The select into syntax will create a new table to insert data into (I know that types may not be consistent etc. but it gives you a rough copy). Is it possible for a delete output into statement to ...
0
votes
2answers
17 views

Create new instance MS SQL Server 2014

I have installed MS SQL Server 2014 , but after installation there is no instance created. There is only the default instance. How make a new instance in existing MS Sql Server ? I found a lot of ...
0
votes
1answer
43 views

Select Into removes IDENTITY property from target table

I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement. source table name: Sales.Orders column name: orderid column property: int ...
1
vote
2answers
104 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
0
votes
1answer
28 views

EXEC master.dbo.xp_cmdshell 'The user name or password is incorrect'

I am trying to move a file from a network drive to another server and when I execute this statement. I get an output of The user name or password is incorrect DECLARE @SourceFile AS ...
2
votes
0answers
43 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
3
votes
1answer
79 views

Aggregate data from multiple rows into single row

In my table I have rows, and each row has some data and its version. When column is not-null, it overrides an old value otherwise nothing happens. Pseudocode (in C#) var resultTable = new ...
1
vote
2answers
167 views

How to use SET operation in CASE clause in SQL?

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: Incorrect syntax near SET Query: DECLARE @tbl_Result TABLE ( RowID INT ...
1
vote
1answer
38 views

Compare Timestamp Difference Between Consecutive Records

How do I compare Timestamp difference between 2 consecutive records? This is my example: DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date); INSERT @x VALUES (101, 1, ...
1
vote
3answers
359 views

Using Echo statement writes Echo is ON in the text file

Following code is used for write a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
7
votes
1answer
660 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
3
votes
1answer
62 views

Select data divided in groups evenly distributed by value

I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to ...
3
votes
1answer
68 views

Where clause from OUTER APPLY using LEFT function

What would be a good alternative to improve my where clause that currently uses the LEFT function? Should I create a persisted computed column and an index to it? SELECT --columns-- FROM ...
2
votes
0answers
34 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
2
votes
2answers
626 views

SQL Server 2008 R2 Database Diagram Error Code

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error ...
2
votes
1answer
32 views

String parsing: Extracting the filename from a URL

This is a link I have in a column of a table: http://earthexplorer.usgs.gov/browse/etm/1/11/2000/LE70010112000130KIS00.jpg There are like 1000's of rows like this. Is it possible to extract the ...
6
votes
3answers
210 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
2
votes
3answers
812 views

Excluding particular words from a string on a WHERE clause

I have a table that stores invoices and I'm trying to get stats out of it, unfortunately the table is poorly build and some crucial information is all mixed up on a nvarchar field, information such as ...
2
votes
2answers
50 views

DBCC Usage on production system

We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores). Another VM (the application server) within the system accesses the db server quite intensively for both ...
2
votes
2answers
186 views

Use Distinct With Sum?

I want to sum a few values using case statements. My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah. For example, in my ...
-2
votes
3answers
58 views

Extract Portion From A Decimal [closed]

I need to extract any value > .5 from a decimal. Here is sample ddl Create Table #b ( fv decimal(16,4) fv1 decimal(16,4) ) Insert Into #b (fv) Values ('.8032') How can I capture the .3032 and ...
3
votes
2answers
102 views

Convert int to RGB or hex color

I'm working with a database where colors are stored as integers. For some unknown reason, they are negative integers actually. Now, I need to use them in the CSS background-color property, which ...
2
votes
2answers
46 views

Finding and listing all columns in a SQL Server database with identity [duplicate]

I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.
1
vote
1answer
30 views

How to combine 2 SQL statements into one?

I have these 2 SQL statements. Each year, I need to add the new one for the new DB. How can I write it so it will just work for the new years instead of me manually editing this each year? Is there a ...
0
votes
0answers
42 views

Query Freezes and SSMS Crashes

I feel like I have the syntax set on my query, but anytime I try to run this SSMS freezes and I have to reboot and once I get my PC back up and running no records have been updated. The table only ...
8
votes
2answers
11k views

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be ...
5
votes
1answer
899 views

Calculating Median Value within a Group By

I have a Stored Procedure which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do ...
0
votes
0answers
11 views

Why is Echo statement in SQL query not writing in text file? [duplicate]

Following code is used for writing a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
1
vote
2answers
62 views

SQL Server: Query Efficiency Having vs Subquery

I have the following query where I used a sub query since I can't use aggregates in the where clause. SSMS suggested I use the HAVING clause but then I would need to group by all the other columns ...
2
votes
2answers
39 views

How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an ...
3
votes
1answer
130 views

SQL to Find Partial Duplicates of a Field

I have a table with a field for a member number. The table can contain records with duplicate values for this field and does. I have a SQL query that will obtain any duplicate values for this field, ...
24
votes
7answers
46k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
1
vote
0answers
59 views

reset identity of a column on a new primary key record

I have the following table CREATE TAbLE aTEST_person ( person_id int default (NEXT VALUE FOR [person_id_TEST]) , version_num int, name varchar(255) CONSTRAINT ...
0
votes
2answers
50 views

Format Phone Numbers In SQL Server

I am using SQL Server 2008 and I found this solution which is only for SQL Server 2012 - what is the equivelant to format a phone number in SQL Server 2008? FORMAT([PhoneField],'###-###-####')