I need to write a query that displays time intervals that go on without interruption. Example: Input: Output: I think we need to use the function DATEDIFF line by line. The problem is that I don’t know how to access indexes. Or can this problem be solved much easier? Answer This is a type of gaps-and-islands problem. Use a lag()
Tag: tsql
How to insert the result in the desired row? SQL Server
I need the result that I got to be inserted in the right row. That is, instead of WHERE id_prod = 1, there was something that inserted the desired result in all rows. I’m not sure I made myself clear, but I hope that you will understand. Answer I think you want There is no need to use a variable
SQL select records with current month
I have a field name textbook date field name NewMonth The data like this My goal to select the records with current month (2020-04-XX) My query and not working. Can some one correct my query. Thank you I think -1 for current month, -2 for last 2 months , -3 for last 3 months andso on Answer My goal to
How can I join a table to itself to get the values needed?
So I have a table that I’m trying to join to and I’m not quite getting everything I need. So I have a table that looks like this. I want to see if there is a way to join this table to itself to get the rest, so I see that when I see the code ‘0013’ then I should
Query to get all companies that have products with all specified attributes
Let’s say I have two tables (1 to many): table company with fk id and table product with fk id and also fields sId and tId (if sId has data tId is null and vice versa, not sure if it matters). I want …
Calculate time from begin date in SQL
I’m trying to tally up the number of unique ID accounts that are active for more than a year as well as include how long each one is active. The problem with the code I have is it’s not including …
TSQL: How to use charindex ? – Finding a specific word in a variable string between specific characters
I have this string: ‘Level 1 – Level 2 – Level 3 – Level 4 – Level 5 – Level 6 – Level 7’ I would like to get the Level 6 word and level 5 word apart. I did try to google a few things, but they are not really helping me in this case. Because the levels will
Creating a “specific” form of XML, without string concatenation in SQL server T-SQL
I have some weird requirements that demand a specific XML format. Due to short deadlines and my lack of skills I decided to do a fast solution where I generate the XML through string concatenation. and this gives the output which I need. However, I’ve been told numerous times that this is not best practice, and creating XMLs through string
How can I add a Count with a condition from another table to a column/equation?
I currently have a table that calculates some player stats Results I am looking to also add the stat “Overall average % of field beaten”, but I am not sure how. The relevant columns in “EventResults” table are ‘EventID’, ‘PlayerID’ and ‘Position’ Image of EventResult table The equation would be But I can’t wrap my head around how to get
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 Simms’ THEN ‘Tommy Simms’ or when FirstName = ‘Robert’ AND LastName = ‘Jones’ THEN