Let me open with: SHOWPLAN permission denied in database ‘MyDatabase’. With that out of the way, I’ll layout my situation. So, The database I work with has a view that executes fairly quickly. …
Tag: sql-server
Query returning data outside the scope of the WHERE condition
I’m trying to retrieve summarized data from the table depending on DateTime and state. The problem is I need multiple state values ex. ‘where 1 or 2 or 3’ SELECT SUM(ticketTotalAmount) FROM tickets …
Divide by zero error encountered when using group by
I’ve got the below piece of code in my query: CAST(COUNT(C.DateChanged) * 100.0 / COUNT(A.LastPassedBackToSalesDate) AS numeric(18, 2)) AS PBTSbutActived Both fields are of DateTime datatype. The …
How can I get rid of having to prefix a WHERE query with ‘N’ for Unicode strings?
When searching for a string in our database where the column is of type nvarchar, specifying the ‘N’ prefix in the query nets some results. Leaving it out does not. I am trying the search for a …
How do I write a case statement to return duplicate records?
I have a table of names and addresses and want to return the full list of names and addresses with an extra column that identifies if the address is a duplicate or unique address. There are several duplicate address entries that I can see when I run a more simple statement — or but I need to return the …
Wrong Opening Balance for some rows
I want to calculate Opening and Closing Balance of a business application. But for some rows wrong Opening Balance is producing. I have following Data Tables: SupplierPayments DateOfPayment Bill …
How to update last record with second lat results [SQLServer]
I want to update the last entry of the table with Pass/Fail result from the second last row. And the query need to didnt fail if only one row is in the table. Here is the code what i have, but ist …
Dynamic SQL Select variable name
I have the following SQL statement, which works fine: DECLARE @TableName VARCHAR(250); SET @TableName = ‘TBL_Name’ SELECT QUOTENAME(@TableName) As TableName, HASHBYTES(‘SHA1’, (SELECT * FROM TBL_Name …
Minutes between dates when multiple records
Table ‘audit’ has fields id, old_status, new_status, & changed_at. Status contains values Open, On Hold, and Closed. I want to determine how long audit was On Hold, simple calculation: SELECT …
Let SQL throw error then handle or prevent error completely?
In my code I generate a list of IDs then input associated data into a table with ID as the Primary Key. Is it better practice to let SQLSever throw an error and handle it (Integrity Error)? Or should …