Search Results

Search found 38660 results on 1547 pages for 'sql index'.

Page 331/1547 | < Previous Page | 327 328 329 330 331 332 333 334 335 336 337 338  | Next Page >

• choosing row (from group) with max value in a SQL Server Database

  - by sriehl

  I have a large database and am putting together a report of the data. I have aggregated and summed the data from many tables to get two tables that look like the following. id | code | value id | code | value 13 | AA | 0.5 13 | AC | 2.0 13 | AB | 1.0 14 | AB | 1.5 14 | AA | 2.0 13 | AA | 0.5 15 | AB | 0.5 15 | AB | 3.0 15 | AD | 1.5 15 | AA | 1.0 I need to get a list of id's, with the code (sumed from both tables) with the largest value. 13 | AC 14 | AA 15 | AB There are 4-6 thousand records and it is not possible to change the original tables. I'm not too worried about performance as I only need to run it a few times a year. edit: Let me see if I can explain a bit more clearly, imagine the id is the customer id, the code is who they ordered from and the value is how much they spent there. I need a list of the all the customer id's and the store that customer spent the most money at (and if they spent the same at two different stores, put a value such as 'ZZ' in for the store name).

  Read the article

• Using SQL Server Intergration Services (SSIS) can you read a file from a FileStream column in SQL Se

  - by tbrovold

  I am trying to create a tool that I can upload different types of files "csv", Excel, XML and load those files into a FileStream column in the database as "Source" untouched over the web. Then using SSIS on the server I want to create a package that will process that file to be loaded into other tables to be used by the web application. Is it possible to have SSIS read a file from FileStream column? if so how?

  Read the article

• Query design in SQL - ORDER BY SUM() of field in rows which meet a certain condition

  - by Christian Mann

  OK, so I have two tables I'm working with - project and service, simplified thus: project ------- id PK name str service ------- project_id FK for project time_start int (timestamp) time_stop int (timestamp) One-to-Many relationship. Now, I want to return (preferably with one query) a list of an arbitrary number of projects, sorted by the total amount of time spent at them, which is found by SUM(time_stop) - SUM(time_start) WHERE project_id = something. So far, I have SELECT project.name FROM service LEFT JOIN project ON project.id = service.project_id LIMIT 100 but I cannot figure out how what to ORDER BY.

  Read the article

• Lucene.NET search index approach

  - by Tim Peel

  Hi, I am trying to put together a test case for using Lucene.NET on one of our websites. I'd like to do the following: Index in a single unique id. Index across a comma delimitered string of terms or tags. For example. Item 1: Id = 1 Tags = Something,Separated-Term I will then be structuring the search so I can look for documents against tag i.e. tags:something OR tags:separate-term I need to maintain the exact term value in order to search against it. I have something running, and the search query is being parsed as expected, but I am not seeing any results. Here's some code. My parser (_luceneAnalyzer is passed into my indexing service): var parser = new QueryParser(Lucene.Net.Util.Version.LUCENE_CURRENT, "Tags", _luceneAnalyzer); parser.SetDefaultOperator(QueryParser.Operator.AND); return parser; My Lucene.NET document creation: var doc = new Document(); var id = new Field( "Id", NumericUtils.IntToPrefixCoded(indexObject.id), Field.Store.YES, Field.Index.NOT_ANALYZED, Field.TermVector.NO); var tags = new Field( "Tags", string.Join(",", indexObject.Tags.ToArray()), Field.Store.NO, Field.Index.ANALYZED, Field.TermVector.YES); doc.Add(id); doc.Add(tags); return doc; My search: var parser = BuildQueryParser(); var query = parser.Parse(searchQuery); var searcher = Searcher; TopDocs hits = searcher.Search(query, null, max); IList<SearchResult> result = new List<SearchResult>(); float scoreNorm = 1.0f / hits.GetMaxScore(); for (int i = 0; i < hits.scoreDocs.Length; i++) { float score = hits.scoreDocs[i].score * scoreNorm; result.Add(CreateSearchResult(searcher.Doc(hits.scoreDocs[i].doc), score)); } return result; I have two documents in my index, one with the tag "Something" and one with the tags "Something" and "Separated-Term". It's important for the - to remain in the terms as I want an exact match on the full value. When I search with "tags:Something" I do not get any results. Question What Analyzer should I be using to achieve the search index I am after? Are there any pointers for putting together a search such as this? Why is my current search not returning any results? Many thanks

  Read the article

