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 s…
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, So…
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 s…
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…
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…
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 wi…
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…
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 …