Questions tagged [tsql]

19426 questions
1

votes
1

answer
119

Views

T-SQL Select, ensure manager row comes before employee

Here is my problem. I have a table of employee data, which contains the username of the employee and their manager's username like so ______________________________________________ | employee_username | employee_manager_username| ------------------------------------------------ tom01 |...
Black Dynamite
0

votes
2

answer
34

Views

SQL join giving duplicate rows with group by clause

Thanks for all help i am facing issue while using joins Using one sccm query select count(distinct resourceid) as ModelCount,caption0,CSDVersion0 from [dbo].[v_GS_OPERATING_SYSTEM] where caption0 like '%Windows Server%' group by caption0,CSDVersion0 Data iam getting is as following so iam getti...
deepti
-2

votes
1

answer
40

Views

how to find all strings in between commas in sql server

I want to display a string in a table format as shown below: For a string like 'hi,is,1,question,thanks,.,.,n' I need this result: column1 column2 column3 column4 ..... column hi is 1 question ..... n
manoj
1

votes
1

answer
17

Views

accept phone numbers of specific characters

In SQL Server, I want a code that allows to accept phone numbers starting with 056 or 078 and have a total of 9-10 digits. I tried: WHERE (SUBSTRACT([mobile], 1 , 3 ) in (056, 078) ) and (len([mobile]) between 9 and 10)) Why does this not work?
Anna
1

votes
1

answer
149

Views

Create Schema Clones with RedGate sqlcompare & table of server names

Ok, so I have taken an old batch file that previous DBA's have used to "clone" the schema from Production databases on multiple servers and drop them in one network directory. This batch file was updated manually so I am trying to automate it. We have a support dbase with a table that has all of t...
maczealot
1

votes
3

answer
719

Views

Change date format in SQL server express database

I want to output the date in either dd/mm/yyyy or dd mmm yyyy e.g. 05/12/2014 or 05 Dec 2014. SELECT [Id] , [Name] , [Date] , [Time] FROM [People];
aazzaawwaazzaa
1

votes
1

answer
2.4k

Views

Microsoft t sql how to declare temporary variable within user defined function?

So how can I declare a temporary local variable with declare like:declare @tempVar varchar(max) within a user declared function? The actual problem I'm trying to solve here is declaring and using variable within user defined stored function. I declare my function like this: create function someFunct...
1

votes
2

answer
35

Views

How to choose everything except some number of items?

I have 4 tables. Every item in the first table is linked through second and third tables to some amount of items in the last table. Here it is : I select all appropriate items (i mean the black ones in the last table) using this code : SELECT DISTINCT a1.Name, a2.Name, st.Name, st.ID FROM Agents a1...
lucifer63
1

votes
4

answer
941

Views

TSQL Optional Where Clause

I have a Query that is pulling in data for the current month for my by default. I then have a "Filter" option on my page where they can enter in some data to filter the content by and re-run the query. I have my select statement working for the current month however I am not quite sure how to imple...
SBB
0

votes
0

answer
2

Views

convert XML column into multi rows

I have one row which one XML column contains data like below, Can you please help me to convert into rows, like Applicationid Tenantid 11119 2 11120 2 11121 2 11122 2
user8408233
1

votes
2

answer
266

Views

Odd LEFT JOIN error

I have two tables I am trying to join together on a particular column (shared between the two). The data in these columns are supposed to represent numbers, but the data are actually characters, and some of the values are non-numeric (e.g. '2,3,4', or 'n/a'). I am ignoring the rows with non-numeric...
Ethan Reesor
0

votes
2

answer
16

Views

Operand data type char is invalid for sum operator

My SQL View Output listed below: Year Date Reference MSNAME Debit Credit AcctNUM AcctDESC 2018 2018-09-21 2018phone Verizon 1023 0 022.20 Telephone 2018 2018-08-11 Phoneusage Verizon 1077 0 022.20 Telephone 2018 2018-07-01 Tx-Phone Verizon...
Jennifer
1

votes
1

answer
3.3k

Views

Creating a Trigger to update another Table in SQL Server

I'm trying to create a trigger in SQL Server. I'm inserting data into the database through forms that I made in Access. When I create a new record that gets inserted into my SALESDETAILS table, I want it to subtract the quantity (column) that was just entered in from my In Stock (column) in my PRODU...
Jess
0

votes
1

answer
39

Views

how to replace the result of sum by 0 if it is Null

I have 3 tables: Projects ( a project can have many phases) Phases (a phase can have 0 or many tasks) Tasks (primary key: id_task, id_phase, id_project) Every task has cost and the cost of a phase is the SUM(cost) of its tasks. I want to update the cost of every phase in the project, I tried this q...
Manel Hkiri
1

votes
6

answer
99

Views

