Let’s say we want to insert two users and I want to know the userId of each record I inserted. Example: Db: User.lookup database with these columns: UserId(PK, identity) | Username Setup, insert …
Tag: sql-server
Create group column with values based on join [closed]
I have two tables 1) a customer table 2)Account table. I want to see what accounts are primary and which are secondary accounts. In one table I have accountRowId and AccountNumber. In the other …
Query to “PIVOT” the results of SalesAmount & CumulativeMonthly for specific YEARS
I have a database table that captures every Sales Transaction: Transactions ( ID INT, TransactionDate DATETIME, SalesAmount MONEY ) I need to run a T-SQL query to produce a Sales …
T-SQL query to summarize sales for ALL YEARS: with total per month per year, and cumulative monthly amounts
I need to create a Sales Report that shows all years sales per month, and cumulative sales. The database table is simple: I want the results to look similar to ExcelSheet below (I am showing only 2017/2018 amounts, but actual query needs to return results for all available years according to TransactionDate) …
PROCEDURE Return always 0 for string statement
I have a procedure that can be changed dynamically by user for multi column and I write it in SQL when I run it. Everything is OK in SQL and Server Explorer in Visual Studio but when I want use it in …
SQL Server performance optimization
I have table to store files. I store SHA256 hash in a column to uniquely identify a file. Before inserting new file I check for duplicate file using the hash and if it exists already I don’t insert. For example my query can be: Right now there are only a few hundred files. When this number grows to a fe…
Sort a VARCHAR column in SQL Server that contains numbers?
I have a column in which data has letters with numbers. For example: When sorting this data, it is not sorted correctly, how can I fix this? I made a request but it doesn’t sort correctly. Answer This should work
Joining two different tables with a common third table on a common column
Here are the tables Table: Status ID, StatusDesc 1, New 2, Active 3, Cancelled 4, Complete Table: Order (foreign key relationship with Status table above) ID, OrderNumber, StatusID 1, 1001 , …
How to rename database file programmatically?
I have two databases: database_A. Name of file is database_A.mdf. database_B. Name of file is database_B.mdf. Then I what I do: Drop database_A. Rename database_B to database_A: sp_renamedb ‘database_B’,’database_A. However, name of file is still database_B.mdf. Is it possible just by code r…
Select Top N rows plus another Select based on previous result
I am quite new to SQL. I have a MS SQL DB where I would like to fetch the top 3 rows with datetime above a specific input PLUS get all the rows where the datetime value is equal to the last row of …