• SQL: Daily Average of Logins Per Hour

  - by jerrygarciuh

  This query is producing counts of logins per hour: SELECT DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0), COUNT(*) FROM EVENTS_ALL_RPT_V1 WHERE EVENT_NAME = 'Login' AND EVENT_DATETIME >= CONVERT(DATETIME, '2010-03-17 00:00:00', 120) AND EVENT_DATETIME <= CONVERT(DATETIME, '2010-03-24 00:00:00', 120) GROUP BY DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0) ORDER BY DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0) ...with lots of results like this: Datetime COUNT(*) ---------------------------------- 2010-03-17 12:00:00.000 135 2010-03-17 13:00:00.000 129 2010-03-17 14:00:00.000 147 What I need to figure out is how to query the average logins per hour for a given day. Any help?

  Read the article

• SQL 2003 Distance Latitude Longitude

  - by J.Hendrix

  I have a table full of Dealers along with their latitude and longitude. I am trying to determine the top n closest dealers to any given lat and lon. I already have the function to calculate distance between locations, but I want to do as few calculations as possible (my table can contain many thousands of entries). Currently I have to calculate the distance for each entry then sort them. Is there any way to sort before I do the calculation to improve performance? This question is good, but I will not always know my range. Should I just pick an arbitrarily high range then refine my results? I am thankful for any help the community can offer. declare @Lat real declare @lon real Set @lat = 41.05 Set @lon = -73.53 SELECT top 10 MemberID, Address1, City, State, Zip, Phone, Lat, Lon, (SELECT fun_DistanceLatLon] (@Lat,@lon,Lat,Lon)) as mDistance --Calculate distance FROM Dealers Order by (SELECT fun_DistanceLatLon] (@Lat,@lon,Lat,Lon))

  Read the article

• sql sum data from multiple tables

  - by Iulian

  I have 2 tables AP and INV where both have the columns [PROJECT] and [Value]. I want a query to return something like this : PROJECT | SUM_AP | SUM_INV I came up with the code below but it's returning the wrong results ( sum is wrong ). SELECT SUM(AP.Value) AS SUM_AP, SUM(INV.Value) AS SUM_INV FROM AP INNER JOIN INV ON (AP.[PROJECT] =INV.[PROJECT]) WHERE AP.[PROJECT] = 'XXXXX' GROUP BY AP.[PROJECT]

  Read the article

• Calculating the average color between two colors in PHP, using an index number as reference value

  - by Roel Krottje

  Hi all! In PHP, I am trying to calculate the average color (in hex) between to different hex colors.. However, I also need to be able to supply an index number between 0.0 and 1.0. So for example: I have $color1 = "#ffffff" and $color2 = "#0066CC" .. If I would write a function to get the average color and I would supply 0.0 as the index number, the function would need to return "#ffffff". If I would supply 1.0 as the index number, the function would need to return "#0066CC".. However if I would supply 0.2, the function would need to return an average color between the two colors, but still closer to color1 than to color2.. If I would supply index number 0.5, I would get the exact average color of both colors.. I have been trying to accomplish this for several days now but I can't seem to figure it out..! Any help would therefor be greatly appreciated!.. Thanks!

  Read the article

