Closest Match SQL Query
Oct 5, 2007
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
View 6 Replies
ADVERTISEMENT
Apr 9, 2008
Okay, I have a table that is arranged like so:
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.
Thanks in advance,
Josh
View 7 Replies
View Related
Mar 27, 2001
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.
View 2 Replies
View Related
May 26, 2005
Hi,
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!
View 2 Replies
View Related
May 12, 2008
I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....
This is what I have so far -
SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @guid then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4
View 10 Replies
View Related
Sep 10, 2007
Hi all,
I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:
SELECT *
FROM TblUser u
WHERE EXISTS
(
SELECT *
FROM TblScore s
WHERE s.FKIDUser = PKIDUser
)
With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15
I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.
So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.
Someboy would have an idea at my problem ?
Thanks in advance
Jerome
View 7 Replies
View Related
Apr 6, 2007
Hi can anybody give me a simple example to check exact match word using sql sever
Ex: my word is like this : welcome to sql sever2005
Now i want to find server how it is possible?
I tried like this
select * from test where content like %server% . but is gives me possible result. But i dont want like this if i have an exact match with that key word then only i want possitive result.
Actually there is no word with server, i'm having only server2005 . So how can I get it?
Thank you
View 3 Replies
View Related
Mar 3, 2014
I work for a school district and new requirement we were just given for scoring some student scores.
Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.
What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.
View 9 Replies
View Related
Nov 3, 2005
Can someone please answer a problem that I've run into. I know that it's probably something stupid. I keep getting this error:Server: Msg 107, Level 16, State 3, Line 1The column prefix 'vFirstTimeEntered' does not match with a table name or alias name used in the query.Here is my query:-----------------------------------------------------------------Update TimeSheetSectionSet TimesheetSection.SECSTARTDT = vFirstTimeEntered.schlstuidWhere timesheetsection.schlstuid = vFirstTimeEntered.schlstuid AND timesheetsection.sectionid = vFirstTimeEntered.sectionid AND Timesheetsection.secstartdt < '2005-08-01'------------------------------------------------------------------vFirstTimeEntered is a view that I created.Do I need a sub query? I know that if this was a select query I'd need to put vFirstTimeEntered in the FROM part but I don't know where it should go here.Thanks for any assistance.Scott
View 1 Replies
View Related
Apr 12, 2007
Need some quick help here.. The data I got from the text file use the "04/11/2007" date format and the StockDate in MS-SQL use the datetime datatype.
My understanding is that the "04/11/2007" will default to the 04/11/2007 12:00 am" format in MS-SQL.
So, when I use the sql query, how do search only for the date part of the data in MS-SQL and match it to the data from text file? I tried this SQL Query below.
--snip--
SELECT RawID FROM tblPurchaseRaw WHERE VIN = '" & sVin.ToString.Trim & "' AND StockDate = '" & dStockDate.ToString.Trim & "'
--snip--
That way, if I get a row then I know the data is there. If I don't get a row then I know the data is not there.
Bold: I get it now. It is all automatic as MS-SQL does it automatically...
View 5 Replies
View Related
Apr 22, 2014
I get the error "The column prefix 'contacts' does not match with a table name or alias used in the query".I am trying to obtain all fields from the communications table whether it is used or NULL.
SELECT organisations.organisation_number, organisations.contact_number, organisations.name, organisations.address_number,
organisations.std_code, organisations.telephone, organisations.status, contacts.title, contacts.initials, contacts.forenames,
contacts.surname, contacts.contact_number, contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes
[code]....
View 9 Replies
View Related
Feb 2, 2007
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE (
OuterID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL,
InnerID int NULL
)
INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2
DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)
INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3
INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5
INSERT @Inner VALUES (4, 2, 1) -- InnerID 6
-- UPDATE Outer Table with best match from Inner table
UPDATE @Outer SET
InnerID = IJ.InnerID
FROM @Outer OJ
INNER JOIN
(
SELECT TOP 1 I.*
FROM @Inner I
WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID
AND IsNull(I.CompID, OJ.CompID) = OJ.CompID
AND IsNull(I.FormID, OJ.FormID) = OJ.FormID
ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC
) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID)
AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID)
AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer
The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5.
Can anyone help me? Thanks in advance.
View 6 Replies
View Related
Jun 7, 2014
I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.
How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)
First name (exact match)
Last name (exact match)
Address line 1 (exact match)
Postal code/zip (exact match)
First Initial (exact match)
Last name (exact match)
DOB exact match
Postal code/zip (exact match)
View 1 Replies
View Related
Mar 15, 2002
I want to do a calc using the closest birthday...
declare @bday datetime ,@past_bday int ,@next_bday int ,@age int
set @bday = '12/25/1973'
--
-- some code goes here to assign values to past & next bday
--
if @past_bday < @next_bday
set @age = @age
else if @past_bday > @next_bday
set @age = @age + 1
TIA
JeffP...
View 1 Replies
View Related
Jul 13, 2002
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.
Thanks
View 2 Replies
View Related
Jan 8, 2015
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
PercRate Amount Code GMAdjPayment
-------------- ----------- ---------------------------------
6.00 ----172715 -----13-------171314.36------900
7.00 ----200200 -----18-------200299.00------500
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
[code]...
View 2 Replies
View Related
Jul 20, 2005
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!
View 8 Replies
View Related
Nov 5, 2007
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
View 11 Replies
View Related
Feb 20, 2006
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
View 2 Replies
View Related
Aug 9, 2007
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
)
SELECT *
FROM GroupsCTE
ORDER BY Sort
RETURN (0)
GO
exec procCTE_Assoc 6
View 5 Replies
View Related
Apr 21, 2015
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?
TableA
PA_IDEval_Date
12015-01-08 00:00:00.000
22015-01-25 00:00:00.000
TableB
IDPA_IDDate Stat
112015-01-15 00:00:00.000 14
222015-01-28 00:00:00.000 8
312015-01-16 00:00:00.000 2
422015-01-029 00:00:00.000 3
512015-01-09 00:00:00.000 11
612015-03-14 00:00:00.000 14
Expected Results
PA_IDDate Stat
12015-01-09 00:00:00.000 11
22015-01-28 00:00:00.000 8
View 8 Replies
View Related
Sep 4, 2007
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. I€™d 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:
Panel
distance
Station
P1
P2
P3
P4
P5
P6
D1
D2
D3
D4
D5
D6
A
P19-04W
P19-06E
P19-05E
P19-05W
P19-03W
P19-07E
8.79
12.00
13.54
19.02
21.00
27.43
B
P19-07W
P19-09E
P19-08E
P19-06W
P19-08W
P19-07E
9.50
11.58
12.92
21.15
24.85
29.11
C
P19-11E
P19-10W
P19-12E
P19-09W
P19-10E
P19-11W
8.45
11.42
16.06
16.38
23.15
25.30
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')
set nocount off
go
Thanks alot in advance!
View 9 Replies
View Related
Mar 10, 2014
I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row.
View 2 Replies
View Related
Nov 12, 2015
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.
View 3 Replies
View Related
Jul 23, 2007
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!
View 2 Replies
View Related
May 5, 2015
I'm trying to write a Stored Procedure that have to calculate the closest date for manufacturing.
What I have:
- The BOM (bill of materials) with the needed quantity for production
DECLARE @BOM TABLE
(
ItemIDINT
,neededQuantityfloat
)
INSERT INTO @BOM (ItemID, neededQuantity)
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)
DECLARE @WhareHouseMovement TABLE
(
ItemIDINT
,Quantityfloat
,DateDATETIME
)
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.
View 9 Replies
View Related
Dec 25, 2006
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
View 2 Replies
View Related
Aug 25, 2015
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
View 2 Replies
View Related
Sep 28, 2015
Basically, the sample raw data is a result from my last cte process which consolidate all the records. I want to find the closest date in PO ended using Receipt_date column but my problem, for this ESN R9000000000019761824, i wanted to get the latest or the last transaction date.
please see below DDL and sample data.
--this is the result from my last cte process
Create table #sample
(ESN nvarchar(35),ESN2 nvarchar(35), Receipt_Date datetime,PO_ENDED datetime)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2014-01-09 04:13:29.000')
[Code] .....
Expected result:
ESN-------------------ESN2-------------Receipt_Date-------------POENDED---------------
--------------------------------------------------------------------------------------
990002036924452-------990002036924452--2015-01-07 17:39:44.660--2015-04-23 20:26:55.000
990002038378525-------990002038378525--2015-01-07 05:29:56.923--2015-05-29 14:50:52.000
R9000000000019761824--354403060637509--2015-01-03 19:23:54.980--2015-03-20 16:43:07.000
[Code] ....
View 8 Replies
View Related
Feb 8, 2015
I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.
id
1
2
3
4
5
6
9
12
18
..
..
so output should be like
2 5
3 4
17 10
.......
View 4 Replies
View Related
Mar 25, 2008
Hello,
I am writing a SP, inside the SP i have a select statement.
I also have another SP, when i run it, it sends a list back with all the users that are currently online.
Now in my first SP, i want to construct a select statement that returns a users data, but only for the users that are online.
This is the first SP (GetAllOnlineUsers), that produces a list with users online (just abit rewritten from the membership function total users online):
SELECT u.Username FROM dbo.aspnet_Users u(NOLOCK), dbo.aspnet_Applications a(NOLOCK), dbo.aspnet_Membership m(NOLOCK) WHERE u.ApplicationId = a.ApplicationId AND LastActivityDate > @DateActive AND a.LoweredApplicationName = LOWER(@ApplicationName) AND u.UserId = m.UserId
This is my second SP, in witch i want to get the online users data:
SELECT m.username, d.data1, d.data2 FROM m.userstable INNER JOIN data ON (data.username = m.username) WHERE (m.username IN (EXEC GetAllOnlineUsers))
Is it possible to exec a SP into a select statement?
View 3 Replies
View Related
Sep 7, 2004
It seems my sysindexes table is inaccurate on a nonclustered index. In my case the rowcount (rows and rowcnt) do not match the actual rowcount of the table. The command UPDATE STATISTICS doesn't change the rows or rowcount, adding 'FULSLCAN' won't budge rowcount either.
After I did a dbcc reindex, the number of rows matched, however, upon adding rows in the table both rows and rowcount are out of sync again.
It's a fairly straightforward table, no triggers, no computed fields, only integer, datetime, varchar and bigint columns. There's a clustered index on a bigint column and a nonclustered index on a integer column.
dbcc show_statistics show that the nonclustered index is updated and it's rows and rows sampled match the number of rows in the table (not in the sysindexes-table).
I'd like to know if I'm chasing ghosts here or if there's something very wrong here. What could be causing the counts being inaccurate? Anyone who could shed some light?
View 2 Replies
View Related
Apr 25, 2008
I have this sql statement:
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are:
PersonSkills:
id, ProductID, Skills
Product:
id, ProductName
Ive been reading and playing around with this without success.
View 6 Replies
View Related
