I want to consolidate records based on a time gap of 5 minutes between the current row-end date and the next row start date. How can I achieve this T-SQL? Before Consolidation, the table is like below – It should look like below after consolidation – Answer This is a type of gaps-and-islands problem. For this problem, use lag() and
Tag: tsql
Why do I get a conversion failed error when I use my attribute on my WHERE clause but it works when I don’t?
Why is my query returning a result when I run the following query: SELECT MAX(CAST(IssueDate as Date)) FROM (SELECT IssueDate FROM [Transient].[Commissions_TIB] WHERE ISDATE(issuedate) = 1 …
How to insert values into the following table by incrementing the date column by 1 day for the next 23 days?
I am using SQL Server 2014. I have a table names T1 (extract shown below): I need to update table T1 for period 2020-10-13 to 2020-11-04 with the following logic: All rows to be appended to the table will have the same values as those for ReviewDate on 2020-10-12 except the ReviewDate which will increment by 1 day until 2020-11-04
T-SQL: removing Time and Preceding characters from nvarchar column
I have a comments column in a SQL Server database table of type nvarchar and typically contains text along the lines of: I am trying to find a way in the select statement to remove the character pattern of ‘#:#:# AM/PM’ and everything preceding it leaving me with “Blah Blah…” In doing so I don’t want to remove legitimate uses
Did this SQL update statement delete records? [closed]
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 2 years ago. Improve this question I ran an update statement like this, last night. where it is joining local data Table_V2 with data on another
Returning Records by Week
I’m trying to come up with a way to return a result set from the below data without a loop that shows the number of records by Team for a particular date range by week. I’ve got a Date table (https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/) that has every day/week/year referenced, but not sure how to connect it up. I’ve got this query: Where it
SQL agent job creation error: Job ‘XXX’ is already targeted at server ‘YYY’
I am trying to create and SQL agent job on SQL Server 2016. Here is the use of sp_add_job. EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’TEST_RUN_A_JOB_STEP’, @enabled=1, @…
How to include OPENJSON in View?
My JSON object is stored in the table (single cell). Right now, I’m reading the cell and saving the value to @json NVARCHAR(MAX) , but that obviously doesn’t work in views. How can I do something like this? Answer You can use cross apply to apply openjson() to each and every row of your table:
SQL. Comparing value from current row versus the value resulted from the same comparison of the previous row
I am trying to create a script that would compare and get the higher value from current row versus the value resulted from the same comparison of the previous row. For example: Row 2 Column ‘Given’ > Row 5 Column ‘Result’. Therefore, Row 2 Column ‘Result’ gets the same value in Row 2 Column ‘Given’ Row 3 Column ‘Given’ <
Select data from multiple existing tables dynamically
I have tables “T1” in the database that are broken down by month of the form (table_082020, table_092020, table_102020). Each contains several million records. There is a second table “T2” that stores a reference to the primary key of the first one and actually to the table itself only without the word “table_”. There is also a third table “T3”,