Questions tagged [tsql]

19398 questions
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
543

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
181

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
30

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
1

votes
2

answer
32

Views

Query to only grab entities with multiple sessions in a single date?

I am trying to write a query to grab Employees that have multiple shifts in one date but with a start and end date query. For example, I am passing in these two variables: startDate = 1/14 and endDate = 1/20. If Employee Paul has two shifts on 1/18, I want to grab him. If Terry has one shift on 1/17...
Soulzityr
1

votes
4

answer
32

Views

TSQL query with aggregated data

I have a query that returns the min, max, avg and total for two different products. The client however wants the min, max, avg and total combined for both products. However I have an undesired result returned when I try to sum the two products and try to aggregate the date. The following SQL query r...
Juan Le Roux
1

votes
1

answer
25

Views

SQL - How to select data row with Maximum value within a specific column? [Part 2]

This is a continuation from my earlier question. Planning to create view with multiple join statements. And I have each separate components ready to assemble: select A.[Course Code], A.[Course Title],A.Result, A.[Employee No], A.[Employee Name], A.Section, max(B.ActStartDate) as [Last Training Date]...
hjh93
0

votes
0

answer
24

Views

SQL Server CTE Recursing too many times

I need some help figuring out the best way to build this query, and I think I've decided that a CTE is the best idea. I am open to other suggestions if there might be a better way. This application has several different object types. A simple hierarchy of the objects I'm currently concerned with is...
hmiedema9
1

votes
3

answer
49

Views

Group by doesnt give appropriate count in sql

To my previous post, I taken all data in temp table like this select * from #MyTempEmployeeTable data: version count1 productname ------------------------------------------ 10.0 2 Windows client Update 10.0 10 Windows client Update 10.0 486 Windows client Upd...
deepti
1

votes
2

answer
1.3k

Views

Execute stored procedure for each line in a CSV

I am trying to run a stored procedure using tsql multiple times in a query using each CSV line for the parameters. How would I go about looping through this? Cheers
Adam Howarth
0

votes
0

answer
8

Views

Cohort Analysis with SQL in SQL Server

I try to implement a Cohort Analysis with SQL in two different cases. The first one has data in the format of UserId, ApplicationDate, LastLogin In this case, what I do is SELECT DATEPART(MONTH, uw.ApplicationDate) AS Month, COUNT(*) AS SignUps, SUM(CASE WHEN uw1.UserId IS NULL THEN 0 ELSE 1 END)*1...
Tasos
0

votes
0

answer
17

Views

How pass parameters by using a function in stored procedures without creating variables

The correct syntax is DECLARE @end_date datetime = GETDATE() EXEC myproc @end_date But i want a shorthand that does not need the variable like the code below: EXEC myproc GETDATE() Is there a way to this?
Earl
0

votes
0

answer
6

Views

Replace Getdate() with store procedure

