I utilize SQL SSMS. Due to the following, SQL prompts error. After I utilized the same script with many other inner joins the following error remains. I have tried to modify my script, and I keep getting the same messages: [My script] Answer You have: But the error message is clear: more than one table in you…
Tag: sql-server
Subtract sum of column from a column in sql
I am inserting First four column multiple times, with new paidVal . Now I want to get sid,snameVal, balance I have used a inner join on studenttbl and feetlb. here I have tried. But I am getting 3 row when I should get 2 only. In the table there is a field cNameVal, and as much different value i m
Why my after update trigger is executing on insert although I have separate trigger for insert as well
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…
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 v…
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.: cityI…
Exclude records where particular value exists in related record
The below dataset displays the audit trail of a “review” record that has gone through a number of statuses. What I’m trying to achieve is only display the case ids where the auditcasesubstatusname is one of (‘Checklist Complete’, ‘All Documents Available’, ‘Revi…
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 …
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 …
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 r…
SSMS – get DDL of all views in a schema
I have a SQL Server database with several schemas, each of them contains several views. I need to create the same views in other databases so I would like to get the DDL of all the views in a script, generated through SQL. In Oracle it was quite easy, accessing ALL_VIEWS and referencing the QUERY column. How …