deleting second comma in data

Ok so I have a table called PEOPLE that has a name column. In the name column is a name, but its totally a mess. For some reason its not listed such as last, first middle. It's sitting like last,first,middle and last first (and middle if there) are separated by a comma.. two commas if the person ha...
Sal
1

votes
1

answer
1.7k

Views

DATEADD When Dealing with SmallDateTime Data Type

What I'm trying to accomplish is to select all records in a table that were created the previous day - and the record is stored in the table as a 'smalldatetime' so it also includes the time the record was created. If I used the DATEADD function in my WHERE clause, I am returning no records which I...
MISNole
3

votes
2

answer
35

Views

How to select and export 5,000 lines of debit and credit transactions at a time and have the debits and credits balance to zero?

The ERP system we're migrating to requires csv files with 5,000 or less rows for the GL. The debit and credit transactions within each file must balance to zero. There are multiple debit and credit transaction rows that share a common transaction ID. Using offset and fetch next I've been able to ext...
Tomas789
1

votes
2

answer
103

Views

SQL Full text Search with phrases, prefix_term on multiple columns

My application is storing Names of Customers in 3 Columns, but the search triggered using a single parameter. I am trying to implement a FULLTEXT Search to effectively search in this customer database. I am looking for the name RIMA HUSSEIN I used the following TSQL to search with the starting word...
Dilip
1

votes
1

answer
743

Views

Output Inserted.Id AND another field

I have the following query: DECLARE @OutProduct TABLE ( ProductID INT, BulkProductId INT ) INSERT INTO dbo.Products ( EanCode , ChangedDateTime , ChangedById , Deleted ) OUTPUT INSERTED.ID, BulkProducts.Id INTO @OutProduct (ProductID, BulkProductId) SELECT EanCode , GETDATE(), GETDATE(),...
iuliu.net
11

votes
1

answer
123

Views

Generate data seeding script using PowerShell and SSMS

Here I found a solution for the manual creation of the data seeding script. The manual solution allows me to select for which tables I want to generate the inserts I would like to know if there is an option to run the same process via PowerShell? So far I have managed how to create a SQL script whic...
GoldenAge
1

votes
4

answer
331

Views

How to change background color for each two rows in SSRS in a group

How can I write the expression in order to change background color for each two rows in SSRS? I need something like that: I tried expression =IIF(Fields!Type.Value="2016 Submitted" , "LightBlue", IIF(Fields!Type.Value="2015 Submitted" , "LightBlue", Nothing)) But because some months dont have value...
Oleg
1

votes
1

answer
2k

Views

t-sql convert datetimeoffset with utc+01:00 to local amsterdam datetime

I want to run a simple T-SQL SELECT query such that this input (datetimeoffset)... 2015-01-01 00:15:00.0000000 +01:00 OR 2015-05-04 14:15:00.0000000 +02:00 ...comes out as this output (datetime): 2015-01-01 01:15:00 OR 2015-05-04 16:15:00 Input is one column and output should also be one column. Any...
R. Neven
1

votes
2

answer
1.2k

Views

SQL Server Write Permission but not delete

I'm pretty sure the answer is that once I grant write/alter access it explicitly gives the person access to delete.
lookslikeanevo
1

votes
2

answer
49

Views

Using SSIS and T-SQL to convert date to dd.mm.yyyy

When I use T-SQL to convert a datetime into dd.mm.yyyy for an csv output using SSIS, the file is produced with a dd-mm-yyyy hh:mm:ss which is not what i need. I am using: convert(varchar,dbo.[RE-TENANCY].[TNCY-START],104) which appears correct in SSMS. Which is the best way to handle the conversion...
user3306489
1

votes
1

answer
28

Views

Set consecutive numbers to specific rows

I have table like this: +-----------+--------------------------------------+--------------+ | DesignKey | DesignTypeGuid | DesignNumber | +-----------+--------------------------------------+--------------+ | 2312 | 4FB560B0-E867-46B2-B116-338AD48C97AC | 3 | |...
Jonathan
0

votes
0

answer
18

Views

Dbmail, why is variable “DIA” converting @body to null

Trigger for sending dbmail, there is one variable "DIA" that is converting the @body part of my dbmail to null when it is used, the trigger does generate the e-mail as designed as long as this variable is not used, and when it is used a e-mail is generated with nothing in the body I have tried decla...
Dru
0

votes
0

answer
4

Views

Inserting a default GUID value into table columns

I'm trying to seed a table, and for the initial data load I would like the CreateBy column to have the value of the default GUID 00000000-0000-0000-0000-000000000000. Is there a simple way to do this? Is there a way to get NEWID() to generate the default value?
PixelPaul
0

votes
3

answer
26

Views

Add triple conditional in CASE clause

