ID _From _To Code 1 08:00 11:00 4002138 2 15:00 17:00 4002138 Example 1 Example 2 Please tell me where am i wrong? Answer If you are referring to the current time, then you can use:
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,
Flag items not having the specified combination in a column
I need to flag ID#s not having a specified combinations of DC and Storage Locations. For example: Articles must have the following combinations for each DC: DC Storage Location DC01 ABC, BCA, DCA DC02 ABC, BCA DC03 ABC, DCA My desired outcome would be the below as I’d like to show the Storage Location missing. ID# DC# Storage Location Flag
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
Convert values in related table to comma-separated list
I have two SQL Server tables: I’m trying to the following result where the related activities are in a comma-separated list: I tried: Can someone help me with this SQL query? Thank you. Answer You could use OUTER APPLY to aggregate the string if you’re using SQL Server 2017 or higher.