Let’s say I have this table: employeetable: empid joindate location HRupdatedate Changes 1 2002-01-05 Delhi 2021-03-01 New 2 2009-09-09 Mumbai 2021-03-05 New 1 2010-06-27 Hyderabad 2021-03-03 Transfer 2 2015-11-02 Delhi 2021-03-06 Transfer 3 2020-01-01 Mumbai 2021-03-06 New 4 2007-07-30 Delhi 2021-03-04…
Tag: sql-server
SQL query on certain database names
I currently have many databases on a server. I want to run a query on only databases that end in “AccountsLive”. Not all of them end with this, so I kind of want to do a wildcard %AccountsLive query and not using a WHERE NOT name IN(‘master’, ‘tempdb’, ‘model’, …
How do select only swipe-in users from the table which has all swipe records
I have a SQL Server table task_swipe_rec which has only 6 columns. I want to know how many people swipe in right now for the assigned task and the list of minutes they spent on those tasks till now. I am expecting to get the list of tasks that are not swiped out (not completed) and number of minutes spent
List No of minutes worked by an employee using self join
I have a SQL Server table emp_swipe_rec which has only 6 columns. I want to know how much time (in minutes) both employees A and B worked for today (last 24 hours). Employee A already swiped out for the day. Employee B is still working. We don’t store two different columns for a swipe in/swipe out. We u…
SQL Multiple queries at the same time creating deadlock (UPDLOCK)
So I have a problem with my database giving me a deadlock. System.Data.SqlClient.SqlException : Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I have a project that starts to run and then splits into x amount…
Conditional Aggregation query not giving correct output
I am trying to find average using conditional aggregation. I have 2 tables in SQL as below RentalExtension Table: RentalItemsID ExtensionDate ExtensionBy_UserID 7 2020-07-27 10 1 2020-07-28 7 The user table contains attributes as listed in select query When I run the query above I get Extension Rate for UserI…
Updating field value based on data across three separate tables
I have been trying to UPDATE the values of a field (IsException) from 0 to 1 in a table (g) based on corresponding data from a separate table (a) in two fields (name, date) when the combination of both appear in that separate smaller table. The problem I am running up against is that the ‘name’ fi…
Store hourly data efficient way
There is a requirement to store hourly data in SQL Server 2016 and retrieve. It’s an OLTP database. I will explain with an example: we need to capture temperature of each city of a country and store on hourly basis. What would be the best and efficient design to do this. The data would be stored for a y…
SQL: How to partition data by the key and transpose the triples in the defined order? (The tool for a human-language translator.)
I do have the table with translated texts to (possibly) many languages. When new text is entered in the chosen language, the lang_hash is calculated. If the text is not bound to any existing text in other languages, the lang_hash is repeated in the key_hash, and–together with the lang code–the new…
How can I use Except all in SQL Server?
I’m trying to use this query statement in order to except daysoff and a duration, let’s called holiday from a calendar of a specific month . This what I have tried : and this is the function of Get_Calendar_Date To except the daysoff ( weekends ) , I have used except , but what I got is something …