A QA tester in my company noticed that the catch block in an insert..exec statement is incorrectly attributing the error to the outer procedure instead of an inner procedure. The inner procedure is being called in several different places in various stored procedures. It’s working fine, but I was tasked…
Tag: sql-server
Display total where row number is max row_number
I have a query that displays invoice information, and in final column displays the Supplier’s total balance, I only want the total to be displayed on the last line of that supplier’s invoice entries. ie. the results may contain 100 invoices for say 20 suppliers, each supplier having a different nu…
Query with group by with CustID and amounts daily and MTD
I have the following data: ID username Cost time 1 test1 1 2021-05-22 11:48:36.000 2 test2 2 2021-05-20 12:55:22.000 3 test3 5 2021-05-21 00:00:0-0.000 I would like to count the costs for the username with a daily figure and a month to date figure in once table I have got the following This will return the mo…
Highest Mark in each course with student name
I want to get the highest mark in each course with the title of that course and student name who toke that course exam. and i have these tables , first one students id name 1 Jhon Doe 2 Sarah Doe and Courses table id title 1 Math 2 history and i made a table for connecting students with courses
Updating SQL Variable
I’m attempting to create a variable that will hold a value, which I will then later use to select from a table using the variable value. This works perfectly the first time around, but any time I try and change the parameters to update the variable it returns blank. Am I able to use the same variable wi…
Cumulative sum of a column
I have a table that has the below data. I wrote a query that will calculate the sum of cumulative rows and got the below output . Now I want to to filter the data and take only where the POOL <=300, if the POOL field does not have the value 300 then I should take the first value after
CASE Statement in WHERE Clause SQL Server
I’m having trouble sorting this out. I want to see the fiscal quarters for Date_Received. When the @ReviewPeriodQuarter = 1 then I want the Date_Received months 10,11,12. If @ReviewPeriodQuarter = 2 then I want the Date_Received months 1,2,3 etc. SQL Server doesn’t like the BETWEEN part of this. T…
Group by the records and that particular group if condition matched then set final result like below
I want to group by the records and that particular group if the condition matched then set final result like below For each group, I want to check the following condition Answer You can use conditional logic: Here is a db<>fiddle.
Different performance from SQL Server query from Management Studio vs EF Core 5
I wrote a simple EF Core query that makes a select on a table using some where clause to filter data: start date and finish date between the actual date and a field (DescrizioneCommessa) containing a value. To get the raw SQL I just execute the statement: And the resultant query is: I notice that it takes ver…
SQL: IF OBJECT_ID() and IF EXISTS don’t work in conjunction with UPDATE statement
I am using SQL Server 2017, I have a View called Foo, what I’m trying to do is to check if Foo is a user-defined table, if Yes, do an UPDATE, else do not do anything. Here I check if the Foo exist and if it is a user-defined table (xtype=’U’). Since Foo is a View, so xtype=’U’ sh…