Answer First of all – for the INSERT – I would use a DATETIME2(3) column with a default constraint – then you do not need a trigger: Now, each time you insert a row and you do not specify the dtEnter column in your list of columns to insert values into – it will automatically be set by SQL Server:
Tag: tsql
Get max date based on another column in SQL
I have a table variable which consists of columns id, date and status as shown below And follows is the sample data Also declared a variable as shown below. From this table I need to get the row which containing the maximum value of date( MAX(dates) ) where status is ‘ABC’, from the above sample values, I should get the
SQL SELECT “pair” records
There are cities, suppliers, and customers tables. Each supplier, (as well as customer), is located in one and only one city. How could I select pairs of supplier and customer within the city, where each of them is mentioned only once? P.S. if there is no “pair” NULL should be instead. E.g.: cityId supplierId customerId 1 1 1 1 2
Pivot data T-SQL
I have the following table: I want to pivot it to the following table: I tried to work with the following example: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/ But in this case a SUM operator is mandatory. I don’t want to use the SUM operator for my data. What would be the best approach to go. Eventually I want to use the data again, I
TSQL – join two tables with TotalCounter and Pagination
I’m trying to join 2 tables (one to many relation) that include all the columns from first table and only the number of rows from the second one. Use case: one Service has many Reviews. The query looks like: The error is I need to include either average or group by clause but how would that look like I can’t
Get first and last Order and the highest value Item in each order for each Customer, all of which are separate tables
I need to find the first and last Order for each Customer by OrderDate, and the name and SKU of the item with the highest business volume in each of those orders. For reference, the Customer table has >150k records, and Orders and OrderDetails (these are the Items) a lot more. Note: Both Orders and their respective items should be
Update and insert from one table with multiple same ID rows but different column attributes to second table
I would like to update the records from one table to another table and if there are new records then insert those records. In this case, the new records are having the same ID but different column attributes. When I am running the update and insert query, it’s only considering the last entry from table one. It seems that it’s
Recursive select that selects rows based own plus childrens values
I need to select rows in a table like this: Select all rows in the table where both conditions are met: Condition 1: the value column should not match with any value in table v Condition 2: no decendent (on any level, ie: child or sub child, sub- sub- child etc) has a value that matches with any value in
Flag “yes/No” if the subsequent row has same ID
I have data like this. If the same id is present in the next row, I want to flag as Yes. If it is not present then Make it as ‘No’. Can you kindly help me with the query? Thanks Answer The problem with multiple rows for the same ID and no other column that can be used to futher
Second member of Azure AD group can not create schema in Azure SQL database
We have a curious issue with our Azure SQL database with AAD authentication. We have created a role (dbt_user) with all permissions, and excluded some schemas: Then, we add an AAD group (myAADGroup) as user, and add this to the role above: There are 2 members of the AAD group: user1@domain.com and user2@domain.com. The first user can log in, and