• LINQ to SQL - Tracking New / Dirty Objects

  - by Joseph Sturtevant

  Is there a way to determine if a LINQ object has not yet been inserted in the database (new) or has been changed since the last update (dirty)? I plan on binding my UI to LINQ objects (using WPF) and need it to behave differently depending whether or not the object is already in the database. MyDataContext context = new MyDataContext(); MyObject obj; if (new Random().NextDouble() > .5) obj = new MyObject(); else obj = context.MyObjects.First(); // How can I distinguish these two cases? The only simple solution I can think of is to set the primary key of new records to a negative value (my PKs are an identity field and will therefore be set to a positive integer on INSERT). This will only work for detecting new records. It also requires identity PKs, and requires control of the code creating the new object. Is there a better way to do this? It seems like LINQ must be internally tracking the status of these objects so that it can know what to do on context.SubmitChanges(). Is there some way to access that "object status"? Clarification Apparently my initial question was confusing. I'm not looking for a way to insert or update records. I'm looking for a way, given any LINQ object, to determine if that object has not been inserted (new) or has been changed since its last update (dirty).

  Read the article

• Make SQL Select same row multiple times

  - by Emre

  I need to test my mail server. How can I make a Select statement that selects say ID=5469 a thousand times.

  Read the article

• SQL Server 2008 Geography .STBuffer() distance measurement units

  - by Chris

  I'm working with a geographic point using lat/long and need to find other points in our database within a 5 mile radius of that point. However, I can't seem to find out what the "units" are for STBuffer, it doesn't seem to conform to feet, miles, meters, kilometers, etc. The documentation only refers to them as "units", any suggestions? Thanks [...] from geography::STGeomFromText('POINT(x y)', 4326).STBuffer(z).STIntersects(geography::STGeomFromText('POINT(' + CAST(v.Longitude as varchar(max)) + ' ' + CAST(v.Latitude as varchar(max)) + ')', 4326)) = 1

  Read the article

• SQL server virtual memory usage and performance

  - by user365035

  Hello, I have a very large DB used mostly for analytics. The performance overall is very sluggish. I just noticed that when running the query below, the amount of virtual memory used greatly exceeds the amount of physical memory available. Currently, physical memory is 10GB (10238k bytes) whereas the virtual memory returns significantly more - 8388607k bytes. That seems really wrong, but I'm at a bit of a loss on how to proceed. USE [master]; GO select cpu_count , hyperthread_ratio , physical_memory_in_bytes / 1048576 as 'mem_MB' , virtual_memory_in_bytes / 1048576 as 'virtual_mem_MB' , max_workers_count , os_error_mode , os_priority_class from sys.dm_os_sys_info

  Read the article

• SQL Logic Operator Precedence: And and Or

  - by nc

  Are the two statements below equivalent? SELECT [...] FROM [...] WHERE some_col in (1,2,3,4,5) AND some_other_expr and SELECT [...] FROM [...] WHERE some_col in (1,2,3) or some_col in (4,5) AND some_other_expr Is there some sort of truth table I could use to verify this? Thanks.

  Read the article

• SQL Query - Count column values separately

  - by user575535

  I have a hard time getting a Query to work right. This is the DDL for my Tables CREATE TABLE Agency ( id SERIAL not null, city VARCHAR(200) not null, PRIMARY KEY(id) ); CREATE TABLE Customer ( id SERIAL not null, fullname VARCHAR(200) not null, status VARCHAR(15) not null CHECK(status IN ('new','regular','gold')), agencyID INTEGER not null REFERENCES Agency(id), PRIMARY KEY(id) ); Sample Data from the Tables AGENCY id|'city' 1 |'London' 2 |'Moscow' 3 |'Beijing' CUSTOMER id|'fullname' |'status' |agencyid 1 |'Michael Smith' |'new' |1 2 |'John Doe' |'regular'|1 3 |'Vlad Atanasov' |'new' |2 4 |'Vasili Karasev'|'regular'|2 5 |'Elena Miskova' |'gold' |2 6 |'Kim Yin Lu' |'new' |3 7 |'Hu Jintao' |'regular'|3 8 |'Wen Jiabao' |'regular'|3 I want to produce the following output, but i need to count separately for ('new','regular','gold') 'city' |new_customers|regular_customers|gold_customers 'Moscow' |1 |1 |1 'Beijing'|1 |2 |0 'London' |1 |1 |0

  Read the article

