Search Results

Search found 28024 results on 1121 pages for 'sql 2014'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 626/1121 | < Previous Page | 622 623 624 625 626 627 628 629 630 631 632 633  | Next Page >

  • Building Stored Procedure to group data into ranges with roughly equal results in each bucket

    - by Len
    I am trying to build one procedure to take a large amount of data and create 5 range buckets to display the data. the buckets ranges will have to be set according to the results. Here is my existing SP GO /****** Object: StoredProcedure [dbo].[sp_GetRangeCounts] Script Date: 03/28/2010 19:50:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_GetRangeCounts] @idMenu int AS declare @myMin decimal(19,2), @myMax decimal(19,2), @myDif decimal(19,2), @range1 decimal(19,2), @range2 decimal(19,2), @range3 decimal(19,2), @range4 decimal(19,2), @range5 decimal(19,2), @range6 decimal(19,2) SELECT @myMin=Min(modelpropvalue), @myMax=Max(modelpropvalue) FROM xmodelpropertyvalues where modelPropUnitDescriptionID=@idMenu set @myDif=(@myMax-@myMin)/5 set @range1=@myMin set @range2=@myMin+@myDif set @range3=@range2+@myDif set @range4=@range3+@myDif set @range5=@range4+@myDif set @range6=@range5+@myDif select @myMin,@myMax,@myDif,@range1,@range2,@range3,@range4,@range5,@range6 select t.range as myRange, count(*) as myCount from ( select case when modelpropvalue between @range1 and @range2 then 'range1' when modelpropvalue between @range2 and @range3 then 'range2' when modelpropvalue between @range3 and @range4 then 'range3' when modelpropvalue between @range4 and @range5 then 'range4' when modelpropvalue between @range5 and @range6 then 'range5' end as range from xmodelpropertyvalues where modelpropunitDescriptionID=@idmenu) t group by t.range order by t.range This calculates the min and max value from my table, works out the difference between the two and creates 5 buckets. The problem is that if there are a small amount of very high (or very low) values then the buckets will appear very distorted - as in these results... range1 2806 range2 296 range3 75 range5 1 Basically I want to rebuild the SP so it creates buckets with equal amounts of results in each. I have played around with some of the following approaches without quite nailing it... SELECT modelpropvalue, NTILE(5) OVER (ORDER BY modelpropvalue) FROM xmodelpropertyvalues - this creates a new column with either 1,2,3,4 or 5 in it ROW_NUMBER()OVER (ORDER BY modelpropvalue) between @range1 and @range2 ROW_NUMBER()OVER (ORDER BY modelpropvalue) between @range2 and @range3 or maybe i could allocate every record a row number then divide into ranges from this?

    Read the article

  • Query next/previous record

    - by Rob
    I'm trying to find a better way to get the next or previous record from a table. Let's say I have a blog or news table: CREATE TABLE news ( news_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, news_datestamp DATETIME NOT NULL, news_author VARCHAR(100) NOT NULL, news_title VARCHAR(100) NOT NULL, news_text MEDIUMTEXT NOT NULL ); Now on the frontend I want navigation buttons for the next or previous records, if i'm sorting by news_id, I can do something rather simple like: SELECT MIN(news_id) AS next_news_id FROM news WHERE news_id > '$old_news_id' LIMIT 1 SELECT MAX(news_id) AS prev_news_id FROM news WHERE news_id < '$old_news_id' LIMIT 1 But the news can be sorted by any field, and I don't necessarily know which field is sorted on, so this won't work if the user sorts on news_author for example. I've resorted to the rather ugly and inefficient method of sorting the entire table and looping through all records until I find the record I need. $res = mysql_query("SELECT news_id FROM news ORDER BY `$sort_column` $sort_way"); $found = $prev = $next = 0; while(list($id) = mysql_fetch_row($res)) { if($found) { $next = $id; break; } if($id == $old_news_id) { $found = true; continue; } $prev = $id; } There's got to be a better way.

    Read the article

  • Only the last run in a for loop in Javascript works

    - by Mengfei Murphy
    Here is a for loop written by Javascript. It is trying to apply queries for websql. for (var i = 0; i < 10; i++){ db.transaction(function (tx){ tx.executeSql('INSERT INTO ProjSetsT (ProjID) VALUES (?);', [i]); }); } The attempt is obvious, I tried to add values "0, 1, 2, ... 9" into column ProjID in table ProjSetsT. It does not work. I only got the last element, i.e. "9" inserted, but not the first eight numbers. Is there any syntax mistakes?

    Read the article

  • SqlServer2008 + expensive union

    - by Tim Mahy
    Hi al, we have 5 tables over which we should query with user search input throughout a stored procedure. We do a union of the similar data inside a view. Because of this the view can not be materialized. We are not able to change these 5 tables drastically (like creating a 6th table that contains the similar data of the 5 tables and reference that new one from the 5 tables). The query is rather expensive / slow what are our other options? It's allowed to think outside the box. Unfortunately I cannot give more information like the table/view/SP definition because of customer confidentiality... greetings, Tim

    Read the article

  • Why does this MySQL function return null?

    - by Shore
    Description: the query actually run have 4 results returned,as can be see from below, what I did is just concate the items then return, but unexpectedly,it's null. I think the code is self-explanatory: DELIMITER | DROP FUNCTION IF EXISTS get_idiscussion_ask| CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC BEGIN DECLARE done INT DEFAULT 0; DECLARE body varchar(600); DECLARE created DATETIME; DECLARE anonymous TINYINT(1); DECLARE screen_name varchar(64); DECLARE result TEXT; DECLARE cur1 CURSOR FOR SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=iask_id; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SET result = ''; OPEN cur1; REPEAT FETCH cur1 INTO body, created, anonymous, screen_name; SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>'); UNTIL done END REPEAT; CLOSE cur1; RETURN result; END | DELIMITER ; mysql> DELIMITER ; mysql> select get_idiscussion_ask(1); +------------------------+ | get_idiscussion_ask(1) | +------------------------+ | NULL | +------------------------+ 1 row in set (0.01 sec) mysql> SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=1; +------+---------------------+-----------+-------------+ | body | created | anonymous | screen_name | +------+---------------------+-----------+-------------+ | haha | 2009-05-27 04:57:51 | 0 | NULL | | haha | 2009-05-27 04:57:52 | 0 | NULL | | haha | 2009-05-27 04:57:52 | 0 | NULL | | haha | 2009-05-27 04:57:53 | 0 | NULL | +------+---------------------+-----------+-------------+ 4 rows in set (0.00 sec) For those who don't think the code is self-explanatory: Why the function returns NULL?

    Read the article

  • Modifying Gridview select action?

    - by Shawn
    I have a gridview and when a user selects a row I want to change the view in my multiview and display several new gridviews. A user would be clicking on a computer, and then it will display the computer stats/atached devices/etc. The new gridviews are going to need a column from the row that was selected, how do I get that? Thanks.

    Read the article

  • Date and time Query - problem

    - by Gold
    hi i try to run this query: select * from WorkTbl where ((Tdate = '20100414' AND Ttime = '06:00') and (Tdate <= '20100415' AND Ttime <= '06:00')) i have this date: 14/04/2010 and time: 14:00 i cant see hem, how to fix the query ? thank's in advance

    Read the article

  • Conditionally Summing the same Column multiple times in a single select statement?

    - by btollett
    I have a single table that shows employee deployments, for various types of deployment, in a given location for each month: ID | Location_ID | Date | NumEmployees | DeploymentType_ID As an example, a few records might be: 1 | L1 | 12/2010 | 7 | 1 (=Permanent) 2 | L1 | 12/2010 | 2 | 2 (=Temp) 3 | L1 | 12/2010 | 1 | 3 (=Support) 4 | L1 | 01/2011 | 4 | 1 5 | L1 | 01/2011 | 2 | 2 6 | L1 | 01/2011 | 1 | 3 7 | L2 | 12/2010 | 6 | 1 8 | L2 | 01/2011 | 6 | 1 9 | L2 | 12/2010 | 3 | 2 What I need to do is sum the various types of people by date, such that the results look something like this: Date | Total Perm | Total Temp | Total Supp 12/2010 | 13 | 5 | 1 01/2011 | 10 | 2 | 1 Currently, I've created a separate query for each deployment type that looks like this: SELECT Date, SUM(NumEmployees) AS "Total Permanent" FROM tblDeployment WHERE DeploymentType_ID=1 GROUP BY Date; We'll call that query qSumPermDeployments. Then, I'm using a couple of joins to combine the queries: SELECT qSumPermDeployments.Date, qSumPermDeployments.["Total Permanent"] AS "Permanent" qSumTempDeployments.["Total Temp"] AS "Temp" qSumSupportDeployments.["Total Support"] AS Support FROM (qSumPermDeployments LEFT JOIN qSumTempDeployments ON qSumPermDeployments.Date = qSumTempDeployments.Date) LEFT JOIN qSumSupportDeployments ON qSumPermDeployments.Date = qSumSupportDeployments.Date; Note that I'm currently constructing that final query under the assumption that a location will only have temp or support employees if they also have permanent employees. Thus, I can create the joins using the permanent employee results as the base table. Given all of the data I currently have, that assumption holds up, but ideally I'd like to move away from that assumption. So finally, my question. Is there a way to simplify this down to a single query or is it best to separate it out into multiple queries - if for no other reason that readability.

    Read the article

  • How to use a getter with a nullable?

    - by Desmond Lost
    I am reading a bunch of queries from a database. I had an issue with the queries not closing, so I added a CommandTimeout. Now, the individual queries read from the config file each time they are run. How would I make the code cache the int from the config file only once using a static nullable and getter. I was thinking of doing something along the lines of: static int? var; get{ var = null; if (var.HasValue) ...(i dont know how to complete the rest) My actual code: private object ExecuteQuery(string dbConnStr, bool fixIt) { object result = false; using (SqlConnection connection = new SqlConnection(dbConnStr)) { connection.Open(); using (SqlCommand sqlCmd = new SqlCommand()) { AddSQLParms(sqlCmd); sqlCmd.CommandTimeout = 30; sqlCmd.CommandText = _cmdText; sqlCmd.Connection = connection; sqlCmd.CommandType = System.Data.CommandType.Text; sqlCmd.ExecuteNonQuery(); } connection.Close(); } return result; }}

    Read the article

  • Cassandra Production ready on Windows?

    - by BlackTea
    Question anyone know of any success stories of Cassandra running on windows in a production environment? I'm doing some work on Cassandra and trying to find the correct platform for it currently the platform is windows running MS-SQLas the data store. what are the dis-advantages if any when running Cassandra on a windows environment.

    Read the article

  • count(*) is it really expensive ?

    - by Anil Namde
    I have a page where i have 4 tabs displaying 4 different reports based of different tables. Now i get row count of each tabled using select count() from table query and display number of rows available in each table on the tabs. Now with each page post back 5 count() queries are executed (4 to get counts and 1 for pagination) and 1 query for getting report. Now my question is should is count(*) query really expensive that i should keep the row counts (at least which are displayed on tab) in view state of page instead of queering each time? How much expensive it is ?

    Read the article

  • Good strategy for copying a "sliding window" of data from a table?

    - by chiborg
    I have a MySQL table from a third-party application that has millions of rows and only one index - the timestamp of each entry. Now I want to do some heavy self-joins and queries on the data using fields other than the timestamp. Doing the query on the original table would bring the database to a crawl, adding indexes to the table is not an option. Additionally, I only need entries that are newer than one week. My current strategy for doing the queries efficiently is to use a separate table (aux_table) that has the necessary indexes. My questions are: Is there another way to do the queries? and if not, How do I update the data in the indexed table efficiently? So far I have found two approaches for updating aux_table: Truncate aux_table and insert the desired data from the original table. Not very efficient because all the indexes must be re-crated. Check for the biggest timestamp in aux_table and insert all entries with a greater or equal timestamp from the original table. Occasionally drop older entries. Only copying entries with greater timestamp leads to dropped entries (because of entries with same timestamp that were inserted into the original table after the last update).

    Read the article

  • Should I catch exceptions thrown when closing java.sql.Connection

    - by jb
    Connection.close() may throw SqlException, but I have always assumed that it is safe to ignore any such exceptions (and I have never seen code that does not ignore them). Normally I would write: try{ connection.close(); }catch(Exception e) {} Or try{ connection.close(); }catch(Exception e) { logger.log(e.getMessage(), e); } The question is: Is it bad practice (and has anyone had problems when ignoring such exeptions). When Connection.close() does throw any exception. If it is bad how should I handle the exception. Comment: I know that discarding exceptions is evil, but I'm reffering only to exceptions thrown when closing a connection (and as I've seen this is fairly common in this case). Does anyone know when Connection.close() may throw anything?

    Read the article

  • IN statement performance in PostgreSQL (and in general)

    - by Vasil
    I know this has probably been asked before, but I can't find it with SO's search. Lets say i've TABLE1 and TABLE2, who should I expect the performance of a query such as this: SELECT * FROM TABLE1 WHERE id IN SUBQUERY_ON_TABLE2; as the number of rows in TABLE1 and TABLE2 grow and id is a primary key on TABLE1. Yes, I know using IN is such a n00b mistake, but TABLE2 has a generic relation (django generic relation) to multiple other tables so I can't think of another way to filter the data. At what (aproximate) ammount of rows in TABLE1 and TABLE2 should I expect to notice performance issues because of this? Will performance degrade linearly, exponentially etc. depending on the number of rows?

    Read the article

  • How to select all parent objects into DataContext using single LINQ query ?

    - by too
    I am looking for an answer to a specific problem of fetching whole LINQ object hierarchy using single SELECT. At first I was trying to fill as much LINQ objects as possible using LoadOptions, but AFAIK this method allows only single table to be linked in one query using LoadWith. So I have invented a solution to forcibly set all parent objects of entity which of list is to be fetched, although there is a problem of multiple SELECTS going to database - a single query results in two SELECTS with the same parameters in the same LINQ context. For this question I have simplified this query to popular invoice example: public static class Extensions { public static IEnumerable<T> ForEach<T>(this IEnumerable<T> collection, Action<T> func) { foreach(var c in collection) { func(c); } return collection; } } public IEnumerable<Entry> GetResults(AppDataContext context, int CustomerId) { return ( from entry in context.Entries join invoice in context.Invoices on entry.EntryInvoiceId equals invoice.InvoiceId join period in context.Periods on invoice.InvoicePeriodId equals period.PeriodId // LEFT OUTER JOIN, store is not mandatory join store in context.Stores on entry.EntryStoreId equals store.StoreId into condStore from store in condStore.DefaultIfEmpty() where (invoice.InvoiceCustomerId = CustomerId) orderby entry.EntryPrice descending select new { Entry = entry, Invoice = invoice, Period = period, Store = store } ).ForEach(x => { x.Entry.Invoice = Invoice; x.Invoice.Period = Period; x.Entry.Store = Store; } ).Select(x => x.Entry); } When calling this function and traversing through result set, for example: var entries = GetResults(this.Context); int withoutStore = 0; foreach(var k in entries) { if(k.EntryStoreId == null) withoutStore++; } the resulting query to database looks like (single result is fetched): SELECT [t0].[EntryId], [t0].[EntryInvoiceId], [t0].[EntryStoreId], [t0].[EntryProductId], [t0].[EntryQuantity], [t0].[EntryPrice], [t1].[InvoiceId], [t1].[InvoiceCustomerId], [t1].[InvoiceDate], [t1].[InvoicePeriodId], [t2].[PeriodId], [t2].[PeriodName], [t2].[PeriodDateFrom], [t4].[StoreId], [t4].[StoreName] FROM [Entry] AS [t0] INNER JOIN [Invoice] AS [t1] ON [t0].[EntryInvoiceId] = [t1].[InvoiceId] INNER JOIN [Period] AS [t2] ON [t2].[PeriodId] = [t1].[InvoicePeriodId] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[StoreId], [t3].[StoreName] FROM [Store] AS [t3] ) AS [t4] ON [t4].[StoreId] = ([t0].[EntryStoreId]) WHERE (([t1].[InvoiceCustomerId]) = @p0) ORDER BY [t0].[InvoicePrice] DESC -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [186] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 SELECT [t0].[EntryId], [t0].[EntryInvoiceId], [t0].[EntryStoreId], [t0].[EntryProductId], [t0].[EntryQuantity], [t0].[EntryPrice], [t1].[InvoiceId], [t1].[InvoiceCustomerId], [t1].[InvoiceDate], [t1].[InvoicePeriodId], [t2].[PeriodId], [t2].[PeriodName], [t2].[PeriodDateFrom], [t4].[StoreId], [t4].[StoreName] FROM [Entry] AS [t0] INNER JOIN [Invoice] AS [t1] ON [t0].[EntryInvoiceId] = [t1].[InvoiceId] INNER JOIN [Period] AS [t2] ON [t2].[PeriodId] = [t1].[InvoicePeriodId] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[StoreId], [t3].[StoreName] FROM [Store] AS [t3] ) AS [t4] ON [t4].[StoreId] = ([t0].[EntryStoreId]) WHERE (([t1].[InvoiceCustomerId]) = @p0) ORDER BY [t0].[InvoicePrice] DESC -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [186] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 The question is why there are two queries and how can I fetch LINQ objects without such hacks?

    Read the article

  • Fact table with multiple facts

    - by Jeff Meatball Yang
    I have a dimension (SiteItem) has two important facts: perUserClicks perBrowserClicks however, within this dimension, I have groups of dimensions based on an attribute column (let's call the groups AboveFoldItems, LeftNavItems, OnTheFlyItems, etc.) each have more facts that are specific to that group: AboveFoldItems: eyeTime, loadTime LeftNavItems: mouseOverTime OnTheFlyItems: doesn't have any extra, but may in the future Is the following fact table schema ok? DateKey SessionKey SiteItemKey perUserClicks perBrowserClicks eyeTime loadTime mouseOverTime It seems a little wasteful since only some columns pertain to some dimension keys (the irrelevant facts are left NULL). But... this seems like it would be a common problem, so there should be a common solution for this, right?

    Read the article

  • Pick Return Values of Stored Procedure

    - by Juergen
    Hi, I have a stored procedure that returns a result with 250!!! columns. But I only need 3 out of the 250. I want to call the SP and put only the 3 column values in a temp table. I don't want to define a temp table with 250 columns! This is how I would like to do it, but this doesn't work of course: create #myTempTable (id int, value1 int, value2 int) insert into #myTempTable exec myBigFatStoredProc Can it be done anyhow? Bye Juergen

    Read the article

  • How can I make a multi search SPROC/UDF by passing a tabled-value to it?

    - by Shimmy
    I actually want to achieve the following description This is the table argument I want to pass to the server <items> <item category="cats">1</item> <item category="dogs">2</item> </items> SELECT * FROM Item WHERE Item.Category = <one of the items in the XML list> AND Item.ReferenceId = <the corresponding value of that item xml element> --Or in other words: SELECT FROM Items WHERE Item IN XML according to the splecified columns. Am I clear enought? I don't mind to do it in a different way other than xml. What I need is selecting values that mach an array of two of its columns' values.

    Read the article

  • MySQL query to order by parent then child places

    - by Swanny
    I have a table of pages in my database, each page can have a parent as below: id parent_id title 1 0 Home 2 0 Sitemap 3 0 Products 4 3 Product 1 5 3 Product 2 6 4 Product 1 Review Page What would be the best MySQL query to select all pages ordered by parent then child then child again if there is more than one level, there will be a maximum of three levels. The above example would produce the desired order: Home Sitemap Products Product 1 Product 1 Review Page Product 2

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 622 623 624 625 626 627 628 629 630 631 632 633  | Next Page >