T-SQL (SS2K8) :: Nearest Neighbor Within Given Shape Query

Nov 24, 2014

I have a table with 257 mil records with latitude and longitude data.

My goal is to find the closest intersecting values from a locations table (88 rows) and update any of the 257 mil records that are applicable with the location_Name and Location_Group_Name.

The query I have works but doesn't perform well on such a big data set.

CREATE TABLE #Positions -- Base table 257 mil rows. Actual table has 20 columns
(
IDBigInt PRIMARY KEY,
LatitudeDec(10,6),
LongitudeDec(10,6),

[Code] ....

Attached you will find the tables, test data, a function to measure distance and some queries that work but are too slow for this much data.

View 9 Replies

T-SQL (SS2K8) :: Round Value To The Nearest

Sep 16, 2014

I have a column called as NDM$ What I want do it round it the nearest value example I am giving below

34.100->34%

39.8->40

35.4->35 some thing like that.

View 6 Replies View Related

T-SQL (SS2K8) :: How To Round A Date To The Nearest Second

Sep 8, 2014

how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.

Also, the solution should not be too cumbersome because it will be used in a high volume environment.

View 5 Replies View Related

T-SQL (SS2K8) :: Rounding Decimal To Nearest Integer Value?

Jun 28, 2015

how to round the nearest value after round of decimal and return integer.

declare @data decimal(18,2)
set @data = 5.55

-- output

select '5'
set @data = 5.58
-- output
select '6'

View 3 Replies View Related

Can I Use Shape Query In .net

Jul 9, 2007

Hi all,I am trying to show the data as the shape query, where in the left part of the table is unique and the other right fields in the grid are many in number. That is a one to many relation ship. Can I use shape query using ADO.net or Oledb please tell me how? Or else if there is any other alternative please let me know.Thanks and regards.

View 3 Replies View Related

Advanced Sql-Shape Query - Help

Jan 12, 2006

Hi,This is my basic sql shape query:------------------------------------------------------------SHAPE {select * from tbl1}APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TOfield)------------------------------------------------------------With this query i get a RecordSet (RS1), who handle all the recordsfrom table tbl1, and a secondary RecordSet (RS2) who handle all therecords from table tbl2, who applies to the criteria that field1=1.It is possible that RS2 will be empty (zero records) since there is norecord in tbl2 who applies to that criteria.My wish is to design a query, that will collect only the records fromtbl1, that will have records from tbl2 who applies to the criteria -that RS2 won't be empty !I want to influence on the main part of the query (RS1), through thecriteria that is being used in the secondery query (RS2).I hope that my question is clear enough. thanks !

View 3 Replies View Related

DMX Shape Query Error

Jun 5, 2007

Hi I created a DMX query to retrieve predictions based on previous customer purchases and wanted to filter out my input data by only purchases made in the current year. I keep receiving this error:

Code Snippet

===================================

Internal error: An unexpected error occurred (file 'dmxinit.cpp', line 1343, function 'DMXNodeInput::InitFromASTOpenRowset'). (Microsoft SQL Server 2005 Analysis Services)

------------------------------
Program Location:

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()
at Microsoft.AnalysisServices.Controls.QueryResultGridStorage.ThreadProc()

And, here's my query:

Code Snippet

SELECT FLATTENED

(SELECT *

FROM PredictAssociation([PredictTable],

10,

INCLUDE_NODE_ID,

INCLUDE_STATISTICS

)

WHERE $NODEID <> ''

)

FROM

[Mining Model]

NATURAL PREDICTION JOIN

SHAPE {

OPENQUERY( [datasrc],

'SELECT ''1234'' AS [Customer_D_SID]'

)

} APPEND ({

SHAPE {

OPENQUERY( [datasrc],

'SELECT [Product_D_SID],[Customer_D_SID], [Transaction_Date]

FROM [Base_Sales_F]

WHERE [Customer_D_SID] = ''1234'' '

)

} APPEND ({

OPENQUERY( [datasrc],

'SELECT [Calendar_D_SID],[CALENDAR_YR_NBR]

FROM [dbo].[Calendar_D]

WHERE [CALENDAR_YR_NBR] >= ''2007'' '

)

} RELATE [Calendar_D_SID] TO [Transaction_Date]) AS B

} RELATE B.[Customer_D_SID] TO [Customer_D_SID]) AS [PredictTable]