• SSIS Data Flow Task SQL 2008

  - by Gerard

  Hi All, I am wondering if it is possible to: 1) Develop SSIS Package for Data Flow Task I am aware of how to do this on a local or network SQLServer, However is it possible to create a package that uploads to a "remote" sqlserver, ie one that is not on site or on the LAN. any guidance would be great Thanks

  Read the article

• Using SQL to calculate a date within a certain date range

  - by David Passmore

  Hi, I'm using access 2007 and i am trying to set up a database to act as a library system, i need a function that automatically calculates a date 14 days after a given date i.e. Loan Date vs. Due Date. But the library is open only on certain Days. So i need the 14 days not to include the dates that are out of hours, like weekends and school holidays etc. I think i need to use Select Case or IIF's? Help would be appreciated! Thanks David

  Read the article

• C# LINQ to SQL Except Operator

  - by kb

  Hi i have a list of event Ids that i want to be excluded from my select statement, but no sure how to implement this: this is what stores my list of event Ids List<int> ExcludedEvents; and this is my select statement (from an XML feed) var allEvents = from eventsList in xmlDoc.Elements("shows").Elements("Show") select new EventFeed() { EventName = eventsList.Attribute("Name").Value, EventSummary = eventsList.Attribute("ShortDesc").Value, EventDetails = eventsList.Attribute("LongDesc").Value, EventShowCode = eventsList.Attribute("Code").Value }; i want to select all events except for the events that have their eventId matching the EventShowCode value i have looked at the except operator, but not sure how to implement it thanks

  Read the article

• Standards for Encrypting data in SOX/HIPAA in SQL Server 200x

  - by Greens

  I was wondering if there are any standards for encrypting sensitive data in Database which are compliant w/ SOX or HIPAA. Or does the Encrypt Function in SQLServer necessary?. or handle in Business logic. Any ideas or links we have.

  Read the article

• How can you tell if a trigger is enabled in SQL Server 2000

  - by Mark Brady

  This shouldn't be hard to do. I'd expect Enterprise Manager to show a folder of trigger and a list and an icon... but I don't see it anywhere. My google results get me answers where I have to write code. Are you kidding me? The only way is by writing code?

  Read the article

• SQL: Gather right hand values from a join

  - by Max Williams

  Let's say a question has many tags, via a join table called taggings. I do a join thus: SELECT DISTINCT `questions`.id FROM `questions` LEFT OUTER JOIN `taggings` ON `taggings`.taggable_id = `questions`.id LEFT OUTER JOIN `tags` ON `tags`.id = `taggings`.tag_id I want to order the results according to a particular tag name, eg 'piano', so that piano is at the top, then by all the other tags in alphabetical order. Currently i'm using this order clause: ORDER BY (tags.name = 'piano') desc, tags.name Which is going completely wrong - the first results i get back aren't even tagged with 'piano' at all. I think my problem is that i need to group the tag names somehow and do my ordering test against that: i think that doing it against the straight tags.name isn't working due to the structure of the resultant join table (it does work if i just do a simple select on the tags table) but i can't get my head around how to fix it. grateful for any advice, max

  Read the article

