Tag: sql

SQL Server nested triggers

I have three tables: CREATE TABLE Rents ( RentID INT IDENTITY NOT NULL PRIMARY KEY, StartDate SMALLDATETIME, EndDate SMALLDATETIME, Price MONEY, RealEstateID INT UNIQUE NOT NULL, DealMadeByEmployeeID INT NOT NULL, CONSTRAINT CHK_Rents CHECK (Price > 0 AND EndDate > StartDate), CONSTRAINT FK_Rents_EstatesBasicInfo FOREIGN KEY (RealEstateID) REFERENCES EstatesBasicInfo(RealEstateID), CONSTRAINT FK_Rents_Employees FOREIGN KEY (DealMadeByEmployeeID) REFERENCES Employees(EmployeeID) ); […]

SQL to get the latest date time row for each type

I need help with a SQL query that is very much like SELECT within SELECT with TOP. However I need the latest date and the value for that date time-stamp. When using SELECT (SELECT TOP(1) NumericValue FROM Datum WHERE ChannelId = test.ChannelId ORDER BY [DateTime] DESC) AS NumericValue, (SELECT TOP(1) [DateTime] FROM Datum WHERE ChannelId […]

SQLCMD to T-SQL (out of memory and scripting error)

I have over 18GB of database .sql file, which have over 200 Millions records. My server have Xeon E5 processor, 16GB ECC RAM and SSDs. I have SQL Server 2014 Web Edition and Windows Server 2012. I know I would need sqlcmd to run huge database so my command is this sqlcmd -S SQL-Server2014 -i […]

Retrieve a query from a SSIS SQL task via C#

I am looking for a way to programmaticly pull the all SQL statements from hundreds of dtsx files. I am already pulling the connection string and type by creating a package instance as seen below. I’m hoping for a solution that will allow me to access a SQLTask object via the package Application app = […]

Overwriting same values has any downside?

There are 2 queries at below and actually both of them are doing exactly same thing. It marks all products as published. But first one seems overwriting even Published is 1 and seems doing more work. Is there any downside of first query compared to second one from performance, logs…etc perspective? Published column is bit […]

How can i convert time to minutes using Sql queries e.g “02:30:05' to 150.5 minutes

I am working on a billing system and its based on time-in and time-out. Rate is R5 per 30 minutes charged to users for loaning an item. I managed to get the Time difference and got time as “02:30:05” now how can i convert this time to minutes and have “02:30:00” = “150 min” so […]

SQL Server 2014: SELECT only those rows that match all rows in another table

I am a beginner in SQL Server. I am trying to solve this problem: Select all (distinct) “item_id” from “ItemTag” table whose corresponding “tag_id” values match at least “all” values in the “UserTagList” table. I tried a join below but instead of the result I got the query should return the item_id 5 since it […]

select combined with an update, race condition

I would like to do something like this begin trans declare @maxpriceprice int; Select @maxpriceprice MAX(price) from products where typeId = 5 and later on Insert into products (price) values(@maxpriceprice + RAND() * 10) commit tran It is rather strange but it is strange application. between the select and insert i cannot afford to havve […]

Order by in subquery behaving differently than native sql query?

So I am honestly a little puzzled by this! I have a query that returns a set of transactions that contain both repair costs and an odometer reading at the time of repair on the master level. To get an accurate Cost per mile reading I need to do a subquery to get both the […]

Filtering SQL QUERY to XML

I have the below SQL statement and I am trying to convert this to XML. select so.ordernumber, sod.productidname, so.statuscode, so.statuscodename, sod.quantity, sod.quantityshipped, ip.pcssu_quantityavailable, so.pcssu_stockavailable from FilteredSalesOrder so inner join FilteredSalesOrderDetail sod on sod.salesorderid = so.salesorderid and ip.pcssu_quantityavailable >= sod.quantity – sod.quantityshipped inner join filteredaccount a on a.accountnumber = so.pcssu_servicecentrecode inner join Filteredpcssu_inventoryproduct ip on ip.pcssu_product […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.