AS T

I figured the only way to associate the calendar table with the sales table was to use a nested shape statement... is this wrong? Thanks for any help!

View 3 Replies View Related

Query - Nearest Birthdays To Mine

Dec 7, 2007

I have a date (my birthday). I would like to find the closest birthdays to mine, both before and after my birthday. I would like to list the people in my database who are the closest age to me, but in that order. So sorting my table by age and taking a row below and above my birthday is not going to work. This is because the three people below me may all have their birthday the next day, while those above me may have theirs years before mine.

Birthdays sorted by date:

05/10/1979 jim
12/01/1980 bob
10/04/1983 mike
10/05/1983 larry
11/21/1983 dan
12/07/1984 josh
05/07/1999 dylan

The order I wish to achieve is:

10/05/1983 larry
11/21/1983 dan
12/07/1984 josh
12/01/1980 bob
05/10/1979 jim
05/07/1999 dylan

Thanks in advance.
Mike

View 4 Replies View Related

T-SQL (SS2K8) :: Date Comparison In Two Table By Returning Nearest Date Of Table A In Table B

Jun 9, 2014

I am having a problem in creating query for this exciting scenario.

Table A

ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000

Table B

IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000

I would like to return the nearest date of Table B in my table like for

ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000

View 3 Replies View Related

Shape And Joins

Sep 3, 2003

Hi,
What is the difference b/w Shape command and the Joins of tables..

Even with Shape we can relate two entity by specifying the "Relate" command

plz can u tell how they functionally differ..

thanks in aadv..
Venu

View 3 Replies View Related

Shape Command

Jan 17, 2004

i use shape command in vb6 success but when transfer to sql query analyzer the error come out "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation"

can you help me to solve this problem

View 2 Replies View Related

Shape Table (s), How Do I Set This Up?

Mar 15, 2004

View 10 Replies View Related

Shape Function

May 7, 2007

Help please!

I got a problem in migrating ms access to sql server. In my vb code im using shape function and it doest work with sql server. Does sql server has shape function or are they have the same syntax? Any idea for these problem..??? Many thanks and God Blessed...

Thanks,

Jacsme

View 1 Replies View Related

Derived Shape - Replacing A Column With Itself.

Apr 27, 2006

Hi there,

I have a derived column shape in which I replace a column with an expression.

The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)

Like this.

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA

But whenever, the condition is NOT met, ColumnA is set to NULL!!!!

Does this mean that the column value is deleted before the Expression is applied?

If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset??

Cheers.

View 13 Replies View Related

Odd Syntax Error With Shape Queries SQL2000/Win2003

Jul 20, 2005

Hi,I am running SQL Server 2000 SP3 on Windows Server 2003 and since recentlyhave a strange problem executing shape queries from COM+ components usingADO.Until 4 days ago, they worked, then from one moment to the next (I must havechanged something, but I have no clue what other than restoring a 1.2 GBdatabase) they started failing with this error:Microsoft OLE DB Provider for SQL Server error '80040e14'Syntax error or access violationI have no problem executing non-shape queries, it is just the shape queriesthat fail.Any clues what may have gone wrong? Or how I can fix it?Cheers,Rsa Myh

View 1 Replies View Related

Output Columns In Script Shape -- Object Reference NOT Set

May 12, 2006

Hi there,

I'm getting an error in ascript transform shape.

When I try to write a value to an output column I'm getting an error saying the object reference cannot be set.

So my code looks like this - Row.OutputColumn = True

Any ideas??

View 1 Replies View Related

InfoPath 2003, Shape, Order By, Two Tables, Can't Get Sort To Work

Nov 9, 2006

InfoPath 2003

