I have Two SQL Query Both Return select round(convert(float,'24367.723'),2) Result:24367.72 Second: select convert(varchar(20),round(convert(float,'24367.723'),2)) Result:24367.7 Why the Second Query Return exclude the last digit after converting to varchar Thanks in Advance ...
I have below table structure in MS SQL AirQuoteID Name SalesValue 7 M 49.50 7 N 23.10 7 +45 233.20 7 +100 233.20 7 +250 2333.10 I want a query which can return AirQuoteID M N +45 ...
I'm using CONTAINSTABLE to search two table columns. Once the search contains small words like 'the' 'for' 'a' the search returns no results even when they are actually present in the column. Quick example. Column being searched contains the text. 'System needs to be upgraded' Following SQL returns 0 rows SELECT * FROM Incident WH...
Below mentioned stored procedure is giving error while creating Msg 156, Level 15, State 1, Procedure crosstab, Line 23 Incorrect syntax near the keyword 'pivot'. Can anyone please tell me the mistake? Below is the script: CREATE PROCEDURE crosstab @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(...
Calling the UDF like so: SELECT product_name, SUM(quantity) AS SumQty, SUM(face_value) AS SumFaceValue, SUM(net_cost)AS SumNetCost, SUM(face_value - net_cost) AS SumScripRebate, organization_name FROM getSalesSummary(@GLSCOrgId, @BeginDate, @EndDate) getSalesSummary GROUP BY product_name, organi...
To fill one variable with a value from a query I can write following: SET @TargetID = (SELECT TOP 1 ID FROM @bigDataSet ORDER BY date DESC) To fill multiple variables from this query, eg. something like: SET (@TargetID, @TargetName) = ....(SELECT TOP 1 ID, [Name] FROM @bigDataSet ORDER BY date DESC) what can I write? ...
One question though lets say publisher database had 100 tables and I use Transactional Replication to move the data from those 100 tables to Subscriber Database that would be fine. But lets say I don't want the 100 tables but i want to create 3-4 Views which contain the key information I want from those 100 tables. How Would I achieve t...
I have a problem to get the max and the min value, I want the result to be XXL and XXS SELECT MAX(tblSizeXL.SizeXLName) AS maxSize, MIN(tblSizeXL.SizeXLName) AS minSize FROM Product JOIN tblSizeXL ON Product.SizeXLID = tblSizeXL.SizeXLID WHERE (Product.GroupID = @GroupID) GROUP BY tblSizeXL.SizeXLID ORDER BY ...
I have a database containing addresses, one address per Row. Contains every address in the UK, so ~28 million rows. One of the columns is 'Street', which I am using to perform searches. I have a non-unique, non-clustered index on this column. I'm having inconsistencies with search speeds however. select * from Postcodes where Street = ...
is there a canonical way to partition a table by referenced data to another table? for example timetable id datetime bigtable id timetable_id -- foreign key .. other data .. i want to partition bigtable by the datetime in timetable. thankx. ...
This question was inspired by one I asked almost a year ago - any-orms-that-work-with-ms-access-for-prototyping - which has recently become active again, but as an Access vs SQL Server debate. There seem to be a lot of Access haters out there, and the main rap seems to be that it doesn't scale well (though some people seem to have been ...
I have this query: select * from table where column like '%firstword[something]secondword[something]thirdword%' What do I replace [something] with to match an unknown number of spaces? Edited to add: % will not work as it matches any character, not just spaces. ...
If I have an empty table with an index and I do a bcp, does SQL Server (internally) drop/disable the index, load the data and then re-apply/enable/build the index? ...
I've been playing with sets in SQL Server 2000 and have the following table structure for one of my temp tables (#Periods): RestCTR HoursCTR Duration Rest ---------------------------------------- 1 337 2 0 2 337 46 1 3 337 2 ...
I have a procedure with a (slightly more complex) version of the below: CREATE PROC sp_Find_ID ( @Match1 varchar(10), @Match2 varchar(10) ) AS DECLARE @ID int SELECT @ID = ID FROM Table1 WHERE Match1 = @Match1 AND Coalesce(Match2,@Match2,'') = Coalesce(@Match2,Match2,'') SELECT @ID ID Essentially Match1 is a mandatory m...
I have self join table. This table is being used to join up to 4 level, i.e.; Region -> Country -> County -> Town How can I get Parent of Parent of Town. To do this up to two level this is the query SELECT t.ShortName AS Town, (SELECT c.ShortName FROM Locations c WHERE c.LocationId = t.ParentId) AS County FROM ...
Hey all, this is my query string here: SELECT SUM(Total) as Total, AdministratorCode, SUM(WOD.Quantity) as thePass FROM tblWO as WO, tblWOD as WOD WHERE WOD.OrderID = WO.ID AND WO.OrderDate BETWEEN '2010-01-01' AND '2010-08-31' AND Approved = '1' ORDER BY WO.AdministratorCode But i keep getting the error: The ...
I suppose everyone runs into this problem once in a while: you have two tables that have autonumber primary keys that need to be merged. There are many good reasons why autonumber primary keys are used in favour of say application-generated keys, but merging with other tables must be one of the biggest drawbacks. Some problems that aris...
I am trying to figure out how to compare the current day's data to the same data from a week ago, 2 weeks, etc. Let's say I have a table called "Order" with 2 columns: Order table ----------- OrderID int identity OrderDate datetime If today, is Monday, I would like to be able to compare the number of orders from today to the previous...
I assume that by intelligent design the source adapter is parameterized but just wanted confirmation from an SSIS veteran. As an example if I have a source query in the adapter like: select files ,transactions ,dateofsomething from reallygoodtablename where theparameter = ? Does this get prepared by sql and get a cached plan or do...