We have a job with couple of steps and almost all of the steps use getdate(), but instead we want to get the date from a specific table and column. The table includes only two columns status as ready (doesn't change) and statusdate (dynamic). The plan is to create a store procedure and replace the g...
DBA
1

votes
3

answer
5.3k

Views

Ordering by a field not in the select statement in SQL

I need to create a query that pulls only the customer_no column (because the software restrictions are as such, and I can't code it externally). But I need to be able to sort the data by create_dt (in reverse) column. The code/SQL is restricting me in using the following because in order to sort by...
YelizavetaYR
1

votes
1

answer
194

Views

Dynamic SQL Create User Statement

I'm trying to create a user script that will pull usernames from a table and put it into a variable, but I am running into a bunch of errors. Here's what Ive got. USE AP GO IF EXISTS (SELECT DB_ID('AP')) DROP TABLE NewLogins CREATE TABLE NewLogins (LoginName VARCHAR(128)) INSERT INTO NewLogins VALUE...
liltimmy
-2

votes
1

answer
18

Views

Variables in Stored Procedures

This is a general question related to MS SQL Server. I want to use a variable internal to my SQL stored procedure. All the variables I declare allow for user input. Is there a way to create a variable that is not exposed to a 'User'. I know I can make a variable optional. Thank You
RMichalowski
0

votes
0

answer
5

Views

Delete unchanged rows between two dates

I inherited a very old and huge (100M rows) table in SQL Server. It's the daily snapshot of accounts and balances but some of those accounts are not even changing anymore but are being added every day (don't ask why!) I want to: Identify and delete unchanged rows unless there is a change Create a q...
Ken
1

votes
1

answer
49

Views

SQL given multiple thresholds, get largest value below each threshold

I am given multiple thresholds (usually 2, but may vary). I would like to find, for each threshold, the row with the largest value lesser than or equal to the threshold in each category. e.g. Given category 2 and thresholds 5 and 10: mytable: category | val | data ---------+-----+--------- 1...
Eric
0

votes
0

answer
5

Views

My tsqlt unit test for a stored procedure is not included in the code coverage?

In my tsqlt unit test for a stored procedure. I fake my tables, Copy data into them from my AutotestDB. Then run the SP. Load the results into my actuals. Also insert into my expected, hardcoded values. Then I AssertEqualsTable and the unit test passes. However, in the code coverage report, the...
drinky
1

votes
1

answer
532

Views

How to update all fields dynamically in Instead of UPDATE trigger SQL Server

I am implementing Instead of UPDATE trigger in SQL-server, want to update all the fields without being in need to cache the values from the INSERTED table and then set it to the fields in the UPDATE Query .. How Can I do This ?! CREATE TRIGGER trg_Instead_updTable1 ON dbo.Table1 INSTEAD OF UPDATE AS...
Mina Nagy
1

votes
2

answer
2.1k

Views

Computed Column is not allowed to be used in another computed-column definition

I get Computed Column is not allowed to be used in another computed-column definition error message when i tried to use a computed field in another computed field. is there any workaround to accomplish this? Computed field1 ALTER TABLE DBO.[ASSy] ADD [CALC STOCK NO] AS ( CASE WHEN isnull([DIRECTION...
KAKA
1

votes
1

answer
744

Views

TSQL Merge using table valued parameter and assign auto generated / updated id back to the parameter

I've a table type named product CREATE TYPE [dbo].[udttProduct] AS TABLE( [ProductID] [bigint] NOT NULL, [ProductDescription] [varchar](500) NOT NULL, [ProductCode] [varchar](50) NOT NULL) And the stored procedure CREATE PROCEDURE [dbo].[uspCreateOrUpdateProduct] @ProductParam udttProduct READONLY,...
Darren Lau
1

votes
1

answer
402

Views

Is It possible to get JSON as an out parameters using sp_executesql

I am writing a stored procedure to try and track changes to objects in different tables. I am trying to construct SQL dynamically so am forced to use sp_executesql So far I have: SET @SQLInserted = 'Select * from ' + '[Diary.Day]' + ' Where Id=' + '13'+ ' for json path'; SET @SQLClash = 'Select * f...
user1
1

votes
1

answer
389

Views

Using sp_rename on a table updates the foreign keys for the table when documentation seems to say it wont

I must be misunderstanding the Microsoft documentation because it says Renaming a table will not automatically rename references to that table. You must manually modify any objects that reference the renamed table. However, when I ran the sp_rename on my table in addition to renaming the table it al...
Hopeless
1

votes
1

answer
175

Views

Group by with inner joins returning too many records

A table has a foundStatus column that's a char. I want to return a list of foundStatuses with a count next to each - this works: SELECT foundstatus, count(foundstatus) as total FROM findings f WHERE findDateTime BETWEEN '2008-01-01' AND '2017-06-24 23:59:59' group by foundstatus order by foundstatus...
niico
1

votes
2

answer
2.7k

Views

If NOT EXISTS in SQL not function

I put this in the SQL Query editor of SQL Server 2008 and doesn't work: If not EXISTS (Select * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= 'dbo' AND TABLE_NAME='PK01') CREATE TABLE [dbo].['PK01'] (Articulo varchar(MAX),Referencia varchar(MAX),Lote varchar(MAX),Cantidad Int,Ubicacion varchar(...
panki
1

votes
3

answer
361

Views

T-SQL Incorrect syntax near the keyword 'TRIGGER'

I created instead of trigger like that: CREATE TRIGGER ReadOnlyEvent ON TableName INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN RAISERROR('Tables are read only.', 16, 1 ) ROLLBACK TRANSACTION END; And that code works fine. But when i combine this with 'IF' i got an error: IF OBJECT_ID ('ReadOnlyEvent...
oginski
1

votes
3

answer
69

Views

Grouping a bigint column by different values

I am working in a Bank. Considering remain balance, we have a plan to give some points to our customers based on below rules: Balance ---- Point(per 1000) 1000-5000 10 5000-10000 20 10000-20000 30 for example a person who has 20000 in his/her account, we give him/her 450 points...
Saeed Esmaeelinejad
1

votes
2

answer
537

Views

SQL Server Log full due to active transaction

I have been trying to update a column in a table and I am getting the below error: The transaction log for database 'STAGING' is full due to 'ACTIVE_TRANSACTION'. I am trying to run the below statement : UPDATE [STAGING].[dbo].[Stg_Encounter_Alias] SET [valid_flag] = 1 FROM [Stg_Encounte...
Doodle
1

votes
2

answer
52

Views

Progress-ODBC-TSQL CASE Statement for Dynamic Column is Truncating Data

I am using an ODBC driver to connect to a Progress database via a Windows C# application. The problem I am having is my data is being truncated. SELECT CASE WHEN (table1_qty_comp = 0) THEN 'Pending' ELSE CASE WHEN (table1_qty_comp >= table2_qty_req) THEN 'Completed' ELSE 'In-Process' EN...
1

votes
2

answer
79

Views

TSQL: Using 'Output' clause in dynamic SQL

I'm using an Output clause in my Insert statement which requires use of a Table Variable. I also want the Table name to be dynamic so I'm using dynamic SQL but it won't allow use of a Table Variable. I get the error Must declare the scalar variable "@InsertedId". CREATE PROCEDURE sp_InsertPerson @Na...
Joe
1

votes
2

answer
19

Views

T-SQL Find node with matching text and extract value from sibling node in XML field

I've a table with an xml field having content like this: Make a Friend Meet and greet friendly animals at the only petting zoo in Disney’s Animal Kingdom park. Meet and greet friendly animals. Pet, brush and feed domesticated creatures Goats Handy animal brushes are available . Keeping Clean Guest...
polonskyg

View additional questions

Смотреть фильмы онлайн бабушка легкого поведения 2 фильм бесплатно