I am working on a project for a wine importer, in which need to calculate the accumulated storage charges per SKU up to the delivery date. I have data from the warehouse recording the Date_In and …
Tag: sql-server
Seed Data using merge statement including data from external table
I’ve been provided an excel file with data that I am supposed to merge into a database. From this data I am concatinating a string in this format: ‘Maastricht’ here is the name of a city, which I need up in a foreign table during the insert. This is the script that I have come up with: To Re…
Return Most Recent Record group by 2 ID fields
I need to display the current status of a part (‘approved’ or ‘not approved’) I am setting up the status table as MaterialNo, ToolID, Status, Asofdate the idea is I may have several tools approved and several not approved. I want to be able to see the most recent status for each tool b…
Rewrite MS Access SQL statement to SQL Server
I wish to convert the following MS Access SQL statement to SQL Server. All of my attempts are resulting in different results from the old & original data. The new Query is: The problem caused by grouping date column in new statement, but in old one it is used in Last function to avoid grouping it and stil…
SQL server query with multiple (same ID different date and different image) (filter by last date)
I want to fetch data from a huge db that same ID number each yea , but different photo for each date I want query to filter and show result with Last date including (ID and photo) to the given ID Answer Possibly the most efficient method is: For performance, you want an index on nid(id, date).
INSERT with autoincrement not working in SQL Server (with DB replication ?)
After no row deletion, but after getting the 60 existing rows by local subscription to replication from another SQL Server instance, I’m inserting new rows with: But this will fail since the ID will start over somewhere (9 in this case), instead of starting at the last ID used +1, ie., 61 in my case. Sy…
Query to Id referencing to an ‘Id’ to the same table
I have a table AMZ_EMPLOYEE_DETAILS with Employee Id, Employee Name and Supervisor1 and Supervisor 2. The Supervisors are employee itself but represented by their name. My task was to replace the Supervisor names with their Ids. I have used the following query to obtain the solution but it uses sub-queries an…
SQL Server – Close external port and work as localhost when sql server and application are on the same server
My database and my web application are at the same server at the moment and I would like to access SQL Server as localhost and close the SQL Server port for remote connections. This is my current …
Concat rows for employee roles
I’m using SQL Server. Looking for similar functionality as GROUP_CONCAT or listagg functions provided in other Databases. Seems like STUFF function is provided to concat rows. Here’s my attempt. Employee Role EmployeeRole Expected Output Answer In SQLServer 2017, you can use aggregate function STR…
Trying to use the condition in a case when expression as the result
I have a CASE expression and for one of the WHEN clauses, I’m selecting data from a table and checking if it exists. If it exists, I want to return one of the columns from that row in the THEN …