Skip to content
Advertisement

Tag: sql-server

SQL deadlock on concurrent delete

I have an API endpoint to delete children of a parent, unless a child is specified. The SQL is: (This query is sanitised, so ignore the fact that I’m not doing ChildID != @ChildIDToKeep) The executed SQL is also wrapped in a transaction. My web application attempts to send hundreds of requests to this endpoint concurrently, so this SQL transaction

sql query for finding ID numbers on date range

I want to get the ID numbers for the last 24 hour range. Say I run a task at 4:00AM each morning and want to get the previous 24 hours of data going back to 4:00AM the previous day. I need to get the id codes to search the correct tables. If the data is like this what would be

Arithmetic Overflow Error if Varchar is over 999, WHY?

I discovered a weird issue in my database, I was able to fix it, but I do not understand WHY the error occurred in the first place. I’m using Microsoft SQL Server 2017. The following code returns an Arithmetic Overflow error: Returns Error: Arithmetic overflow error converting varchar to data type numeric. But what is weird is that the following

Delete all but the newest with specific filename

I migrated a file logger to log to the database instead of to disk. This caused many duplicates in the database, whereas on disk the file would have just gotten overwritten. I want to delete all the “not newest” rows given a specific filename, what SQL would do this given the following table: The table: log_rules The columns: log_rules_id, file_name,

SQL Server: join data but in single row

I have a table Invoice that looks similar to this: order_id completed_at subtotal discount_amount handling_amount 100 07/01/2021 10.09 0 0 101 07/04/2021 200.30 0 0 102 07/04/2021 54.10 0 0 103 07/06/2021 12.00 0 0 And I have another table InvoiceDetail: order_id product_id qty amount 100 1234 1 1.09 100 Shipping1 1 4.30 100 SalesTax 1 .67 101 987 2

Accurate DateDiff

I have 2 time fields in SQL. I want to calculate the difference between start time and end time in hours. From 7 AM in the morning to 7:30 PM it would be 12.5 hours but the datediff function returns 12 hours because it returns an integer. I also tried converting it to seconds: All of these return 12 hours

SQL consolidate overlapping dates based on criteria

I’m trying to merge overlapping dates between Admit and discharge dates of patients. There are a few edge cases which I couldn’t cover in the query. Input Expected Output Query I used the logic that was here But this doesn’t cover the edge case for ID 2 and 3. Also the subquery is slower when the data is huge. Is

Advertisement