I’m currently implementing a search function in one of our grids and when doing so, my query goes from running in 1 second or less to running in about 16 seconds (I’ve added suggested indices, etc). The data returned from this query will be the parent’s id, parent’s name, most recent c…
Tag: sql-server
Select the most popular month
This is my sample data. I want to select the most popular month for borrowing. This is what I’ve tried so far: Answer In SQL Server, you can use select top (1): If there are ties, this returns an arbitrary matching row. If you want all of them, use TOP (1) WITH TIES.
tsqlt FakeTable not rolling back after unit test executed
I have an extremely simple unit test where I am testing the functionality of a tsqlt.FakeTable. However, after running the command and inserting data, it does not roll back to the previous state. Current table: [product01].[batch] After executing the below code, the table is not rolled back to the above. It o…
Calculate Number of Consecutive Days Where a Condition Applies Across Two Columns
I have a table similar to below: I need to calculate the number of consecutive days where “Balance <0” for reach ID and for each distinct Date (including the date itself in the calculation). Each Id might have several balances in a given date either positive amount or negative. Even if one bala…
Update just one of the row that have the same ID SQL Server Query
I have two table. Table1 and Table2 , i want to update Table 2 with Table 1 field, and i want to update just one of the row with same CN number in table2 Answer Use a join with row_number() to get just one row per id:
Why collation doesn’t work with for xml path in subquery?
I usually use collate SQL_Latin1_General_CP1251_CI_AS to remove accents (diacritics) like this. escrzyaie-escrzyaie Unfortunately, after one of the search queries stopped working, I found out that collation is ignored when used in combination with for xml path in subquery. ěščřžýáíé-ěščřžýáíé It is possible t…
SQL server: Question about query group and auto id
i have a table which has 2 columns like this, picture is input and output: Explain input: 2 column is 2 person who relation ship together. Exam: A relation with B, C,D,H Output: i want to merge 2 column , with Column group ID auto and column RelationShip ID group auto: i tried query: row_number() OVER (ORDER …
Combine ORDER BY and GROUP BY and a counter
I use this SQL query to get customer order lines from an imported CSV file and get this result Now I would like to produce 2 different results, where one, in addition to order by ordernr also group them by their customerid and another where I get a counter (the cnt column) with how many times a customer/custo…
Creating a PIVOT with dynamic rows and columns – SQL Server 2016
First, let’s define the tables: The following query gets all the answers with questions for a specific reviewee: And the output looks more or less like this: What I want is output that looks like this: The number of questions and reviews/review_dates is not known until runtime. I am ultimately writing t…
Divide Zero error when calculating Sales % in SQL
I keep getting the following error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. I’ve seen some posts about using IFNULL which I have tried. For example: But this returns the following error: ‘IFNULL’ is not a recognized built-in function name. What I’m…