I have a procedure that selects an offset of rows from a table: In addition to @Start and @Length parameters, the procedure also receives @SearchValue NVARCHAR(255) parameter. @SearchValue contains a string of values delimited by a space, for example ‘1 ik mi’ or ‘Li 3’. What I need is…
Tag: sql-server
Create a View in SQL with a Concatenated Column > Then use Case Statement to affect values
I created a view with a concatenated column named DisplayName, using the code below. Is there a way to change specific DisplayName values using a case statement (or a better method) Something like: case when DisplayName = ‘Robert Jones’ THEN ‘Bob Jones’ when DisplayName = ‘Thomas…
Calculating time with datetime values
I have two tables where one has tasks, other has actions. In the task table I have tasks that ha ID, start date (when the task was created) and end date (when the task was closed). In the action table I have actions that were done in the task table so it has ID, task_ID, action_ID and time of the
Problems finding out Active users due to Hire/Resign Date
In my Employee table in SQL Server I have two datetime columns: HiringDate and ResignDate. I want to create a new column Status (Active, Inactive) in a view. If HiringDate is NULL or greater than today = Inactive If HiringDate is Active but ResignDate is earlier than today then Status also have to be Inactive…
Create table with the week numbers of the last 6 months, with their respective month and year. SQL Server
I’m trying to get a table that contains the week number of the last 6 months. I found some code in github, and I modified it to accomplish my goal. This what I have: — 2019 only used to show the info from 2020 and above, because I don’t want the week number of 2019 or below I get this
Subquery returned more than 1 value – STIntersects
I have three tables 2x tables of observational point data with a point geom column (for different time periods) A table of hexbins across the study area, with a polygon geom column Both are in the …
How to filter and count percentages and count in SQL
After a query I end up with a table like this tbl_1: category type count cpg b 1 auto c 1 cpg c 1 auto v 1 I would like to calculate the …
Find how many times the department of an employee has changed
I have the following table Employee storing any updates made on an employee: EmployeeId DepartmentId Status From To 44 30 Recruited 01/01/2017 06/03/2017 44 …
SQL Server Query filter with order is slow
I have been struggling with this for a while. I have a database with three tables (each of which has millions of records) as follows (removed some columns for simplicity): There exists indexes As follows To eliminate the possibility that the slowness is because of the selected columns, I only select a fixed v…
Retrieve a row of data with the most recent date when another row ‘X’ in T-SQL
I have a database of customers who have an effective date and end date of their membership, both separate columns. The data is a bit dirty, however, and a customer can have multiple rows of data, only one of which is their most recent membership record. A member is considered “active” if they have…