Questions tagged [tsql]

25916 questions
1

votes
1

answer
471

Views

create cte multiple times and drop cte [closed]

I am using a cte in stored procedure. I have to use it multiple times ie populate cte for different ids how can I drop or remove current cte thanks ;WITH PAYOUT_CTE(REGNKEY, REGNTPE) AS ( SELECT REG_KEY, 'Parent' FROM ML_MSTR_REGN A (NOLOCK) WHERE A.COMP_NO = @COMP_NO AND A.REG_KEY = @CUR_...
GL SOFT INDIA
1

votes
3

answer
36

Views

SQL - Get Records within Date Range using 2 tables (Date Range/Hours given)

I have 2 tables: Table 1: Name, StartDate, EndDate Ex: Timmy, 9/12/17 08:00:00, 9/13/17 00:00:00 Timmy, 9/13/17 05:00:00, 9/13/17 07:00:00 Table 2: Name, StartHour, Data... Ex: Timmy, 9/13/17 06:00:00, Data1... Timmy, 9/13/17 04:00:00, Data2... Timmy, 9/13/17 07:00:00, Data3.....
Future44
-3

votes
0

answer
14

Views

How to log output result of SQL scalar valued function?

SQL DB is used as back end DB in part of PHP web application. Is there a way to write to some table result that function sends to front end? I have to use function (instead of sp) because of some limitation in front end framework. I made SP to write all data but if I try to execute it from function...
hoggar
1

votes
1

answer
53

Views

Transaction Level Delayed Durability

We are looking to deploy Transaction Level delayed durability to our databases. BEGIN TRANSACTION ----- COMMIT with (DELAYED DURABILITY = ON) Many of our client SQL servers are 2014 so should be fine but we still have some clients using 2010 - problem is we deploy the same code to all clients If i...
andyc209
1

votes
1

answer
78

Views

When inputting data into my T-SQL table from C#, I get an error

I am creating a register like program using C# and a local SQL Server database. I have managed to create a query that inputs the data into the table called UserAdd. Code: ALTER PROCEDURE UserAdd @Name VARCHAR(30), @Organisation VARCHAR(20), @DOB DATETIME, @Contact VARCHAR(50), @Address VARCHAR(250...
WMTS
1

votes
2

answer
274

Views

Follow the MS tutorial, but see the error [Microsoft][ODBC Driver Manager] Connection not open ODBC Error in SQLDisconnect

Using the following Microsoft SQL Server R Machine Learning link, I am able to execute till step 2 and everything goes fine: https://microsoft.github.io/sql-ml-tutorials/R/customerclustering/step/3.html But when I try to deploy the ML model in SQL SERVER 2017, I will always see the following issues:...
sy.zhang
1

votes
2

answer
62

Views

Apply filtering criteria to several SQL columns based on the values of temporary table

I have the following Input data: Chr Start End Ref Alt FORMAT Sample_01 Sample_02 Sample_03 row_id 1 1111111 1111112 AG - GT:AD:DP:GQ:PL 0/1:233,233:465:99:0,120,1800 0/0:117,0:117:99:0,120,18...
Yatrosin
1

votes
2

answer
53

Views

An if statement to a variable SQL Server

When I run a procedure (below), the 'Execute procedure' popup box requests a value which is what I expect. If I complete the value or I pass a null value it runs as expected. What I am after is if I don't select pass null value or leave the value field blank the query will run for all fields, effec...
FuriousD
1

votes
0

answer
68

Views

TSQL from 2012 to 2016, lost all performance on same query

I face this issue when same query performed at least 30 times slower on 2016v, and that was only for sample data, in real life it never finish, I ran it 24hr+, I see that inflation rate is very high, having 2 rec on input produces 200K rows. This query produce pretty complex Execution Plan and they...
Mike S
1

votes
1

answer
186

Views

Incorrect syntax near the keyword 'TRIGGER'

CREATE TRIGGER AirportCodeDegis ON AIRPORT INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @Silinen_AirportCode nvarchar(10) = (SELECT deleted.Airport_code FROM deleted); DECLARE @Yeni_AirportCode nvarchar(10) = (SELECT inserted.Airport_code FROM inserted); -- Airport Tablosunda güncellenen Airp...
BRKZKN
1

votes
2

answer
36

Views

Parsing a comma delimited column and transposing into rows [duplicate]

This question already has an answer here: Turning a Comma Separated string into individual rows 14 answers Suppose I have a table like this with an undetermined number of comma-delimited values in one column: thingID personID 1 123,234,345 2 456,567 and I want to get it into a...
Greg
1

votes
1

answer
45

Views

SQL Divide output not working

the code below is giving the result rounded up by a decimal place. i.e 123 abandoned calls/2473 Call volume*100 should give me 4.97 but is showing 5.00 any idea how to fix this? convert (Decimal (10,2),Cast (Abandoned as decimal(10,2))/Cast ([Call Volumes] as decimal(10,2)))*100 as Abandon_Rate
Joyиal Ali
1

votes
1

answer
32

Views

Creating an Efficient (Dynamic) Data Source to Support Custom Application Grid Views

In the application I am working on, we have data grids that have the capability to display custom views of the data. As a point of reference, we modeled this feature using the concept of views as they exist in SharePoint. The custom views should have the following capabilities: Be able to define wh...
Jason Richmeier
1

votes
2

answer
42

Views

Many-to-many that shows columns with null

I'm having some difficulty trying to figure how to adjust my query. I'm not very good at SQL queries as it's not my forte. Anyway, I'm not sure what I'm doing wrong. Here's my table setup. ID | Customer ---+------------- 1 | John 2 | Jane 3 | Steve ID | Assets ---+------------- 1 | RealEstate 2...
gdubs
1

votes
1

answer
68

Views

Not recognize some of Emojis in sql server with Arabic_BIN collation

I'm using SQL Server 2016 and my database has the Arabic_100_CI_AS collation. I inserted apple emojis in my database, but some of the emojis are equal in comparisons while other emojis are not equal. I resolved this problem with collate Arabic_BIN and I asked in this topic But I have new problem wit...
MhdMhd
1

votes
2

answer
307

Views

SQL Server Reporting Services Query

I want to a report on how many reports is successful, how many has failed as a count with added information like the report name, the user that made the report, reason for failure, path to the report, subscriptions, schedules and also be able to view historical data to show trends. Which tables can...
420
1

votes
0

answer
351

Views

Why a System-Versioned temporal table cannot be merged?

I have this error which I don't understand quite well. I have the following System-Versioned temporal table: CREATE TABLE [dbo].[Dim_Table] ( columnA int IDENTITY, -- columnB nvarchar(10) NOT NULL, columnC nvarchar(10) NULL, SysStartTime datetime2 G...
SNR
1

votes
1

answer
221

Views

Convert a Varchar/String with day name to Date in Sql Server

I have a string Sunday, November 19, 2017 in Varchar. How can I convert this to Date Format - dddd, MMMM, yyyy?
Akshay Mishra
1

votes
1

answer
66

Views

Number of decimal places returned by dividing integers?

--in sql server 2005. SELECT 5/13; ----> 0 SELECT Cast(5/13 AS real); --> 0 SELECT 5.00/13.00; --> 0.3846153 SELECT cast(5/13 as decimal(10, 2)); --> 0.00 SELECT cast(5 as decimal)/cast(13 as Decimal); --> 0.3846153846153846153 Is there a global setting for number of decimal places re...
Nabil
1

votes
1

answer
57

Views

T-SQL: AVG function

Can you please help me to find an AVG of my dataset for 'Count_of_Attno'? AttNo as 'Count_of_Attno' is nvarchar. SELECT CONVERT(CHAR(10), ArrDateTime, 120) as [Date], count(AttNo) as Count_of_Attno FROM [dbo].[Main] GROUP BY CONVERT(CHAR(10), ArrDateTime, 120) Order by CONVERT(CHAR(10), ArrDateTime,...
Algirdas
1

votes
3

answer
77

Views

SQL to find only those records which are before certain date

I have the above set of 2 tables and their respective columns. HEADER PO_Number (char) CUSTNMBR (char) Expiry_Date (datetime) PO_Status (tinyint, 0 = Open, 1 = Closed) STATUSDATE (datetime) BATCH PORDNMBR (char) CUSTNMBR (char) BTCHID (char) DATE1 (datetime) In he HEADER table the primary key is (P...
SMA
1

votes
1

answer
33

Views

XML - Extract after a String and before a certain character

I've the following XML Code: _RCFM*=.·AA123EDC/NB: Cheque holding v05 TESTE PT 223427 I'm trying to create a statement that allows me to get the ID: AA123EDC For that I'm using: SUBSTRING(col1, LEN(SUBSTRING(col1, 0, LEN(col1) - CHARINDEX ('DSI Request Number', col1))) + 1, LEN(col1) - LEN(SUBSTR...
SaCvP
1

votes
1

answer
54

Views

Join on partial column to compare ERP CRM codes

We're integrating our erp and crm. We want to compare lookup codes. The erp has codes in the code table, code_value column. The crm has a lookup table with a delimiter of // in the column. For example, the crm has state with the state abbreviation and // then the state long name. VA// VIRGINIA. It h...
Blocks
1

votes
0

answer
70

Views

Issue using greater then under case statement

I'am having issue using greater then between two date time while using case when statement. Basically i have a column called 'Status' which has rows of 'Open','Hold','Close'. I have another column as duetime and completedtime. Below is my Query in SQL server. Can any one help me to find ' if duetime...
Biswa
1

votes
2

answer
162

Views

Split Comma Separated List and Explode down on condition

I have a problem with exploding a DataFrame down into separate rows against a comma delimited list after splitting across to a set amount of cols. I'm trying to achieve this in Pandas but if this is possible using raw SQL (I tried and gave up) then that would be an ideal solution. Sample Data Refere...
CheyRav90
1

votes
1

answer
50

Views

Azure Stream Analytics: How to use two Azure Machine Learning Function

I am using two Azure Machine Learning Function in Stream Analytics but it seems like only one can be used per query. How can I split it into two queries? The streaming job failed: Stream Analytics job has validation errors: Multiple AML web service functions used in subquery. A query step can conta...
SamTew
1

votes
2

answer
53

Views

SQL - UPDATE CASE without a JOIN

bit of a novice here. I am trying to write an update query on a versioned view (table 'v') of an esri sde database in SQL Server. I update the view using data gathered from other processes and stored in table 'j'. The view has an Instead of Update Trigger so I created this query using a subquery t...
Simon.y
1

votes
2

answer
38

Views

Replace decimal value with a different value according to its current value in T-SQL

I've got a table with items and their prices. What I want to do is change the decimal places of the prices based on a rounding table. If the price ends in .01-.09, the decimal place should end in .94. If it ends in .10-.21, it should end in .95. If it ends in .22-.38, it should end in .96 and so...
user3634056
1

votes
0

answer
137

Views

SQL Server getting modified column name using sys.fn_dblog

SELECT s.[operation], s.[transaction name], s.[context], s.[allocunitname], s.[page id], s.[slot id], s.[begin time], s.[end time], from sys.fn_dblog(NULL,NULL) as s where operation IN ('LOP_INSERT_ROWS','LOP_MODIFY_ROW', 'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT') I use this select quer...
Aniki
1

votes
1

answer
42

Views

Execute stored procedure only once but pass different parameter values 10 times

I want to execute a stored procedure only once but pass different parameter values every time from the temp table #tbl_schools. How can I accomplish this task? create table #tbl_schools(id int not null) insert into #tbl_schools(id) values (1) insert into #tbl_schools(id) values (2) insert into #tbl_...
Sasha J
0

votes
0

answer
39

Views

SQL retrieve string that have special character

I am trying to write a query that select only the values that have a '+' at the end. These values are pieces of text that users select from a screen that serves them a list of reference comments. The different special characters that a pieces of text can have at the end are: '+' or '~' or '|' The v...
erasmo carlos
0

votes
1

answer
35

Views

Optimize the GUID column

Sorry for my English; in our SQL Server 2014 project, we have two tables that are synchronized between a web application and Android devices (they are therefore on all mobiles). In our databases, these two tables are identified by unique identifiers of type integer as the primary key, but we also...
Fred
0

votes
1

answer
29

Views

Shred XML into multiple rows in SQL

I have to shred an XML document every two weeks. It is compliant with the NEMSIS 2 standard and I have a compliant database built to hold the info. I've successfully built a query to import all of the one-to-one data, but I'm having trouble shredding the one-to-many data. Using this demo data: dec...
dav
1

votes
0

answer
11

Views

How to show time since last null value on Grafana

I am using influx DB and grafana to display some data. There are frequently power outages where it will display null data. I want to create a single stat panel that will display the time since the last null value. When editing the single stat I know about the 'Time of last point' and the unit 'from...
Jane
1

votes
3

answer
69

Views

SQL Server MAX Date from another table

I'm in a bit of a jam and hoping someone here can point out the errors in my ways. I have an update that I wrote that has a case statement and if the case statement conditions I have an ELSE to get the max date from another table. I initially had it as ELSE MAX(CONVERT(VARCHAR(23),vp.CancelDate,121)...
PeteMiller
1

votes
1

answer
43

Views

Grouping Daily data into Weekly data but incorrect grouping when year changes

I have a basic table with daily dates and numeric value for each day. However, after the necessary filters, some dates are missing. I would like this daily data (while missing random days of the week) grouped weekly where beginning of the week would be sunday and ending of the week would be saturday...
Nick Momin
1

votes
1

answer
302

Views

SQL Server Cannot Build Execution Plan

I'm using Microsoft SQL Server 2016 Enterprise SP1: Core-based Licensing (64-bit) Version 13.0.4411.0 and I have an extremely odd problem I don't know how to solve. I have an (admittedly fairly large) query that joins together over 100 tables. I wrote the original a while back and have run the que...
JoZ
1

votes
2

answer
169

Views

Sql server. The current transaction cannot be committed and cannot support operations that write to the log file

begin tran begin try select case when 1=0 then 0.0 else '' end --this will not work end try begin catch --error has occured. But it doesnt matter. We want to continue anyway end catch select 1 --do something else commit --unfortunatelly this producess error 'The current transaction cannot be committ...
1

votes
1

answer
47

Views

Group wise Last Null Values with specific condition in SQL

Given Data ID X_ST Y_ST STATUS_FLAG T_DATE Last Tran HOURDIFF 42015 0 2 3 1/1/2017 0:00 1/1/2017 23:49 NULL 42015 0 2 3 1/2/2017 0:00 1/2/2017 23:49 NULL 42015 0 2 3 1/3/2017 0:00 1/3/2017 23:49 NULL 42015 0 2 3 1/4/2017 0:00 1/4/2017 23:49 NULL...
Saumil Shah
1

votes
2

answer
60

Views

Trying to figure out how the best way to use TSQL to retrieve current date but have a time value hard coded for select queries [duplicate]

This question already has an answer here: Set time portion of a datetime variable 6 answers Currently I am looking for a way to avoid hard coding date values for select queries I seek to automate. Ideally my queries will run daily on a report server, so I want the day to change automatically but I...
Thomas

View additional questions