Search Results

Search found 32223 results on 1289 pages for 'sql 2012'.

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 422/1289 | < Previous Page | 418 419 420 421 422 423 424 425 426 427 428 429  | Next Page >

  • I need some ideas on my algortihm for a Hit Counter

    - by stckvrflw
    My algorithm is for a hit count, I am tring to not count for the same person twice if that person came to the site twice in a time internval (For example if he comes twice in 5 minutes, I want to count it as 1 for this person) Here how my database looks like UserIp UserId Date of user came 127.0.0.1 new.user.akb 26.03.2010 10:15:44 127.0.0.1 new.user.akb 26.03.2010 10:16:44 127.0.0.1 new.user.akb 26.03.2010 10:17:44 127.0.0.1 new.user.akb 26.03.2010 10:18:44 127.0.0.1 new.user.akb 26.03.2010 10:19:44 127.0.0.1 new.user.akb 26.03.2010 10:20:44 127.0.0.1 new.user.akb 26.03.2010 10:21:44 127.0.0.1 new.user.akb 26.03.2010 10:22:44 127.0.0.1 new.user.akb 26.03.2010 10:23:44 What I need to do is get number of distinct UserIPs from the table above that occured within a time interval. For example if I set the time interval for 5 minutes, and let say that is starts at 26.03.2010 10:15:44 Then I will get 2 as the results, since 1 distinct value between 10:15 to 10:20 and , 1 distinct value from 10:20 to 10:23, For example if my interval is 3 minutes than the return result will be 3 Thanks.

    Read the article

  • Efficiently select top row for each category in the set

    - by VladV
    I need to select a top row for each category from a known set (somewhat similar to this question). The problem is, how to make this query efficient on the large number of rows. For example, let's create a table that stores temperature recording in several places. CREATE TABLE #t ( placeId int, ts datetime, temp int, PRIMARY KEY (ts, placeId) ) -- insert some sample data SET NOCOUNT ON DECLARE @n int, @ts datetime SELECT @n = 1000, @ts = '2000-01-01' WHILE (@n>0) BEGIN INSERT INTO #t VALUES (@n % 10, @ts, @n % 37) IF (@n % 10 = 0) SET @ts = DATEADD(hour, 1, @ts) SET @n = @n - 1 END Now I need to get the latest recording for each of the places 1, 2, 3. This way is efficient, but doesn't scale well (and looks dirty). SELECT * FROM ( SELECT TOP 1 placeId, temp FROM #t WHERE placeId = 1 ORDER BY ts DESC ) t1 UNION ALL SELECT * FROM ( SELECT TOP 1 placeId, temp FROM #t WHERE placeId = 2 ORDER BY ts DESC ) t2 UNION ALL SELECT * FROM ( SELECT TOP 1 placeId, temp FROM #t WHERE placeId = 3 ORDER BY ts DESC ) t3 The following looks better but works much less efficiently (30% vs 70% according to the optimizer). SELECT placeId, ts, temp FROM ( SELECT placeId, ts, temp, ROW_NUMBER() OVER (PARTITION BY placeId ORDER BY ts DESC) rownum FROM #t WHERE placeId IN (1, 2, 3) ) t WHERE rownum = 1 The problem is, during the latter query execution plan a clustered index scan is performed on #t and 300 rows are retrieved, sorted, numbered, and then filtered, leaving only 3 rows. For the former query three times one row is fetched. Is there a way to perform the query efficiently without lots of unions?

    Read the article

  • Creating a stored procedure in SQL Server 2008 that will do a "facebook search"

    - by dig
    Hello, I'm trying to implement a facebook search in my system (auto suggest while typing). I've managed to code all the ajax stuff, but I'm not sure how to query the database. I've created a table called People which contains the fields: ID, FirstName, LastName, MiddleName, Email. I've also created a FTS-index on all those fields. I want to create a stored procedure that will get as a parameter the text inserted in the query box and returns the suggestions. For example, When I will write in the textbox the query "Joh Do" It will translate to the query: select * from People where contains(*, '"Joh*"') and contains(*, '"Do*"') Is there a way to do that in stored procedure? P.S I've tried to use the syntax select * from People where contains(*,'"Joh*" and "Do*"') but it didn't returned the expected results, probably because it needs to search the words on different fields. Is there a way to fix that? Thanks.

    Read the article

  • Stored Procedure IDENTITY_INSERT

    - by Jacob
    I'm recently change my data table, I remove column and add a new column that define as identity = True and identity seed = 1, identity increment = 1. When i tried to insert data to this table by STORE PROCEDURE i get this exception: An explicit value for the identity column in table 'AirConditioner' can only be specified when a column list is used and IDENTITY_INSERT is ON. I saw that i need to add this lines: SET IDENTITY_INSERT [dbo].[AirConditioner] ON and finally OFF I added and its still throw an exception... My store procedure is attached as a picture

    Read the article

  • Trying to use table aliases in SQL...

    - by user366685
    I'm a graphic designer trying my best to understand table aliases, but it's not working. Here's what I have so far: SELECT colours.colourid AS colourid1, combinations.manufacturercolourid AS colourmanid1, colours.colourname AS colourname1, colours.colourhex AS colourhex1, combinations.qecolourid2 AS colouridqe2, colours.colourid AS colourid2, colours.colourname AS colourname2, colours.colourhex AS colourhex2, colours.colourid AS colourid3, combinations.qecolourid3 AS colouridqe3, colours.colourname AS colourname3, colours.colourhex AS colourhex3, colours.colourid AS colourid4, combinations.qecolourid4 AS colouridqe4, colours.colourname AS colourname4, colours.colourhex AS colourhex4, combinations.coloursupplierid FROM combinations INNER JOIN colours ON colours.colourid = combinations.manufacturercolourid; Now, the idea is that in the colours lookup table, the id will pull the colour code, hex and name from the lookup table so that I can pull the colour code, hex and name for the 4 colours that I'm looking for. I can get this to work, but it only pulls up the first name, code and hex and I'm just not seeing what I'm doing wrong.

    Read the article

  • MongoDB equivalent of SQL "OR"

    - by Matt
    So, MongoDB defaults to "AND" when finding records. For example: db.users.find({age: {'$gte': 30}, {'$lte': 40}}); The above query finds users = 30 AND <= 40 years old. How would I find users <= 30 OR = 40 years old?

    Read the article

  • Can a Snapshot transaction fail and only partially commit in a TransactionScope?

    - by Travis Brooks
    Greetings I stumbled onto a problem today that seems sort of impossible to me, but its happening...I'm calling some database code in c# that looks something like this: using(var tran = MyDataLayer.Transaction()) { MyDataLayer.ExecSproc(new SprocTheFirst(arg1, arg2)); MyDataLayer.CallSomethingThatEventuallyDoesLinqToSql(arg1, argEtc); tran.Commit(); } I've simplified this a bit for posting, but whats going on is MyDataLayer.Transaction() makes a TransactionScope with the IsolationLevel set to Snapshot and TransactionScopeOption set to Required. This code gets called hundreds of times a day, and almost always works perfectly. However after reviewing some data I discovered there are a handful of records created by "SprocTheFirst" but no corresponding data from "CallSomethingThatEventuallyDoesLinqToSql". The only way that records should exist in the tables I'm looking at is from SprocTheFirst, and its only ever called in this one function, so if its called and succeeded then I would expect CallSomethingThatEventuallyDoesLinqToSql would get called and succeed because its all in the same TransactionScope. Its theoretically possible that some other dev mucked around in the DB, but I don't think they have. We also log all exceptions, and I can find nothing unusual happening around the time that the records from SprocTheFirst were created. So, is it possible that a transaction, or more properly a declarative TransactionScope, with Snapshot isolation level can fail somehow and only partially commit?

    Read the article

  • Linq 2 SQL Grouping Question

    - by Jack Marchetti
    var groups = from p in dc.Pool join pm in dc.PoolMembers on p.ID equals pm.PoolID group p by p.Group into grp select new { grp.ID }; This isn't working. Basically I want to do the grouping, and then select certain columns, but when I do select new { grp. } I get no intellisense, so I'm obviously doing something wrong. Any ideas?

    Read the article

  • SSIS Null Value Questions

    - by Saobi
    I have a table with 5 string columns, all can be NULLs. After I read the data from this table, I want to convert any null values into empty strings. The reason is that I need to compare these columns with columns in another table of the same schema (using conditional split), and null values would cause the comparison to evaluate to NULL. Is there any functionality in SSIS that allows me to convert NULL's to empty strings, or just not having to deal with NULL's at all?

    Read the article

  • Fill in missing values in a SELECT statement

    - by benjamin button
    If i have a table with two fields.customer id and order. let's say i have in total order ID 1,2,3,4 all the customer can have all the four orders.like below 1234 1 1234 2 1234 3 1234 4 3245 3 3245 4 5436 2 5436 4 you can see above that 3245 customer doesnt have order id 1 and 2. how could i print in the query output like 3245 1 3245 2 5436 1 5436 3 EDIT: i dont have order table but i have list of order's like we can hard code it in the query(1,2,3,4) i dont have an orders table.

    Read the article

  • Simple calculation in SQL Server

    - by meep
    I got a distance field in my database that stores the distance traveled on a specific route. I need to select all the distance fields and plus them together, then returning the result from my stored procedure. How can this be done?

    Read the article

  • sql query without subquery

    - by user1285737
    I need to rewrite this query and I'm not allowed to use a subquery. I need to select the name and color of the parts that are heavier than the wheel. SELECT name, color FROM parts WHERE weight > (SELECT weight FROM parts WHERE name="wheel"); This is the table: PARTS ID NAME COLOR WEIGHT 1 wheel black 100 2 tire black 50 3 gear red 20 Thanks in advance

    Read the article

  • Strange LINQ to SQL Behavior

    - by mcass20
    What is wrong with the last query? Is it a bug or am I missing something? This query returns 2 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>David</value>%")); This query returns 2 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>%David%</value>%")); This query returns 0 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>av</value>%")); This query returns 2 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>%av%</value>%")); This query returns 0 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>v</value>%")); This query returns 15 records (incorrect, should return 2): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>%v%</value>%"));

    Read the article

  • integrity Constraints on a table.

    - by Dinesh
    See this sample schema Passenger(id PK, Name) Plane(id PK, capacity, type); Flight(id PK, planeId FK(Plane), flightDate, StartLocation, destination) CREATE TABLE Reservation(PassengerId, flightId, PRIMARY KEY (passengerId, flightId), FOREIGN KEY (passengerId) REFERENCES Passenger, FOREIGN KEY (flightId) REFERENCES Flight); I need to define an integrity constraint that enforces the restriction that the number of passengers on a plane cannot exceed the plane’s capacity. I have tried and achieved so far is this. CREATE TABLE Reservation( passengerId INTEGER, flightId INTEGER, PRIMARY KEY (passengerId, flightId), FOREIGN KEY (passengerId) REFERENCES Passenger, FOREIGN KEY (flightId) REFERENCES Flight, Constraint check1 check(Not Exists(select * from Flight s, (select count(*) as totalRes from Reservation group by flightId) t where t.totalRes > s.capacity ) ) ); I am not sure i am doing in right way or not. Any suggestions?

    Read the article

  • Use Linq to SQL to generate sales report

    - by Richard Reddy
    I currently have the following code to generate a sales report over the last 30 days. I'd like to know if it would be possible to use linq to generate this report in one step instead of the rather basic loop I have here. For my requirement, every day needs to return a value to me so if there are no sales for any day then a 0 is returned. Any of the Sum linq examples out there don't explain how it would be possible to include a where filter so I am confused on how to get the total amount per day, or a 0 if no sales, for the last days I pass through. Thanks for your help, Rich //setup date ranges to use DateTime startDate = DateTime.Now.AddDays(-29); DateTime endDate = DateTime.Now.AddDays(1); TimeSpan startTS = new TimeSpan(0, 0, 0); TimeSpan endTS = new TimeSpan(23, 59, 59); using (var dc = new DataContext()) { //get database sales from 29 days ago at midnight to the end of today var salesForDay = dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS)); //loop through each day and sum up the total orders, if none then set to 0 while (startDate != endDate) { decimal totalSales = 0m; DateTime startDay = startDate.Date + startTS; DateTime endDay = startDate.Date + endTS; foreach (var sale in salesForDay.Where(b => b.OrderDateTime > startDay && b.OrderDateTime <= endDay)) { totalSales += (decimal)sale.OrderPrice; } Response.Write("From Date: " + startDay + " - To Date: " + endDay + ". Sales: " + String.Format("{0:0.00}", totalSales) + "<br>"); //move to next day startDate = startDate.AddDays(1); } }

    Read the article

  • Multiple column sorting (SQL SERVER 2005)

    - by Newbie
    I have a table which looks like Col1 col2 col3 col4 col5 1 5 1 4 6 1 4 0 3 7 0 1 5 6 3 1 8 2 1 5 4 3 2 1 4 The script is declare @t table(col1 int, col2 int, col3 int,col4 int,col5 int) insert into @t select 1,5,1,4,6 union all select 1,4,0,3,7 union all select 0,1,5,6,3 union all select 1,8,2,1,5 union all select 4,3,2,1,4 I want the output to be every column being sorted in ascending order i.e. Col1 col2 col3 col4 col5 0 1 0 1 3 1 3 1 1 4 1 4 2 3 5 1 5 2 4 6 4 8 5 6 7 I already solved tye problem by the folowing program Select x1.col1 ,x2.col2 ,x3.col3 ,x4.col4 ,x5.col5 From (Select Row_Number() Over(Order By col1) rn1, col1 From @t)x1 Join(Select Row_Number() Over(Order By col2) rn2, col2 From @t)x2 On x1.rn1=x2.rn2 Join(Select Row_Number() Over(Order By col3) rn3, col3 From @t)x3 On x1.rn1=x3.rn3 Join(Select Row_Number() Over(Order By col4) rn4, col4 From @t)x4 On x1.rn1=x4.rn4 Join(Select Row_Number() Over(Order By col5) rn5, col5 From @t)x5 On x1.rn1=x5.rn5 But I am not happy with this solution. Is there any better way to achieve the same? (Using set based approach) If so, could any one please show an example. Thanks

    Read the article

  • SQL LIKE question

    - by user176121
    I was wondering if there's a drawback (other than bad practice) to using something like this SELECT * FROM my_table WHERE id LIKE '1'; where id is an integer. I know you're supposed to use id=1 but I am writing a java program and if everything can use LIKE it'll be a lot easier for me. Also, so far, everything works fine; I get the correct query results, so if there is no drawback I will continue doing it like this. edit: I am using MySQL.

    Read the article

  • How do I return the IDENTITY for an inserted record from a stored Proecedure?

    - by user54197
    I am adding data to my database, but would like to retrieve the UnitID that is Auto generated. using (SqlConnection connect = new SqlConnection(connections)) { SqlCommand command = new SqlCommand("ContactInfo_Add", connect); command.Parameters.Add(new SqlParameter("name", name)); command.Parameters.Add(new SqlParameter("address", address)); command.Parameters.Add(new SqlParameter("Product", name)); command.Parameters.Add(new SqlParameter("Quantity", address)); command.Parameters.Add(new SqlParameter("DueDate", city)); connect.Open(); command.ExecuteNonQuery(); } ... ALTER PROCEDURE [dbo].[Contact_Add] @name varchar(40), @address varchar(60), @Product varchar(40), @Quantity varchar(5), @DueDate datetime AS BEGIN SET NOCOUNT ON; INSERT INTO DBO.PERSON (Name, Address) VALUES (@name, @address) INSERT INTO DBO.PRODUCT_DATA (PersonID, Product, Quantity, DueDate) VALUES (@Product, @Quantity, @DueDate) END

    Read the article

  • Looping over some selected values in a stored procedure

    - by macca1
    I'm trying to modify a stored procedure hooked into an ORM tool. I want to add a few more rows based on a loop of some distinct values in a column. Here's the current SP: SELECT GRP = STAT_CD, CODE = REASN_CD FROM dbo.STATUS_TABLE WITH (NOLOCK) Order by STAT_CD, SRT_ORDR For each distinct STAT_CD, I'd also like to insert a REASN_CD of "--" here in the SP. However I'd like to do it before the order by so I can give them negative sort orders so they come in at the top of the list. I'm getting tripped up on how to implement this. Does anyone know how to do this for each unique STAT_CD?

    Read the article

  • SQL server partitioning

    - by durilai
    I have a table that has millions of records and we are looking at implementing table partitioning. Looking at it we have a foreign key "GroupID" that we would like to partition on. Is this possible? The Group will have more entries added to it, so as new GroupID's are added can the partition's be made dynamically?

    Read the article

  • Oracle SQL outer join query puzzle

    - by user1651446
    So I am dumb and I have this: select whatever from bank_accs b1, bank_accs b2, table3 t3 where t3.bank_acc_id = t1.bank_acc_id and b2.bank_acc_number = b1.bank_acc_number and b2.currency_code(+) = t3.buy_currency and trunc(sysdate) between nvl(b2.start_date, trunc(sysdate)) and nvl(b2.end_date, trunc(sysdate)); My problem is with the date (actuality) check on b2. Now, I need to return a row for each t3xb1 (t3 = ~10 tables joined, of course), even if there are ONLY INVALID records (date-wise) in b2. How do I outer-join this bit properly? Can't use ANSI joins, must do in a single flat query. Thanks.

    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 | 418 419 420 421 422 423 424 425 426 427 428 429  | Next Page >