Questions tagged [tsql]

25980 questions
1

votes
2

answer
452

Views

Can I use SQLCMD Commands in Visual Studio Database Project Stored Procedure?

I have a lot of SQL that is the same over multiple stored procedures. For example most procedures have the same variables declared, and is in the same try catch block to handle errors. I'd like to use the :r command so that I can write this code in one file and just import it into each sp. I can use...
Samuel O'Connor
1

votes
2

answer
109

Views

Azure Stream Analytics query to detect missing alive event for a specific deviceId

I do not see a way to analyse a stream for the absence of a specific event with azure stream analytics query language. The stream may contain DeviceAlive and BeaconDetected events containing a DeviceId and in case of BeaconDetected also a BeaconId. Now I want to generate an error event if the Device...
Micha
-1

votes
2

answer
28

Views

How to make a query to check the value of a field

I have this table: ----------------------------------------------- code intvalue checkrole result ----------------------------------------------- A01 14 A02-A03 true A02 24 A04 false A03 10 A04 false A...
Renzo
1

votes
1

answer
45

Views

Fuzzy search in Azure SQL Server

In the project I work we have the necessity to use some kind of fuzzy search to fill an autocomplete field. This autocomplete field must show some information as the user type inside. I'm using Azure SQL database, and I found out that it's not possible to use Master Data Services. (https://docs.micr...
Henrique
1

votes
2

answer
27

Views

CRM - Query records with 'incompatible' combinations of values (SQL, SSRS)

I have a record type in my CRM environment where there are two lookups. There are certain combinations of values that aren't 'valid,' but no constraint in the system to prevent that entry. To illustrate, I'll use a small sample of cities and states to explain. External to the CRM, I have a spreadshe...
Adam G
1

votes
1

answer
17

Views

Trigger with case when

I have simple problem but I do not know what am I doing wrong and how to solve it. I wrote a trigger on table. Can you advice me? alter trigger PriceEU on pojazdyEN after update,insert as begin declare @value nvarchar(50) set @value = (select i.Currency from inserted i inner join Cars s on i.ID = s....
Rafał Kuba
1

votes
2

answer
47

Views

Getting column information from system MS SQL Server

When I am using the below SQL statement to retrieve the column information of a SQL Server database. I am getting more columns than there actually are I presume this is because of the system columns that are there also. SELECT c.name Field, t.name Type, c.Precision, c.Scale, c.max_length, c.is_nu...
Dave
1

votes
2

answer
62

Views

Search list of values including range in SQL using WHERE IN clause with SQL variable?

I am trying to implement search functionality with list of values in SQL variable, including range. Appreciate any guidance/links pointing to correct approach for this. Below is the dataset: CREATE TABLE [dbo].[Books] ( [ID] [NCHAR](10) NOT NULL, [AUTHCODE] [NCHAR](10) NULL, [TITLE] [NCHAR](10) NULL...
variable
1

votes
2

answer
28

Views

Multiple Cases For Same Result Column

Have a table where one of the columns has all of the info I need for a report. I want to substring certain portions of this column as a column in this report, but the problem is that this column has results from 3 varying character lengths. Example: Row1: 20180101_ABC_12 Row2: 20180102_DEFG_23 Row3...
joru100
1

votes
1

answer
41

Views

Selecting separated data from string as singe rows in MSSQL

I have a table of a legacy system with structure and data like this: id data 1 some|data|I 2 need|to|get 3 out 4 of|the|database I need some way to get this data as single rows in a query like so: some data I need to get ... and so on. Anyone got an idea on how to do that? Thanks a...
Stefan Wolf
1

votes
2

answer
63

Views

how to check whether one of Claimants have Status 0 and assign Claim Status based on that

Each Claim can have multiple Claimants. Claim is considered 'Open' if at least one of those claimants have ClaimantStatus = 0 So I need to retrieve data on Claim Level (not Claimants) and Create column ClaimStatus that would indicate whether Claim is Open or Closed. For each ClaimID, I need to chec...
Oleg
1

votes
2

answer
29

Views

When x and y inserted fill GEOMETRY automatically

The idea is when Longitude and Latitude inserted Location has to fill with correct info. But when I try to write code of this one I get error about Column names in each table must be unique. Column name '[Location]' in table 'Points' is specified more than once. Here's the code block. ALTER TABLE Po...
Samir Hemzeyev
1

votes
2

answer
25

Views

Best way to do a IIF conditional with subquery

I have a select with subquery inside like SELECT .... , (SELECT TOP 1 [DDC].[ContractedAmount] FROM @CustomersTable AS [DDC] WHERE [DDC].[DesignKey] = [D].[DesignKey]) AS [Contracted Amount] FROM.... But sometimes value of this select can be 0.00 so I want to add conditional if it's 0.00 just return...
Jonathan
1

votes
1

answer
36

Views

WAITFOR DELAY doesn't act separately within each WHILE loop

I've been teaching myself to use WHILE loops and decided to try making a fun Russian Roulette simulation. That is, a query that will randomly SELECT (or PRINT) up to 6 statements (one for each of the chambers in a revolver), the last of which reads 'you die!' and any prior to this reading 'you survi...
Simmo33
1

votes
1

answer
44

Views

Optimize A Complex Query

The below procedure taking 9 seconds to execute, and the primary table has only 30000 records. ALTER PROC [dbo].[TransactionReport_NewWork] @StartDate datetime = NULL, @EndDate datetime = NULL , @Mid varchar(50) = NULL -- varchar(5000)=null , @BatchNo var...
1

votes
2

answer
54

Views

MS SQL - should I use case is there other way?

I need help with SQL code. I have a table that looks like this: acIdent acSubject acCode 201.2.013 Jusel F-203 201.2.013 Tehnomehanika F-101 (1,5 mm) I have an application that creates report, based on some filters. This is what i want to achive: When I choose acSubject...
Filip Jankovic
1

votes
3

answer
58

Views

Get most frequent value per group

I have a table (DeviceOS2) and would like to get the most frequent value for each column (OS and Device) per ID. ID OS Device 123 OSX Mac 123 OSX PC 123 OSX PC 123 Android Tablet Desired result: ID OS Device 123 OSX PC However, my code n...
apples-oranges
1

votes
1

answer
27

Views

SQL query returning values not existing in table from set in (brackets)

Need to get values which are not in table, enumerated right in TSQL query Table Cities create table Cities (Id int, City nvarchar (max)) insert into Cities values (1, 'New York'), (2, 'Moscow'), (3, 'Mexico'), (4, 'Kuala-Lampur') Now I need to extract cities that exist in set ('Los Angeles', 'Mosc...
Viktor Fursov
1

votes
1

answer
29

Views

T-SQL: DateTme Column varchar cast that may contain '1753-01-01' to empty string

Task: convert a DateTme column (cast as varchar) that may contain 1753-01-01 to an empty string. What is the most efficient way to do this in T-SQL? For example, if I use: SELECT LEFT(CONVERT(VARCHAR, DateCol1, 120), 10) FROM ourDatabase.dbo.ourTable How can I efficiently change any row containing...
JosephDoggie
1

votes
4

answer
7.6k

Views

How to find if a word has all vowels (SQL Server 2005)

DECLARE @t TABLE(Words VARCHAR(100)) INSERT INTO @t SELECT 'Stack Overflow' UNION ALL SELECT 'EQUATORIAL' SELECT * FROM @t WHERE Words LIKE '%[AEIOU]%' I am getting both as the output Words Stack Overflow EQUATORIAL The desired output being EQUATORIAL Thanks
priyanka.bangalore
2

votes
3

answer
46

Views

How to assume a DATETIME is in my local time zone, then convert to UTC

Using SQL Server 2016, I have a date_time column of type DATETIME that was inserted using the machine's local time zone. I need to convert this value to UTC. I tried using CONVERT(NVARCHAR(19), date_time AT TIME ZONE 'UTC', 127) + 'Z' AS Timestamp but I realize now that it automatically assumes the...
lzzydy
-2

votes
1

answer
36

Views

Time Diff Calculation

This is my data: How can I calculate the time difference: so in this case it should be 08:26:00 ? The column in 'Vrijeme'
Hrvoje Voda
1

votes
2

answer
22

Views

Is it possible to get the PERCENT_RANK for a single record, but relative to the entire table?

I would like the PERCENT_RANK value for a single record, but in relation to the entire table. Is this possible? Examples I've seen are like this: SELECT Name, Salary PERCENT_RANK() OVER (ORDER BY Salary) FROM Employees Notice that it's calculating the percentile for the returned recordset. If you're...
Deane
3

votes
2

answer
49

Views

How can I get better performance on union clause on t-sql

I have three tables. Each table contains more than 3M rows. I run following code: SELECT * FROM ( SELECT col_1, col_2, col_3, [date], 1 as type FROM table_1 UNION SELECT col_1, col_2, col_3, [date], 2 as type FROM table_2 UNION SELECT col_1, col_2, col_3, [date], 3 as type FROM table_3 ) AS tb tb...
1

votes
3

answer
1.1k

Views

Product price comparison in sql

I have a table looks like given below query, I add products price in this table daily, with different sellers name : create table Product_Price ( id int, dt date, SellerName varchar(20), Product varchar(10), Price money ) insert into Product_Price values (1, '2012-01-16','Sears','AA', 32) insert int...
Vicky
1

votes
3

answer
5.7k

Views

SQL: Count two different columns from two different tables

I am trying to get the distinct counts for the resource column of two different tables, then show the comparison for each project ID. Right now, this query gives me the same count values for both tables. select t1.PRJCT_ID, count(t1.RSRC_ID) as TBL1_RSRC_CNT, t2.PRJCT_ID, count(t2.RSRC_ID) as TBL2...
Jeremy F.
0

votes
0

answer
16

Views

How to Change the title of SQL query according to parameter passed on stored procedure?

I have a stored procedure which accepts value @OrderType and if Ordertype is Production i want heading as ProductionDate and if it is TakeDown I want heading as TakedownDate. DECLARE @OrderType nvarchar(50); SELECT CONVERT(VARCHAR(10), FromDate, 101) AS [PostingDate] FROM table //if ordertype i...
Biraz Dahal
-2

votes
1

answer
17

Views

SQL Code: Date Range Conditions Required to get desired output

I have a table: ID primarykey Constant BeginDate EndDate 1 201922893 324 2018/12/01 2019/04/30 1 204225217 338 2019/05/01 NULL 2 200562823 411 2018/12/01 2019/03/31 2 202525490 461 2019/04/01 2019/04/30 2 204228089 461 2019/05/01...
user3555246
1

votes
0

answer
15

Views

How do I convert this local function call to a remote function call?

I've seen other posts talk about using OPENQUERY to call a function on a remote server, but I have not been able to get this to work for my simple function below, which returns a numeric value. (I cannot change this function, so I am stuck with its parameters and return value): DECLARE @ProjectDepa...
Robert
0

votes
3

answer
24

Views

Wanted to select from a table but it's ignoring it

I want to have a select statement that takes a table parameter of IDs. The desired behavior is that if there are no elements in the table, get all of the values but if there are elements in the table, only select those who's values match the ID parameter of the table. My where clause looks like this...
mark b
0

votes
1

answer
16

Views

Executing script for add primary keys to all tables returns error

I want to add Primary Key IDD to every 'Temp...' table in the schema. I'm trying to do it the following way: DECLARE @addId VARCHAR(MAX) = ''; SELECT @addId = @addId + 'ALTER TABLE ['+ TABLE_NAME +'] ADD IDD int IDENTITY(1,1);ALTER TABLE ['+TABLE_NAME +'] ADD PRIMARY KEY (IDD);' FROM INFORMATION_SCH...
Passer7by
1

votes
0

answer
19

Views

How to create a SQL Server table with exactly 1 MB data in it

I'd like to try and gauge my users internet speeds based on the downloading of a dataset of known size (1MB). Using T-SQL only, how can I quickly create a table with exactly 1MB of data in it? I want to be able to run EXEC sp_spaceused N'dbo.myTableName to verify data size.
ericOnline
1

votes
2

answer
1.1k

Views

How to combine two query results into one and I have different column name

I have two queries I want to combine into a single output, I can't use a Union operator because they have different numbers of columns. **First query result** A1 A2 10 15 **Second query Result** B1 B2 5 10 **The final result would be** A1 A2 B1 B2 10 15 5 10 T...
Bhanu
1

votes
3

answer
83

Views

How to calculate the number of nodes in a JSON string

Is there a way to calculate the number of nodes in a JSON in SQL. { 'File':[ { 'ID':1, 'Fragment':'Frag1' }, { 'ID':2, 'Fragment':'Frag2' }, { 'ID':3, 'Fragment':'Frag3' }] } Is there a way to count the number of child elements under File node? Answer should be 3 according to the above ex...
Harsha W
1

votes
3

answer
56

Views

Include hard-coded value in INSERT..SELECT

I have this stored procedure in SQL which works fine. But I want to insert a text 'POSTED' into Assesable column while executing the procedure; I need help to achieve this. CREATE PROCEDURE [dbo].[spBankToAll] AS BEGIN INSERT INTO AllPayandRec (BID, EntryDate, Typez, FullName, Assesable) SELECT Ban...
eka24
1

votes
3

answer
40

Views

Which command for cut off string data in T-SQL at specific symbol?

Here is the data I have : BS120-S12-H01 b120-rjos-H03 BS12-J02-H03 and here is what i want to get from the sql : H01 H03 H03
Juthathip Jangjaras
1

votes
1

answer
70

Views

Finding duplicates using HAVING

I'd like to find all duplicates in below table. I know I could use a CTE to wrap query and then syntax would be correct. I am interested in solution where I would be able to access cnt column directly. I'd like to use count() over (partition by) clause as well. CREATE TABLE #People ( ID INT, [NAME]...
tylkonachwile
1

votes
1

answer
31

Views

T-SQL Json_modify function

T-Sql JSON_MODIFY function has some issue its adding wrapper when appending a new value pair like declare @jsonstring varchar(max) ='{'Width':'100%','Length':'45%'}' select JSON_MODIFY(@jsonstring,'append $.Height ','50%' ) -- output {'Width':'100%','Length':'45%','Height':['50%']} -- Its addin...
Kumar_2002
1

votes
3

answer
71

Views

GROUP BY statement with a PIVOT command in T-SQL

I have a SQL query that involves a PIVOT command that creates the correct table but now I need to GROUP BY one of the columns. When I try to add the GROUP BY statement, it is returning the following error: 'Column 'PivotTable.1' is invalid in the select list because it is not contained in either an...
C1-14MP
1

votes
2

answer
41

Views

Non Clustered Index - One or two index for almost the same need (join between two tables)?

I have a great amount of queries where I make a join between two tables on three columns: col1,col2 and col3. So I created a non clustered index on those three columns in the two tables. But I have the same great amount of queries where I make a join between the two tables on two columns only: col1...
Marc

View additional questions