OK, this should be an easy one but my brain isn't quite working right now.
I have a table, we'll call Table1 like so:
ProductID Supplier Cost
12345 A 14.50
12345 B 13.49
12345 C 12.00
43222 A 15.00
43222 B 15.21
43222 C 13.99
12312 B 14.00
15421 A 21.99
15421 C 20.00
And I want to Get the name of the Supplier with the Lowest cost,
I know I can go like:
SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID
and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as ( SELECT ref, MAX(epcpond)[Unitcostprice], MAX(datalc) MaxDate FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722 Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?
@AppId AS Application_Id , MB.POLICY_ID AS Policy_Id , MH.BFN_CHG_DATEAS Date_Of_Disinvestment , AD.X_INVEST_AMT AS Original_Investment_Value , MB.TOTAL_AMOUNT AS Encashment_Value , WH.TOT_COMMISSION AS Top-Up_Commission
FROM: I select from lot of table, as specified from my select statement
My Aliases returns just one values, of which that's what I am expecting, so I need to make a comparison between Original_Investment_Value and (Encashment_Value + Top-Up_Commision), and return the lowest value
Is there a more elegant way to do the following?...declare @d1 datetime, @d2 datetimeset @d1 = '2005-01-01'set @d2 = '2005-02-01'selectcasewhen datediff(dd,@d2,@d1)is nullthen coalesce(@d1, @d2)when (datediff(dd,@d2,@d1)> 0)then @d2else @d1endCheers,..N
Hi, this seems to be an easy question but I can't find a quick answer to it. I have an asp page that communicates with a database and I want the following example to work...
Insert 5 records into the DB with primary keys 1-5. Remove record with key number 2.
Now, if I insert a new record I want it to take the smallest available key number (in this case number 2). This will save space in the database and keep the key numbers from increasing forever.
Does auto increment work like this? If not, how do I do it?
Our company is a proffesional deticated bullet proof server and general server provider in china. If you're looking for a reliable and long term cooperation bp server provider, it deservers you to read our company file to know more about us.
I'm using sql express 2005. I need help with buliding a view.
I'm trying to join two tables and then, if duplicate ID's exist, retrieve the row with the lowest value in a specific column.
For instance:
TableHousehold:
ID Name Status
001 The Smith€™s A
002 The Jone€™s A
003 The Adam€™s A
004 The Grant€™s A
TableResidents
Pk HouseholdID ResidentID Name Gender
1 001 1 Mary Smith F
2 001 2 Jason Smith M
3 001 3 Rachel Smith F
4 002 1 Mike Jones M
5 002 2 Sara Jones F
6 003 2 Erik Adams M
The resident ID is auto increment and assigned by the server. It is possible for resident 1 for each household to have been deleted. It is also possible that a household does not contain any residents. Therefore, I wish to join these tables and return this:
ID HouseHoldName Status ResidentName Gender
001 The Smith€™s A Mary Smith F
002 The Jone€™s A Mike Jones M
003 The Adam€™s A Erik Adams M
004 The Grant€™s A null null
There are other columns in both tables that I will want to join, but for simplicity, the above example illustrates my need.
I was able to accomplish this by creating two views. But I€™d like to try and do this in one.
I have a long stored procedure that performs some inserts and updates on the table that users are accessing through a user interface. We this stored procedure runs users experience slowness, is there any way I can get the stored procedure runs with a lowest priority so that user will be able to perform their tasks first.
I have a little problem that I just haven't been able to solve. I don't think it is very difficult but I can't seem to make it work. Here's the scenario:
I have a database with the following values: Model Make Price DA1100 GTN$88.00 DA1100 GTN $100.00 DA1000 GBN$110.00 DA668 GTN$100.00 DA880 GTN$200.00
In this case DA1100 is listed twice with 2 different prices. I only want to display the one with the lowest price. So the result I want is: Model Make Price DA1100 GTN$88.00 DA1000 GBN$110.00 DA668 GTN$100.00 DA880 GTN$200.00
Let's say I have a result set (records) containing numbers such as:01239162145100101102103104105106How might someone write a procedure to get the next lowest number inthis sequence? In this case it would be: 4. Some combination ofSelect, Min & When, I am sure, but it escapes me at the moment.Thoughts? Thanks...
I have a long stored procedure that performs some inserts and updates on the table that users are accessing through a user interface. We this stored procedure runs users experience slowness, is there any way I can get the stored procedure runs with a lowest priority so that user will be able to perform their tasks first.
I am attempting to only query the lowest year, and month from my entire production database. I am using the following query and when I do it individually it will bring back the lowest year but also with additional years. How can I remove those other years so I can only query the lowest year and month from the entire database?
Code: select distinct RRCID, MIN(YEAR)as YEAR, MONTH from Gas_Prodv1 where RRCID = 1221376 group by RRCID, MONTH order by YEAR, MONTH
Results: 122137620071 <----- Only want this record 122137620072 122137620073 122137620075 122137620076 122137620077 122137620078 122137620079 1221376200710 1221376200711 1221376200712
ID (PK), userID (FK), from [Datetime], to[datetime], type [int] , value [nvarchar]
Conditions I want to select the column "to" which is a datetime-column for all users within the table.
One user can have multiple registered rows in "mytable", so I want to select the row that contains the newest "from" date.The newest "from"-column for a specific user is where I am struggling... Because if the "to"-column of any of the registered rows by the current user is equal to the newest "from -1 day" column for the user, I want to select the second newest from-date.
Code: //Handwritten DECLARE @from DATETIME //Current Selected datetime [the newest of the current user doing the query] DECLARE @id INT //userID DECLARE @tbl TABLE (from datetime, userid int)
[code]....
But for user ID 201 [all users with Type = 1], I need to run through some more code, to figure out which date we want... Which date do I want? The "from"-date that is the newest, but if "to" is just 1 day before "from", I want to select the "from" at that row instead... Example with numbers [integers] instead of Date:
Code: From - To 5 8 10 15 16 20
I want to select "10" in the from column as the maximum "from"-integer, and not 16. Because in the "To" column we have a match in the "from" column on the condition "To + 1" [15+ 1 == 16], so then I want the "from" value where "To" equals 15, which is 10. The top row I do not want, because the difference between From and To [10 - 8 is larger than 1]...
This means that the Image shown... I am first selecting the "1", then I do some checks, if the "to" for the user is 1 day earlier than the MAX(from) I grab a new "from" value...
Code: SELECT MIN (t1.from) FROM table as t1 WHERE t1.from IN ( SELECT t2.to+ 1 FROM table as t2 WHERE t2.userID = @id )
I have a table and am usuing ASP to query the database, the connection is to a MS Access table at the moment but am working to convert to SQL Server.
Question:
I need to select the last 20 records, by a date field, then from those 20 records select the 10 lowest scores.
Example is a member logs on an that member has say 80 total records in the table. Then I need to select the last 20 records entered by the date field then select the lowest 10 scores out of those 20.
I am new to more compex SQL Statements any help would be mostly appreciated!
table = HC_ID date field = date member_id = member score = ScoreHC
Select table1.id, table1.original_date, (select €¦€¦..from €¦..) as Date1, (select €¦€¦..from€¦..) as Date2 from table1
Date 1 and Date 2 are both complex subqueries that retrieve a date from other tables. I would like another column displaying the oldest date between date1 and date2. Can anyone help?
I wrote some code to insert some data into a table, if the record already exists overwrite, etc. I've never done something like this so I am going to bang my head against that for a while. But when I did an execution plan I got
Index Seek Cost : 105 %
How do I get more than 100% and what does that mean?
Trying to get the lowest unused value of CrewID from the below table in a efficient manner. I cant change the table structure as its from a 3rd party and it would break our support agreement.
CREATE TABLE [dbo].[Crews]( [CrewID] [nvarchar](10) NOT NULL, [CrewName] [nvarchar](200) NOT NULL, [CompanyID] [nvarchar](10) NOT NULL, [StartTime] [nvarchar](50) NULL,
CREATE TABLE #tree ( Childid varchar(20), Parentid varchar(20) ) INSERT INTO #tree (Childid,ParentId) SELECT '123' , null UNION ALL SELECT '456' , '123' UNION ALL SELECT '789' , '456' UNION ALL SELECT '870' , '456' UNION ALL SELECT '985' , '870';
I have just been running a query which I was planning on improving by removing a redundant GROUP BY (there are about 20 columns, and one of the columns returned is atomic, so will mean that the "group by" will never manage to group any of the data) but when I modified the query to remove the grouping, this actually seems to slow the query, and I can't see why this would be the case.
Both queries return the same number of rows (69000), as I expected, and looking at the query plan, then they look nearly identical, other than at the start, there is a "stream aggregate" and "sort" being performed. The estimated data size is 64MB for the non-grouped query (runs in 6 min 41 secs), vs 53MB for the aggregated query (runs in 5 min 31 secs), and the estimated row size is smaller when aggregated.
Can rationalise this? In my mind, the data that is being pulled is identical, plus there is extra computation for doing an unnecessary aggregation, so the aggregated query should be unquestionably slower, but the database engine has other ideas; it seems to be able to work more quickly when it needs to do unnecessary work :) Perhaps something to do with an inefficient query plan for the non-aggregated query? I would have thought looking at the actual execution plan might have made this apparent, but both plans look very similar.
Edit: More information, the "group by" query had two aggregations on it, a count of one of the columns, and an average of another one. I changed this so that it was just "1" instead of the count, and for the average, I changed it to be the expression within the average aggregate, since the aggregation effectively does not do anything.
I'm still a bit new to T-SQL and am wondering how to get various LOWEST values from a table, rather than TOP values. There does not appear to be a keyword opposite of TOP in T-SQL.
My real problem is this: I know how to get the 2nd highest value, third highest value, etc...but how does one get the 2nd lowest value, third lowest value, etc?
Id   Level   ParentId 1   0      1 2   1      1 3   2      2 4   0      4 5   1      4 6   0      6 7   1      6 8   2      7 9   3      8      10  4      9 11  0        11
As you can see even the parent element has parentId(in this case id = parentid)
How can I select the lowest level data in the hierarchy and get this result:
Id   Level   ParentId 3   2      2 5   1      4 10  4      9 11  0        11
I am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.
Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3 From Valid_Timesheets Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and ((Bill_Rate_1 = £10.26 or Bill_Rate_1 = £9.67 or Bill_Rate_1 = £8.27 or
I have 2 different queries which produce same result. I want to know which querry is better and why? The query is used to display the employee details who is handling the maximum number of project.
Queries are the following
Query A
Code Snippet
SELECT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName, COUNT(LUP_EmpProject.Empid) AS Number_Of_Projects FROM LUP_EmpProject INNER JOIN EmployeeDetails ON LUP_EmpProject.Empid=EmployeeDetails.Empid GROUP BY EmployeeDetails.FirstName+' '+EmployeeDetails.LastName, LUP_EmpProject.Empid HAVING COUNT(LUP_EmpProject.Empid)>0 AND COUNT(LUP_EmpProject.Empid)=(SELECT MAX(Number_Of_Projects) FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects FROM LUP_EmpProject GROUP BY LUP_EmpProject.Empid)AS sub)
Query B
Code Snippet
SELECT LUP_EmpProject.EmpID, EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName AS EmpName, COUNT(*) AS NumberOfProjects FROM LUP_EmpProject INNER JOIN EmployeeDetails ON LUP_EmpProject.EmpID = EmployeeDetails.EmpID GROUP BY LUP_EmpProject.EmpID, EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName HAVING COUNT(*)=(SELECT MAX(Number_Of_Projects) FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects FROM LUP_EmpProject GROUP BY LUP_EmpProject.Empid)AS sub
I've converted most SQL Server databases to unicode since the AS 400 stores data in unicode (UTF-16). So far the only difference I see is that running queries to text in Unicode takes a lot longer, and UTF-16 files are 4x as large as Cp1252 files.
Any way to get the file sizes / query times of old with Unicode?
