Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,602 questions
2 answers
Getting error creating view
I am creating a view over multiple tables. I am getting an error on a field that says it's invalid but it does exist in the table. field name is udHCSSE360WO and I have verified the spelling against the table. The same field exists in a different…
asked 2020-08-21T13:22:45.243+00:00
Brenda Stewart
96
Reputation points
commented 2020-08-25T01:53:33.187+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 76%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEM%3C/text%3E%3C/svg%3E)
EchoLiu-MSFT
14,581
Reputation points
2 answers
best way to query
Hi, I have the below query.I am passing the param to SP and @month is of yyyy-MM format.Basically whenever the @month is any month other than quarter end month,i calculate value from previous quarter end else i calculate the value for quarter end…
asked 2020-08-20T01:57:31.053+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 7.000000000000001%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
PV
21
Reputation points
commented 2020-08-25T01:29:47.747+00:00
EchoLiu-MSFT
14,581
Reputation points
3 answers
Why I am not able to restore TDE database?
Hello, I am running into an issue regarding restoring a TDE database and I just keep getting the "thumbprint not found error". I don't know what's causing it. Here are some details. I'm hoping someone can help me. I have many…
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(195.2, 52%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESL%3C/text%3E%3C/svg%3E)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 48%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
3 answers
One of the answers was accepted by the question author.
SQL Server Aggregate Query
I need to write a query that returns the ID of the items counted to find duplicates. I have a Transaction table with Procedures and need to find accounts where COUNT(Procedure)>1 GROUP BY Account. That part I can do. I don't know how to return the…
asked 2020-08-21T14:12:02.877+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(214.4, 21%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
SM
21
Reputation points
answered 2020-08-24T09:49:00.597+00:00
EchoLiu-MSFT
14,581
Reputation points
6 answers
Help with Transact SQL
Hello all, I have a table with columns as below DECLARE @meter TABLE (prop_id int, min_calc_date datetime, max_calc_date datetime, fuel_rate int ) INSERT INTO @meter VALUES (123,'01-01-2020','08-19-2020', 50) , (123,'01-01-2020','08-19-2020',…
asked 2020-08-20T20:15:42.217+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(112.00000000000001, 0%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESK%3C/text%3E%3C/svg%3E)
Sri Kotte
21
Reputation points
commented 2020-08-24T08:52:02.687+00:00
EchoLiu-MSFT
14,581
Reputation points
5 answers
Select top 1 column along with CSV column
Hi All, I've a table as below: TableId SId Data DataOwner 1 10 aaaa 1001 2 10 aaaa 1000 3 20 bbbb 1000 4 10 ccc 2000 Declare @table1 table(TableId int, SId int, Data varchar(200), DataOwner int) insert into @table1 values(1, 10, 'aaaa', 1001)…
asked 2020-08-20T14:23:47.04+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(76.8, 53%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EB%3C/text%3E%3C/svg%3E)
Bluecloud1921
1
Reputation point
commented 2020-08-24T08:50:43.723+00:00
EchoLiu-MSFT
14,581
Reputation points
3 answers
BCP date format
I am attempting to import data using bcp. The data contains dates formated as Jan 03 2019 12:00:00:00.000000AM. BCP rejects this data, saying this is an invalid date format. I believe BCP expects a date formatted as 2019-01-03 12:00:00:00.000000. …
asked 2020-08-21T03:31:31.837+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 85%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETD%3C/text%3E%3C/svg%3E)
Ted Dykhorst
1
Reputation point
commented 2020-08-24T06:42:01.547+00:00
MelissaMa-MSFT
24,191
Reputation points
2 answers
Native Bcp file is converting special characters in the column value
Hi, I am generating bcp file in Sybase ASE with -n (native) and delimiter "|" and trying to load in SQL SERVER 2016. I know native file is not compatible with MSSQL Server . Is there any way or option i should explore to handle special…
asked 2020-08-20T20:25:13.447+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(51.2, 81%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
ssalun
26
Reputation points
commented 2020-08-24T06:40:29.3+00:00
MelissaMa-MSFT
24,191
Reputation points
3 answers
Return null values if there is no record for top n selected
I am trying to get the top ‘n’ rows from the following query: SELECT TOP 1 ImageName FROM (SELECT TOP 5 * FROM ClientKeywordMedia where keywordid=1147 ORDER BY ID) z ORDER BY ID DESC The above query is working fine if top 5 is used in subquery as…
asked 2020-08-20T16:40:24.183+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(182.40000000000003, 13%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESB%3C/text%3E%3C/svg%3E)
sudhir bharti
1
Reputation point
commented 2020-08-24T06:39:22.577+00:00
MelissaMa-MSFT
24,191
Reputation points
4 answers
Query with Exist
Hello team, I have written a query as below: ;With myCTETable AS ( SELECT ShipCountry, CustomerID, OrderID, OrderDate, Rank() Over(Partition by shipCountry Order by Shipcountry, OrderDate Asc) AS FirstOrderDate From Orders ) SELECT…
asked 2020-08-19T20:00:44.82+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(112.00000000000001, 15%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC%3C/text%3E%3C/svg%3E)
CouldsInSky
41
Reputation points
commented 2020-08-24T06:38:41.953+00:00
MelissaMa-MSFT
24,191
Reputation points
3 answers
Database design for friends relationships
Hallo! Can you help me design database for friends social relations with 1000 requests per second. Bob->Mary John->Bob Serg->Francis Francis->Bob Result: Bob->Francis->Serg Thanks.
asked 2020-08-18T13:06:15.097+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(70.4, 14.000000000000002%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EIM%3C/text%3E%3C/svg%3E)
Ilya Mihaylov
1
Reputation point
commented 2020-08-24T06:36:12.773+00:00
MelissaMa-MSFT
24,191
Reputation points
5 answers
One of the answers was accepted by the question author.
Return one record per criteria
Hello folks, I am struggling with a solution for getting one record per criteria. In the attached example, I would like to get one record per "nbr". I have started with this, but obviously something does not work as it should :) SELECT…
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 90%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC%3C/text%3E%3C/svg%3E)
3 answers
One of the answers was accepted by the question author.
Updating a table based on values in the table
I have a temp table #XX that looks like this" ID KWID NAME ORDER 1447 1144 SW_6368_58_55.png NULL 1450 1145 SW_6942_58_55.png NULL 1451 1147 Arb3_43_55.jpg NULL 1452 1147 Arb1_43_55.jpg NULL 1453 1147 Arb2_43_55.jpg NULL 1598 1152…
asked 2020-08-22T00:45:59.007+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(48, 23%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJS%3C/text%3E%3C/svg%3E)
Jim Seidel
61
Reputation points
commented 2020-08-22T21:57:34.327+00:00
Jim Seidel
61
Reputation points
4 answers
One of the answers was accepted by the question author.
Delete duplicated lines based on 2 primary key
Greeting guys i want to delete duplicated primary keys in a table based on update date : Ex i have this table : i want to delete the duplicated lines based on the primary key (ID1 and ID 2 are my primary key ) . i want to keep the…
asked 2020-08-17T10:57:41.437+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 82%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERF%3C/text%3E%3C/svg%3E)
Rami Frikha
91
Reputation points
accepted 2020-08-21T20:08:24.373+00:00
Rami Frikha
91
Reputation points
1 answer
One of the answers was accepted by the question author.
SSRS - STORED PROCEUDURE WITH TABLE VALUED PARAMETERS
Hi, How can we use Stored Procedure with Table Valued Parameter in SSRS Dataset ? CREATE TYPE DBO.MultiFilter AS TABLE ( filterVal VARCHAR(200) ) CREATE PROCEDURE DBO.USP_multiFilterTest @position DBO.MultiFilter READONLY …
asked 2020-08-19T09:59:25.577+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(300.8, 28.000000000000004%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESR%3C/text%3E%3C/svg%3E)
Srinivasa Rao Chigulla
46
Reputation points
commented 2020-08-21T18:29:45.693+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(124.80000000000001, 34%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
Allen M
11
Reputation points
2 answers
New sqlserver server performance cpu issue
I am having issues with a new sqlserver 2019 server. It is a virtual server running on 2 cores and 6 logical cores with 64GB ram. It seems to not be maxing out the cpus, for example when I do a rebuild of a clustered column store index on the old…
asked 2020-08-18T16:31:15.71+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(9.6, 98%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJG%3C/text%3E%3C/svg%3E)
jonjoseph@gmail.com
1
Reputation point
answered 2020-08-19T06:32:03.163+00:00
CathyJi-MSFT
21,136
Reputation points Microsoft Vendor
1 answer
One of the answers was accepted by the question author.
Trying to show a row total as percentage of the whole
I've looked at several examples of using the windowing functions to achieve what I am aiming for and as far as I can tell, I have my syntax correct according to those. However, I'm still getting an error : Column…
asked 2020-08-18T02:39:46.867+00:00
Nick Ryan (NZ)
121
Reputation points
commented 2020-08-19T03:05:37.337+00:00
Nick Ryan (NZ)
121
Reputation points
1 answer
How to Reset DENSE_RANK for new group
Hi All, How to reset DENSE_RANK for new set DDL and sample data population, start drop table if exists #t select * into #t from (select 1 as ID, 'a1' as address,'p1' as phone) p insert into #t select 1, 'a1', 'p2' union select…
asked 2020-08-18T13:59:35.78+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(230.39999999999998, 59%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Papillon28
86
Reputation points
answered 2020-08-18T23:11:40.333+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 12%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJL%3C/text%3E%3C/svg%3E)
Jingyang Li
5,891
Reputation points
2 answers
One of the answers was accepted by the question author.
sql if value is null not equal does not work
I have the below query, but the AND pd.FROMLOC <> 'DMS' does not work if the column has {null} is there a workaround? SELECT pd.wavekey, pd.orderkey, o.externorderkey, pd.pickdetailkey, pd.status, pd.fromloc, pd.loc, pd.sku, pd.qty,…
asked 2020-08-18T15:27:27.96+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 73%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJB%3C/text%3E%3C/svg%3E)
jim brown
271
Reputation points
commented 2020-08-18T19:31:35.217+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Yitzhak Khabinsky
25,731
Reputation points
2 answers
One of the answers was accepted by the question author.
Display current hour Production
CREATE TABLE dbo.RAWTable(Time_Stamp datetime , Date date , Shift_Id varchar(5) , Line_Code varchar(10) , Machine_Code varchar(10) , Variant_Code varchar(20) , Machine_Status varchar(30) , OK_Parts int , NOK_Parts int , …
asked 2020-08-17T12:15:33.477+00:00
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(6.4, 27%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERP%3C/text%3E%3C/svg%3E)
Rakesh Ponnala
21
Reputation points
commented 2020-08-18T12:12:48.63+00:00
Rakesh Ponnala
21
Reputation points