• Formula parsing / evaluation routine or library with generic DLookup functionality

  - by tbone

  I am writing a .Net application where I must support user-defined formulas that can perform basic mathematics, as well as accessing data from any arbitrary table in the database. I have the math part working, using JScript Eval(). What I haven't decided on is what a nice way is to do the generic table lookups. For example, I may have a formula something like: Column: BonusAmount Formula: {CurrentSalary} * 1.5 * {[SystemSettings][Value][SettingName=CorpBonus AND Year={Year}]} So, in this example I would replace {xxx} and {Year} with the value of Column xxx from the current table, and I would replace the second part with the value of (select Value from SystemSettings WHERE SettingName='CorpBonus' AND Year=2008) So, basically, I am looking for something very much like the MS Access DLookup function: DLookup ( expression, domain, [criteria] ) DLookup("[UnitPrice]", "Order Details", "OrderID = 10248") But, I also need to overall parsing routine that can tell whether to just look up in the current row, or to look into another table. Would also be nice to support aggregate functions (ie: DAvg, DMax, etc), as well as all the weird edge cases handled. So I wonder if anyone knows of any sort of an existing library, or has a nice routine that can handle this formula parsing and database lookup / aggregate function resolution requirements.

  Read the article

• sql - HAVING at least 10

  - by every_answer_gets_a_point

  i would like to display results for values that are only 10 and over select name, count(*) from actor join casting on casting.actorid = actor.id where casting.ord = 1 group by name order by 2 desc that will return this: name count(*) Sean Connery 19 Harrison Ford 19 Robert De Niro 18 Sylvester Stallone 18 etc but i want to return values of count(*) that are only above 10 how do i do this? with having?

  Read the article

• Spitting out a Index view using parameters

  - by George

  Hello guys. I'm using ASP.MVC and trying to learn... I have the following controller // get all authors public ActionResult Index() { var autores = autorRepository.FindAllAutores(); return View("Index", autores); } // get authors by type public ActionResult Index(int id) { var autores = autorRepository.FindAllAutoresPorTipo(id); return View("Index", autores); } If i try http://server/Autor/1 I get a 404 error. Why is that? I even tried to create a specific method ListByType(int id) and the correspondent view, but that does not work too (URL: http://server/Autor/ListByType/1) Any ideas? Thanks in advanced EDIT Oh, the http://server/Autor works just fine. The method without parameters is spitting out my view correctly.

  Read the article

• Linq-to-sql orderby thenby

  - by Curtis White

  I'm using the following query syntax from table where where orderby orderby Where the first orderby is a date and second orderby is a date. I would assume this would work like orderby thenby but appears to be doing something else. 1 How can I do an orderby thenby using the above syntax without using extension syntax. (Got it) 2 And what does the orderby, orderby do?

  Read the article

• SQL orderby / case issue: orderdirection fail

  - by Joris

  I got a stored procedure that delivers a table of students, and it needs to order by surname, name etc... it also needs to sort ascending, descending, depending on the parameter @orderby... code: ORDER BY CASE WHEN @orderby = 'studentkey' THEN Studentkey END ASC, CASE WHEN @orderby = 'studentkey' and @desc = 1 THEN Studentkey END DESC, CASE WHEN @orderby = 'initials' THEN Initials END ASC, CASE WHEN @orderby = 'initials' and @desc = 1 THEN Initials END DESC, CASE WHEN @orderby = 'firstname' THEN Firstname END ASC, CASE WHEN @orderby = 'firstname' and @desc = 1 THEN Firstname END DESC, CASE WHEN @orderby = 'nickname' THEN Nickname END ASC, CASE WHEN @orderby = 'nickname' and @desc = 1 THEN Nickname END DESC, CASE WHEN @orderby = 'insertion' THEN Insertion END ASC, CASE WHEN @orderby = 'insertion' and @desc = 1 THEN Insertion END DESC, CASE WHEN @orderby = 'surname' THEN Surname END ASC, CASE WHEN @orderby = 'surname' and @desc = 1 THEN Surname END DESC NED There is a difference in output between @desc = 1 and @desc = 0, but not what i desire... Does anyone have a solution?

  Read the article

< Previous Page | 327 328 329 330 331 332 333 334 335 336 337 338  | Next Page >