I'm trying to manipulate the data in my Service_2 column to round off the Estimated_Miles to the closest 5K miles. I tried the round function (Round(EstimatedMiles, -4)) but this does not exactly give me the results I want. Below is my result set:
Row_ID
Service_2
Estimated_Miles
1
40000
44012
2
50000
46124
3
120000
121512
4
30000
31857
5
10000
7547
6
80000
79500
And below is what I'd like to have
Row_ID
Service_2
Estimated_Miles
1
45000
44012
2
50000
46124
3
125000
121512
4
35000
31857
5
10000
7547
6
80000
79500
The range of data I'm working with is pretty broad meaning my Min & Max Estimated_Miles is between 0 - 1,000,000. In essence, I would like to say is if the estimated mileage is between 0-4999 then Service_2 = the closest 5K miles otherwise round to the closest 10K miles. Any help is truly appreciated. Thanks
Is there any way in SQL of selecting a date closest to another date.
i.e. I have a date say 4/4/2002, and in the database I have dates 4/2/2002 and earlier and 4/7/2002 and later.
I want to do a query and return the date 4/2/2002 as it is only 2 days away even though it is an earlier date.
So short of writing a loop and saying if the date is eq then good else, take date and add a day if not correct take initial date and subtract a day etc etc. This way is proving to be a little too processor heavy.
Iβm trying to write a query that will select closest value to Amount field in #TestValue without go over #SM.GMAdj value.Outcome of the query should be something like
Tables are: SELECT PercRate, Amount, Code INTO #TestValues FROM ( SELECT 6.00, 172715, 13 UNION ALL SELECT 6.50, 172716, 13 UNION ALL SELECT 7.00, 181351, 13 UNION ALL SELECT 7.50, 190419, 13 UNION ALL
Hello,I need help in writing a SQL statement in MS SQL Server 2000 to selectthe latest date (i.e., the date closest to or equal to the current date)for a given date.For example, in a table I have the following records:Date Exchange-Rate01/Sep/03 0.5505/Sep/03 0.59If the given date is 02/Sep/03, then the rate 0.55 should be return.If the given date is 03/Sep/03, then the rate 0.55 should be return.If the given date is 04/Sep/03, then the rate 0.59 should be return.Thanks in advanced,Benny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
ID: (primary, auto generated) Work order : (non unique foreign key) Status: Internal status Modified date: date time of status change to work order
This keeps track of our work order status changes. We'd like to trend our internal orders and monitor status durations (so to know how long each work order spend on a particular status). Now, the only thing is missing is an "end date/time", thus I must generate it in either a query or a SQL report form. The issue, is that i need the "modified date" from the next highest combination of work order + ID values (since they are the only reference). Is there any way to get this to happen? So essentially, if I sort by "work order" and then "id", i'd want the "modified date" value from line 2 to be on line 1, and so on. Now, if the "work order" number changes, or there isn't a higher work order+ID, we leave it blank and move on. Any ideas?? I'm really stuck.
I have a need to execute a query in T-SQL on a numeric field in a SQL table. However if there is no exact match I'd likea query that will return the row that is just below my value. As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned. Is this possible? Can anyone help me with such a query? Regards Clive
I apologise if this is a stupid question, but I would appreciated anyhelp on this subject.I want to create a view (VIEW_1 in example below) where I take numericvalues from a field in one table (DEPTH_FROM in TABLE_1) and find theclosest matching values from another field in another table (DEPTH_ATin TABLE_2) - the higher matching value and the lower matching value.TABLE_1BH_ID DEPTH_FROMB1 10.6B1 14.2B1 16.1B1 17.0TABLE_2BH_ID DEPTH_ATB1 9B1 10B1 11B1 12B1 13B1 14B1 15B1 16B1 17B1 18VIEW_1BH_ID DEPTH_FROM DEPTH_LOWER DEPTH_HIGHERB1 10.6 1011B1 14.2 1415B1 16.1 1617B1 17.0 1717Any assistance would be appreciated.Regards,Hennie
I have included the create table statements, sample data and a stored procedure.
I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.
I have two requests/needs/problems that I need to solve: (1) I want to create a NEW stored procedure that takes a WhoID and an AssocID. This new stored procedure would show the shortest or closest path between WhoID and AssocID.
For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be 6 - 8 8 - 2 2 - 3 3 - 1
(2) I might also need a proc that accepts two inputs (again, lets use 6,1) but would show all paths / branches of the tree where person 1 exists. Based on the stored procedure procCTE_Assoc 6, this solution would exclude the "fred and jeff" node but would include the other two nodes because person 1 is within the other two nodes.
Please provide your suggestions, comments, or solutions!
CREATE TABLE dbo.Who( WhoID bigint IDENTITY(1,1) NOT NULL, FName varchar(50) NOT NULL ) GO CREATE TABLE dbo.Assoc( WhoID bigint NOT NULL, AssocID bigint NOT NULL ) ON PRIMARY
SET IDENTITY_INSERT dbo.Who ON GO INSERT INTO Who (WhoID,FName) VALUES (1,'user1') INSERT INTO Who (WhoID,FName) VALUES (2,'user2') INSERT INTO Who (WhoID,FName) VALUES (3,'admin1') INSERT INTO Who (WhoID,FName) VALUES (4,'admin2') INSERT INTO Who (WhoID,FName) VALUES (5,'admin3') INSERT INTO Who (WhoID,FName) VALUES (6,'Keith') INSERT INTO Who (WhoID,FName) VALUES (7,'fred') INSERT INTO Who (WhoID,FName) VALUES (8,'Joe') INSERT INTO Who (WhoID,FName) VALUES (9,'Jack') INSERT INTO Who (WhoID,FName) VALUES (10,'Doug') INSERT INTO Who (WhoID,FName) VALUES (11,'Mark') INSERT INTO Who (WhoID,FName) VALUES (12,'Al') INSERT INTO Who (WhoID,FName) VALUES (13,'Jeff') INSERT INTO Who (WhoID,FName) VALUES (14,'Brandon') INSERT INTO Who (WhoID,FName) VALUES (15,'Ashley') INSERT INTO Who (WhoID,FName) VALUES (16,'Lisa') INSERT INTO Who (WhoID,FName) VALUES (17,'Julie') INSERT INTO Who (WhoID,FName) VALUES (18,'Brian') go SET IDENTITY_INSERT dbo.Who OFF GO INSERT INTO Assoc (WhoID,AssocID) VALUES (1,4) INSERT INTO Assoc (WhoID,AssocID) VALUES (2,1) INSERT INTO Assoc (WhoID,AssocID) VALUES (2,3) INSERT INTO Assoc (WhoID,AssocID) VALUES (6,7) INSERT INTO Assoc (WhoID,AssocID) VALUES (6,8) INSERT INTO Assoc (WhoID,AssocID) VALUES (7,13) INSERT INTO Assoc (WhoID,AssocID) VALUES (8,2) INSERT INTO Assoc (WhoID,AssocID) VALUES (14,8) INSERT INTO Assoc (WhoID,AssocID) VALUES (6,15) INSERT INTO Assoc (WhoID,AssocID) VALUES (15,18) INSERT INTO Assoc (WhoID,AssocID) VALUES (15,17) INSERT INTO Assoc (WhoID,AssocID) VALUES (17,5) INSERT INTO Assoc (WhoID,AssocID) VALUES (5,1)
go create proc dbo.procCTE_Assoc @WhoID bigint AS
WITH GroupsCTE (WhoID, AssocID, FName, FNameAssoc, Lvl, Sort, GroupWithIndent) AS ( -- Anchor Who definition SELECT M.WhoID, MA.AssocID, M.FName, AssocWho.FName, 0 AS Lvl, CONVERT(varchar(2000),AssocWho.FName) AS Sort, CONVERT(varchar(255), AssocWho.FName) FROM Who M INNER JOIN Assoc MA ON M.WhoID = MA.WhoID INNER JOIN Who AssocWho ON AssocWho.WhoID = MA.AssocID WHERE M.WhoID = @WhoID UNION ALL -- Recursive Who definition SELECT g1.WhoID, MA1.AssocID, g1.FName, g2.FName, Lvl + 1, CONVERT(varchar(2000),RTRIM(Sort) + '|' + g2.FName), CONVERT(varchar(255), REPLICATE ('| ' , Lvl+1) + g2.FName) FROM Who g1 INNER JOIN Assoc MA1 ON g1.WhoID = MA1.WhoID INNER JOIN Who g2 ON g2.WhoID = MA1.AssocID INNER JOIN GroupsCTE CTE ON MA1.WhoID = CTE.AssocID )
i have a column of dates. i make an index or key for this column so that i can perform binary search i hope. If the search is not succesful how do i get the query to return the two dates surrounding the date i was searching for.
I have two tables and I am trying to pull a value closest to another date. Β There could be instances where the Eval_date in TableA matches the date in TableB so how would that be handled?
Hi all, I am seeking your expertise to create SQL codes (SQL server 2005) that can help me to answer the problem below.
I have two tables (points and station), presented in form of SQL codes below. Id like to find the 6 closest panels for each of the station. As can be seen in the result table below, the 6 closest panel names are arranged from the first closest (P1) to the sixth closest (P6). Similar procedure also applies for the distance column arrangement. This distance column (D1 D6) is the distance of panels P1 P6 to the station. The distance between two points (with x-y coordinates) can be calculated using a simple Cartesian formula: Distance = ( (X1 X2)2 + (Y1 - Y2)2 ) 0.5 . As the sample, distance between station A and panel P19-04W is = ((737606.383 - 737599.964)2 + (9548850.844 - 9548856.856)2) 0.5 = 8.79. The expected result of the work is presented in the table below:
Table 1: create table 1 ( Panels varchar(20), X_Coord float, Y_Coord float ) go set nocount on
insert into 1 values('P19-03E','737640.722','9548882.875') insert into 1 values('P19-04E','737630.166','9548868.3') insert into 1 values('P19-05E','737619.611','9548853.726') insert into 1 values('P19-06E','737609.054','9548839.15') insert into 1 values('P19-07E','737598.495','9548824.571') insert into 1 values('P19-08E','737587.941','9548809.998') insert into 1 values('P19-09E','737577.386','9548795.425') insert into 1 values('P19-10E','737563.359','9548776.163') insert into 1 values('P19-11E','737552.795','9548761.578') insert into 1 values('P19-12E','737542.256','9548746.919') insert into 1 values('P19-13E','737531.701','9548732.345') insert into 1 values('P19-14E','737521.146','9548717.772') insert into 1 values('P19-03W','737610.519','9548871.43') insert into 1 values('P19-04W','737599.964','9548856.856') insert into 1 values('P19-05W','737589.404','9548842.275') insert into 1 values('P19-06W','737578.849','9548827.702') insert into 1 values('P19-07W','737568.294','9548813.128') insert into 1 values('P19-08W','737554.274','9548793.77') insert into 1 values('P19-09W','737543.718','9548779.195') insert into 1 values('P19-10W','737533.157','9548764.614') insert into 1 values('P19-11W','737522.603','9548750.041')
set nocount off go
Table 2: create table 2 ( Station varchar(20), X_Coord float, Y_Coord float ) go set nocount on
insert into 2 values('A','737606.383','9548850.844') insert into 2 values('B','737575.41','9548806.838') insert into 2 values('C','737544.437','9548762.832')
What I'm trying to select is the closest value from a list given by a parameter or select the matched value.
declare @compare as int set @compare = 8 declare @table table ( Number int ) insert into @table values(1), (2), (3), (4), (5), (10)
If the parameter value match one of the values from the table list, select that matched one.If the value does not exist in the table list, select the closest lower value from the table list, in this case, it would be value 5.
I'd like to get some ideas for the following: I am writing a quick mini-application that searches for records in a database, which is easy enough. However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after. (Obviously the results are ordered on the search term column) So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it. I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better. Thanks!
SELECT 1, 10 UNION ALL SELECT 2, 10 UNION ALL SELECT 3, 5
- a calculated table that told me the availability for each component of the BOM, sorted by date. (each row have a plus or minus of the quantity so it can by summarized)
INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date) SELECT 1, 10, '2015-03-01'
[Code] ....
My question is: how do I check when is the closest date to manufacturing? I have to check that the quantity of ALL the components of the BOM is enough to produce the product, but I can't get how to do it.
If I'm not wrong the example should give the result 2015-03-26.
I have the following code that retreives the current value of the item price. however it always rounds up. If I manually enter a return value like so:return (decimal)12.47 It returns the correct value, however if I set it with an expression like this:return (decimal)arParam[1].Value;It rounds the number up: How can I get it to not round up when insertign a value based ona expression? public decimal GetCreditPrice(string CustomerSecurityKey) { try { System.Data.SqlClient.SqlParameter prmCrnt; System.Data.SqlClient.SqlParameter[] arParam = new System.Data.SqlClient.SqlParameter[2]; prmCrnt = new System.Data.SqlClient.SqlParameter("@CustomerSecurityKey", SqlDbType.VarChar,25); prmCrnt.Value = CustomerSecurityKey; arParam[0] = prmCrnt; prmCrnt = new System.Data.SqlClient.SqlParameter("@Price", SqlDbType.Decimal); prmCrnt.Direction = ParameterDirection.Output; arParam[1] = prmCrnt; SqlHelper.ExecuteNonQuery(stConnection, CommandType.StoredProcedure, "GetCreditPrice", arParam); return (decimal)arParam[1].Value;
I have the following statement and I want to round the final value(gbkmut.bdr_hfl)two decimal places.
begin UPDATE gbkmut SET gbkmut.bdr_hfl = gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate') WHERE reknr = ' 1040' end
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
Looking for a way to round numbers to a specified number of significant digits. The ROUND function rounds to a specific decimal place but does not take into account the level of significance of the remaining numbers. (i.e. ROUND(7.12435,2)=7.12000) The type of function I need would round the number in the following manner: SigFigRound(7.12435,3)=7.12 or SigFigRound(7.12345,1)=7.
I have rounding problems when editing or inserting a new record in float type fields. e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @FCost. Then I round @FCost=Round(@FCost,2). When I try to inert this value to a new record again I'using Round(@FCost,2). However in a lot of records a lot of digits are stored. I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@FCost*100)/100 in order to have 2 digits, a lot of demical values are created. What is the best practise in order to solve this problem? Regards, Manolis
I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different: eg. ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding
SQL SProc: 1665.91 Rounds down ADP VBA: 1665.92 Rounds up
I need help on this query. I'm trying to have a number rounded, so I can truncate the decimal. The reason I want to do this is that it is for a planning function and I need it to round to a number that is divisible by an order minimum qty. Example: I show a need for 2611 items, but the item is only ordered in qtys of 100, so I'd need 2600 instead of 2611, because the vendor won't let me order out of qty. So, my query would take 2611 / minimum order qty (100) which would be 26.11 somehow take off the .11 then multiply back by 100, which would give me 2600.
use mas500test_app -- UPDATE timItem -- SET UserFld3 = 1 Select distinct I.ItemID, V.VendID, D.ShortDesc, B.Name as ItemBuyer, BV.Name as VendorBuyer, IC.ItemClassID, PPL.PurchProdLineID, isNUll(BI.QtyOnHand,0) AS QtyOnHand, IV.QtyOnPO, IV.QtyONSo, IV.QtyONBo, W.WhseID, ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) as Available, IV.MaxStockQty, IV.MinStockQty, IV.MaxStockQty - IV.MinStockQty AS SafetyStock, I.UserFld6 as Rank, I.UserFld3, -- Case -- WHEN I.UserFld3 = 0 -- THEN '1' -- ELSE I.UserFld3 -- END -- as PackQty,
CASE WHEN (IV.MaxStockQty - IV.MinStockQty) <> 0 THEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO) / (IV.MaxStockQty - IV.MinStockQty)) ELSE 0 END AS MonthsOnHand,
CASE WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < IV.MinStockQty THEN IV.MaxStockQty - ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) --* I.UserFld3
END as QtyNeed, I.StdBinQty,
CASE WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < I.StdBinQty THEN I.StdBinQty WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) > I.StdBinQty ******This is the number I need rounded *****THEN ROUND(IV.MaxStockQty - (IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO) / I.userfld3), -2))) END AS OrdQty
from timwhsepurchprodln WPL INNER JOIN TAPvENDOR v ON WPL.PrimaryVendKey = V.VendKey INNER JOIN timPurchProdLine PPL ON WPL.PurchProdLineKey = PPL.PurchProdLinekey inner join timInventory IV ON PPL.PurchProdLinekey = IV.PurchProdLinekey INNER Join timBuyer B ON IV.BuyerKey = B.BuyerKey INNER Join timBuyer BV ON V.BuyerKey = BV.BuyerKey INNER JOIN timItem I ON I.ItemKey = IV.ItemKey INNER JOIN timItemClass IC ON I.ItemClassKey = IC.ItemClassKey INNER JOIN timWarehouse W ON W.WhseKey = IV.WhseKey INNER JOIN timItemDescription D ON I.ItemKey = D.ItemKey INNER JOIN timItemUnitOfMeas IUOM ON I.ItemKey = IUOM.ItemKey INNER JOIN tciUnitMeasure UM ON IUOM.TargetUnitMeasKey = UM.UnitMeasKey LEFT JOIN (SELECT ItemKey, SUM(QtyOnHand) AS QtyOnHand FROM timWhseBinInvt GROUP BY ItemKey) BI ON BI.ItemKey = I.ItemKey where IV.WhseKey = 22 and I.Status = 1 and ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < IV.MinStockQty
I have a problem...Data transformation rounds or truncate decimals!I have an ODBC source in witch is a table with float numbers (ODBC Driverpublish it as float).If I build a query form access or from excel with Query Analizer, I see alldecimal places, but when I try to insert data using DTS, float numbers willbe converted to its integer values.A "select * from table", with table ODBC table, gives integer value whenrunned from DTS to transform data from ODBC to MS-SQL Server table, andgives float values from Access or other tools.Where I can fix my problem?Thanks.Giorgio
When ISELECT CAST(96.58 AS DECIMAL(10 , 0)), it returns 97.When ISELECT CAST(575629 / 54 AS DECIMAL(10 , 0)), why it returns 10659? Itshould return 10660, right?What am I missing?Thanks,Faye Larson
Perhaps someone can settle an arguement for me ?I have a set of data that I need to group together. SQL Script below.CREATE TABLE [dbo].[CommTransactions] ([ID] [id_type] NOT NULL ,[TransactionID] [id_type] NULL ,[ClientID] [id_type] NULL ,[AccountCode] [varchar] (10) NULL ,[Amount] [float] NULL ,[CreateDateTime] [datetime] NULLFor the records I want to group the following applies.The ID is unique and distinct.The TransactionId is the same.The ClientId is the same.The AccountCode is different.The Amount will be the same.The CreateDateTime field is different by a few milliseconds.I want to create a single line showing two account codes in differentfields. i.e. Staff and Manager (where their ID is the account code).These can be entered in any order in the table mentioned.The problem I have is I need to link two records together (that's theproblem in it's most simplistic terms). However, there may beadditional records with the same TransactionId, ClientId, AccountCodeand Amount, but happened at a slightly different time. It could bedone on the same day.Now, the arguement is that we can group using the CreateDateTimefield. I argue that we can't as it will show down to the millisecondand any rounding will not always allow for a match. If we added thematching records once per day, then I can extract the date and groupon it, but if more than one group is added per day, then this wouldcause the logic to fail.So, are there any reliable methods for grouping date/time fieldsreliably if there is a small difference (I suspect not)?Is there anything I have missed ?Any help or suggestions would be appreciated.ThanksRyan