I have a update clause with CASE clause like: UPDATE [D] SET [DesignTypeGuid] = (CASE WHEN([D].[DesignNumber] < @CurrentContractedDesignNumber) THEN @BDTypeGuid ELSE @COTypeGuid END) My question is how can I add a third conditional, I mean I also want to do is [D].[DesignNumber] = @CurrentContracted...
Jonathan
1

votes
1

answer
422

Views

Deleting Tree-Nodes in a SQL table without a cursor

I have a tree of strings stored in a sql table, this is the definition for the table. CREATE TABLE [FileTree] ( [ID] INT NOT NULL, [Name] VARCHAR (MAX) NOT NULL, [ParentID] INT NULL, [UserID] VARCHAR (MAX) NOT NULL ); And I have a recursive delete procedure for delet...
Dave Demirkhanyan
0

votes
2

answer
27

Views

How to modify Count(*) result

Need to modify the Count result. Input Query: SELECT COUNT(*) COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '' AND COLUMN_NAME IN ('**Column1Name**','**Column2Name**') Output: 2 My requirement is, result should be '1' if both the columns get present in table. Could some one help...
Kms
0

votes
0

answer
10

Views

Get distinct code client with different language

I have a table that contains code client, client name, language. How could I get distinct client code and name select distinct code, name But it sometimes returns name with duplicate name. when name it is not correctly written.
test test
0

votes
0

answer
10

Views

How to filter out SQL Select without using “where” condition?

I would like to give a specific permission to user. Example; A user cannot see Software department from Department column in the Personnel table. So when I execute "Select * from Personnel" it will automatically filter out the Software department without using where condition. Is this possible ?
Aniki
1

votes
3

answer
550

Views

SQL Find the first non null value with arbitrary order by

Given the following table: ID | Value ---------- 1 | NULL 2 | B 3 | C 4 | NULL I want to get the first non-null value from the Value column according to different ordering, e.g.: SELECT FIRST_NON_NULL(Value ORDER BY ID) FROM MY_TABLE This will return B SELECT FIRST_NON_NULL(Value ORDER BY ID DESC)...
user1589188
0

votes
0

answer
12

Views

Modify Script to make Teachers with same EventId which are in different rows in 1 row

This is my initial table: EventId | StartDate |Category |Event Name | Teacher | Paid | PayTier 1 10/16/1995 Retreat Hello Franco 1000 Tier 1 1 10/16/1995 Retreat Hello Jeff 1000 Tier 2 1 10/16/1995 Retreat Hello Azeem 1000 Tier 3...
Azh588
1

votes
1

answer
15

Views

How to generate calendar table having begging month date and end month Date

I need to generate calendar table between two dates having begging month date and end month date. And if its greater than today, then it should stop at current date. Should look like this: As you can see the last value for column Eomonth has today's date (not the end of the month) Thank you
Oleg
0

votes
1

answer
18

Views

How to convert varchar to time hh:mm

I have varchars representing military times: a 2130 -- 21 hours b 103 -- 1 hour c 10 -- 10 minutes d 1 -- 1 minutes the left two characters always represent minutes. In example c and c above 10 and 1 are always minutes. example b 103 has three characters. 1 is hour 03 is minute in this case. Ho...
Jacob Jolliff
1

votes
2

answer
183

Views

Why does my record count blow up with a left join?

If I run this: Select * FROM RAW_DATA WHERE Portfolio like '%deposit%' I get 131047 records. Now, I join to another table, and I want all records from RAW_DATA and matches from another table, like this: Select * FROM RAW_DATA AS RawData LEFT OUTER JOIN DATAHIST AS HIST ON RawData.Parse2 = HIST.CONT...
ryguy72
0

votes
1

answer
11

Views

SQL Server - Query to extract data from stored grids (varchar)

I have a table called dbo.Client with a column Client_Location. The column holds information on customer locations using a grid system. So, our HQ is at 0,0 and our customers approx location in miles from the HQ is stored the same way e.g. -1,9, 7,11, -5,-5, etc. I am using the data type Varchar to...
Trax101
1

votes
2

answer
31

Views

Multiple rows in table with values from another table

I am struggling with following issue: Table1: Table2: Expected result: Basically I want to multiple rows in dates table with rows from User table. Is it somehow possible? (using TSQL).
Juraj C
1

votes
3

answer
52

Views

LEN() & CHARINDEX() problem in formatting field

I have the following query: UPDATE P SET Street = TRIM(LEFT(FormattedAddress, CHARINDEX(',', FormattedAddress)-1)), Town = TRIM(RIGHT(FormattedAddress, LEN(FormattedAddress)-CHARINDEX(',', FormattedAddress))) FROM Person P and it's suddenly started failing with the following error: Msg 537, Level 16...
Philip

View additional questions