I have an Infopath form to edit the values in the NotifyMembers table. I want to list the email addresses alphabetically when the query returns data to the form. Unfortunately, I use EmailAddressID as an int in the table, so I need to join? the NotifyMember table with the EMailAddress table, which has a column EMail which is the address. Both have EmailAddressID as common fields.

I can't get the Shape command (edit SQL in the Datasource) to be happy with any syntax that I have tried.

For (non-working) example:

select "GroupCode","EmailAddressID","DelBit" from "dbo"."NotifyMembers" as "NotifyMembers" Join "dbo"."NotifyMembers"."EmailAddressID" On "dbo"."EmailAddress"."EmailAddressID" = "dbo"."NotifyMembers"."EmailAddressID" order by "dbo"."EmailAddress"."Email"

Any help appreciated..

View 1 Replies View Related

Nearest Distance

Jan 21, 2004

Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated

View 12 Replies View Related

List By Nearest Date?

Oct 7, 2006

Hi, I'm making a birthdays database where I want to list everyone in it ordered by the firstcoming birthdays according to the current date.

I have all the birthday records stored in a table called bursdager and the person name is stored in the navn column and the persons birthday date is stored in the dato column.

I'm having some problems, currently I have this statement:

Code:

DECLARE @tbl TABLE (navn VARCHAR(60), dato DATETIME)

INSERT INTO @tbl
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) >= DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)

INSERT INTO @tbl -- those are the one who allready have had birthday this year
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) < DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)

SELECT * FROM @tbl

It works *allmost* as it should-- except, it still lists the last persons who had birthday first, even the days after their birthday if the month is still the same.

I thought about adding an additional check:
Code:

AND DATEPART(day, dato) >= DATEPART(day, getdate())

in the WHERE clause of the SELECT statement but that won't be correct either because it then just lists everyone based on whether the day number the person was born is higher or less than the day number of the current date.

Anyone have any suggestions? Is there an easier way to do it?

Dag

View 7 Replies View Related

Round To Nearest WHOLE Number

Nov 16, 2006

T-SQL:How to round to the nearest WHOLE number ?sofrom -- to-------------170 --17096.58 --97thanks

View 1 Replies View Related

Rounding To The Nearest Thousand

May 11, 2007

Hi

Which parameter value for the Round function do I need to pass to get it to round to the nearest thousand ?

Thanks,
Neil

View 7 Replies View Related

Rounding Seconds Up To The Nearest 15 Minutes

Dec 6, 2007

I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.

View 3 Replies View Related

Joining Records With Nearest Datetimes

Mar 19, 2013

How would I match datetimes in records structured as follows:

Code:
Record1 AccountNo StartDateTime EndDateTime
1 1234 4/30/2012 8:00 AM NULL

2 1234 NULL 5/15/2012 8:00 AM

Desired Result:

Code:
AccountNo StartDateTime EndDateTime
1234 4/30/2012 8:00 AM 5/15/2012 8:00 AM

Of course there are multiple accounts, about 2100 in this case but they very by time periods, and multiple start and stop dates for an account. I need to get the start times and match them w/ the nearest end times but AFTER the value of the start time. Nearest end times must be forced to correspond to the nearest start time but there are some start times w/o end times and end times w/o start times due to user data entry errors. I need a solution that handles this. It is ok w/ the customer to make the assumption of nearest times supposedly going together so they can show the users the errors. I am on SQL Server 2008 R2.

View 4 Replies View Related

Optimal Search For The Nearest Date

Nov 23, 2005

I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated

View 1 Replies View Related

Joining Records By Nearest Datetime

May 19, 2008

Hi,

I have such a scenario:
- two tables with record containing car vehicle number, datetime of message and other data like weight ect.
- first table contains only two messages for one car per one day
- second has many messages for one car for one day

I would like to get a list of messages from first table but joined with the nearest (previous) record for the same car from second table.

Thanks,
Przemo

View 13 Replies View Related

Retrieving Nearest Number From Database

Jan 7, 2008

OK, this is the scenario. I have a database with many columns ( each a mean value and a standard deviation, and with it a set of coordinates that i want to retrieve ).

