A way to not repeat expressions in a SQL Server string manipulation query

I have the following sql: SUBSTRING(Location, CHARINDEX(‘ : ‘, Location) + 3, CHARINDEX(‘ ms – ‘, Location) – (CHARINDEX(‘ : ‘, Location) + 3)) (Location could be something like this: 1dasev : 232 ms – 323-asv3R and the substring would return 232) If I wrote this in C# I would not re-calculate CHARINDEX(‘ : ‘, […]

Select only if more than X occurences

My data is something like this: Client Number | Order Date | Order Amount | Sequence (created with Row_Number()) I have created a sequence with Row_Number(), so I can see how many orders a client has. If I use WHERE Sequence > 3, I lose the orders prior to 3. I can’t use HAVING because […]

Double counting in Table

I have a table of Employees, and related table of the Group of Employees The relationship is one to many, when the – id from the table Employee groups can appear many times in the table – Employees I want to know which employee group contains the most Employees. Or, in other words, some of […]

Attempting to change databases using cursor loop, but database won't change

I’m writing a script that, when executed, will drop any tables in a list of given DBs if the DB Name + Table Name combo does not exist in a table named CleanUpTableList. All DBs reside on the same server. I am using SQL Server 2014. I am trying to do this by creating an […]

SQL – Value difference between specific rows

My query is as follows SELECT LEFT(TimePeriod,6) Period, — string field with YYYYMMDD SUM(Value) Value FROM f_Trans_GL WHERE Account = 228 GROUP BY TimePeriod And it returns Period Value ————— 201412 80 201501 20 201502 30 201506 50 201509 100 201509 100 I’d like to know the Value difference between rows where the period is […]

Delete Specific Duplicate Data with Condition

I have a table which contains some duplicated data and I want to delete specific entries from it based on the value stored in the Time column. In my table, ID: 504289 has 3 entries: Date Time Place ID 20161128 2016-11-30 12:54 East 504289 20161128 2016-11-30 12:55 East 504289 20161128 2016-11-30 13:10 East 504289 I […]

How do I drop a view from all databases on a server

I’m trying to drop a specific view from most databases on a server. Pasted below is what I currently have. It completes successfully but it does not actually drop the view. What am I missing, why would it not error but not actually drop the view? Also, When I run the exec statement in a […]

Sql Query to return 3 rows for every entry with the same value in column?

OK, I’m a little outta practice on SQL Queries here, I have a table with thousands of entries. Each row has a unique Id but there is a column named EquipmentId which is not unique and would be present in several rows. I want to return 3 rows for every EquipmentId and if there is […]

Transpose in SQL Server 2012

I have the following: Country | StateCity ——–+———— USA | MO USA | LA USA | OH CANADA | Ontario CANADA | Toronto and am looking to transpose and unify the header with a result like USA CANADA MO Ontario LA Toronto OH

How split a string a string based on position in SQL Server

String input: xxxxyyyyzzzz String output: xx xx yy yy zz zz Please split the above string in SQL Server. I am trying to use replace function something like PARSENAME(REPLACE(@str, ‘,’, ‘.’), 2) but it’s not working

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.