I’m joining 3 tables in SQL Server for the purpose of mapping users to their managers. The purpose of the junction table is to map their full usernames to their network ID. Here’s the query: The first 3 columns appear as I’d expect, showing the Network ID, Full User Name and the Manager̵…
Tag: tsql
How can I conditional join two tables to get results based on an evaluation (SQL Server)?
I have two tables in my database: Offices Positions How can I select all entries from Positions based on their Department and Section, but one above their level? I’m guessing I need a join with some kind of condition that gets evaluated to do this. For example, the Senior Dev position is at level 1. So,…
Running assignment of values with break T-SQL
With the below table of data Customer Amount Billed Amount Paid Date 1 100 60 01/01/2000 1 100 40 01/02/2000 2 200 150 01/01/2000 2 200 30 01/02/2000 2 200 10 01/03/2000 2 200 15 01/04/2000 I would like to create the next two columns Customer Amount Billed Amount Paid Assigned Remainder Date 1 100 60 60 40 01…
Track state of time series event in SQL column
This seems really simple but I can’t figure it out, working in SQL Server. I have time series data and I want a column to track the state of ON/OFF events for each row, so when there’s an ON event then the Desired Output column will have a 1 for each subsequent event until there is an OFF event. P…
Query nanoseconds from first entry
I have a data set keyed with a TestID and a datetime2 value where there are about 8000 entries per TestID, each is about 100 nanoseconds apart. I’d like to query all rows for a given data set (#40 in this partial example) and return the nanoseconds from the first record. Like this: I have a query to do …
Using multiple nested OR statements in WHERE clause makes query return incorrect results
I have a WHERE clause that has a nested OR statement, as seen here: The @NeedsFollowUp, @NeedsInitialVacc, and @NeedsTraining variables are all set by the variables above. When one or more of these are set to “1”, the query should return employee entries that match the criteria inside their relate…
Loop to create new columns
Say that I have a SQL Server table which has 4 columns MonthEnd, Gender, Weight and Age. Now, the MonthEnd go from 201901 through 201912. I have a process where a run a query which generates a variety of metrics, for instance sum(Weight) and average(Age) for each month. Right now, I set a where MonthEnd = 201…
Replace a recurring word and the character before it
I am using SQL Server trying to replace each recurring “[BACKSPACE]” in a string and the character that came before the word [BACKSPACE] to mimic what a backspace would do. Here is my current string: “This is a string that I would like to d[BACKSPACE]correct and see if I could make it %[BACK…
Counter Number of Times in State
I want to add a column which tells me the iteration count of how many times we’ve been in a state, ordered by date. We see we’re in state 1 for the first time, state 2 for the first time, then BACK to state 1 (iteration is now 2), the Iteration stays the same until we change state. I’d like
Could a SELECT inside of a transaction lock the table?
I would like to know if it’s possible that a select is blocking a table if it’s inside a transaction. It’s something like this: It’s a dummy example, but if there’s a lot of records in that table, and an API is receiving a lot of requests and calling this stored procedure, could …