Then i have a value that i want to query with the database, by comparing it with the mean and its standard deviation, and it should return a few sets (lets say 2) of coordinates whereby the the value of the mean is closet to the one in the database, in order of nearest value. How should i do it, since i am not using the exact value of the mean in the database?

I know its a bit confusing the way i wrote, but anyone understand wat i am trying to say and can help, i am very grateful. I had googled around for answers but cannot find. Thanks.

View 4 Replies View Related

Find Nearest Date Record Without Cross Apply

Oct 13, 2012

Table :

ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal

Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.

SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date

The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.

View 1 Replies View Related

SQL Server 2008 :: Rounding To Nearest Quarter Hour?

Aug 12, 2015

I am getting the time difference between two dates using

DATEDIFF(second,Information.[Start Time],Information.[End Time]) / 60.00 / 60.00 AS hours,

My output looks like
1.33
0.17
1.50
etc

I'd like to round to the nearest quarter hour

1.50
0.25
.150
etc

View 4 Replies View Related

Changing Seconds To Hours And Minutes And Rounding To The Nearest 15 Minute.

Dec 6, 2007

I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.

SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM dbo.SLPTRANS INNER JOIN
dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING (dbo.SLPTRANS.ClientID = 405)

View 4 Replies View Related

Transact SQL :: Only Store Datetime Values Down To Nearest Minute Automatically Without Using Trigger

Sep 25, 2015

Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?

Here is a very basic example of what I am trying to do:

--example: '09-22-2007 15:07:18.850' this is the value inserted into the table by the code
select getdate()

--example: '2007-09-22 15:07:00.000' this is the value I want to store in the table
select dateadd(mi, datediff(mi, 0, getdate()), 0)

View 24 Replies View Related

T-SQL (SS2K8) :: Query For Treeview

Sep 11, 2012

I have a query which is working fine. Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.

In other words Child column is directly under Table2's row column name (Father), but occasionally it comes under Table1 with no relation to Table 2.

How can I out put that in a query for a treeview? I am assuming that I will have to program the out come in c# also with 3 for loops and in the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of treeview, but I am having a problem building a query in sql.

The query below shows all Parent, then child then grand child(all well and working), but what is desired is at times child takes place of a father.

declare @x as xml
set @x =
(
SELECT distinct
Table1.AssetSysID, Table1.Asset_ID , Table1.FromLR, Table1.Asset_ID + ', ' + Table1.[Desc2] as GarndFather,
Table2.ACISysID ,Table2.PAssetSysID, Table2.FeatureName + ', ' + Table2.[DESC] AS Father,
Table3.ITMSysID ,Table3.Item_ID + ',' + Table3.[DESC] as Child

[Code] .....

View 6 Replies View Related

T-SQL (SS2K8) :: How To Add Counts To A Query

Jun 11, 2014

How to get task counts in a sales report.

The data is located in three tables: Projects, ProjectTasks, Sales.

I need to group the data by month and project. It needs to include sales per month as well as the number of each project task completed that month.

Projects:
ProjectIDName
1Project1

ProjectTasks:
ProTaskIDProjectIDTaskCodeBeginDateEndDate
11 Task11/1/20141/15/2014
21Task21/15/20141/20/2014
31Task3 1/21/20141/29/2014

Sales:
SalesIDProjectIDClosingAmount
11 1/31/2014$5000

Query Output:
ClosingDateProject TotalSalesTask1sTask2sTask3s
1/2014Project1$500011 1

My query so far is:

SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate,
p.name as Project, SUM(s.amount) as TotalSales
FROM Sales s
JOIN Project p ON p.projectID = s.projectID
WHEREs.closing >= DATEADD(mm, -12, GETDATE())
GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name

This will give me the grouping by month/year and project.

View 4 Replies View Related

T-SQL (SS2K8) :: Duplicates In Sub Query

Sep 13, 2014

I have the piece of sql code here below that keeps giving out duplicates. How to resolve this.

isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1
LEFT OUTER JOIN
#BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and
a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0)
As Actual_CurrentMonth,

View 1 Replies View Related