The data in my table represents physical locations with the following data: A municipality name, a state(province/region), and a unique ID which is comprised of a prefix and postfix separated by a dash (all NVARCHAR). Name State UniqueID Atlanta Georgia A12-1383 The dash in the UniqueID is not always in the same position (can be A1-XYZ, A1111-XYZ, etc.). The postfix
Tag: sql-server
Retrieve rows where Column1 != Multiple Values from Column2
I have 2 Location columns. I need to find a way to show only exceptions, where Location1 != Location2, BUT there are multiple values in Location2 column that can be associated with value in Lcoation1. For example: San Diego in Location1 column can have 4 possible values in Location2 column (North, Central, South, San Diego) and such rows need to
Count rows between time periods excluding time-overlap
I have a table where it holds some vehicle events, I need to count the number of event occurrences every 5 minutes. Sample : I was able to get the results by the following query : query results : The issue with the above results is that the query excluded Ids (15 and 28) the correct output should be :
How to order queries execution in SQL script so that they are executed in the right order?
Let’s say that I have three tables: Table References A – B A C B and I have created a SQL DDL script with these tables’ definitions: script.sql: IMPORTANT: The order of definitions in the script can be random! Is there are way/tool that would take the script as input and return another SQL script with sorted DDL queries? Example:
SQL Divide previous row balance by current row balance and insert that value into current rows column “Growth”
I have a table where like this. Year ProcessDate Month Balance RowNum Calculation 2022 20220430 4 22855547 1 2022 20220330 3 22644455 2 2022 20220230 2 22588666 3 2022 20220130 1 33545444 4 2022 20221230 12 22466666 5 I need to take the previous row of each column and divide that amount by the current row. Ex: Row 1 calculation
SQL Count each occurence of words separated by comma
I have a column in a table with words separated by comma. I need to count each occurence of each word My column looks like : (‘a, b, c’), (‘a, b, d’), (‘b, c, d’), (‘a’), (‘a, c’); (fiddle at the bottom) Here is what I get : But here is what I expect Here is what I’ve done so
Delete operation is slow and rebuilding index doesn’t seems to solve the issue
I have a simple delete query : ImportLog table has around 3 million records with all log details. I have non clustered index created on ImportLog table on ImportLogID. The fragementation is less than 10% but still the query is taking more time to execute but when I rebuild the index manually it is executed within a second. After a
MS SQL Convert rows to columns with Pivot
in MS SQL I have following table I would like to convert it to : I had a look at pivot table function, but could not get it work correctly. Any advice? Answer You coud try this:
Using Variables Passing Into MSSQL Stored Proc as part of queries
I want to use the value from variables that is passed into the stored proc. Is that allow? For example, I want to pass CID=5,SID=4 Into an Update Stored Proc and it looks like this: In which is like “DELETE FROM [User54]” But I want to dynamically done given the parameter Can it be done and how is it done?
Convert List Of XML Tags in varchar column to comma separated list
I have a table that contains a list of xml tags/values that I need to use to join to another table to retrieve their actual value and display the result as a csv list. Example varchar data: However, these values actually translate to other values: red, white, blue respectively. I need to convert that list to the following list: As