Search Results

Search found 28985 results on 1160 pages for 'sql training'.

Page 338/1160 | < Previous Page | 334 335 336 337 338 339 340 341 342 343 344 345  | Next Page >

• SQL query for getting count on same table using left outer join

  - by Sasi

  Hi all, I have a table from which i need to get the count grouped on two columns. the table has two columns one datetime column and another one is success value(-1,1,0) What i am looking for is something like this... count of success value for each month month----success-----count 11------- -1 ------- 50 11------- 1 --------- 50 11------- 0 ------- 50 12------- -1 ------- 50 12------- 1 ------- 50 12------- 0 ------- 50 if there is no success value for a month then the count should be null or zero. I have tried with left outer join as well but of no use it gives the count incorrectly. Thanks in advance Sasi

  Read the article

• SQL Group By equivalent

  - by MikeB

  Pretend I have a cupcake_rating table: id | cupcake | delicious_rating -------------------------------------------- 1 | Strawberry | Super Delicious 2 | Strawberry | Mouth Heaven 3 | Blueberry | Godly 4 | Blueberry | Super Delicious I want to find all the cupcakes that have a 'Super Delicious' AND 'Mouth Heaven' rating. I feel like this is easily achievable using a group by clause and maybe a having. I was thinking: select distinct(cupcake) from cupcake_rating group by cupcake having delicious_rating in ('Super Delicious', 'Mouth Heaven') I know I can't have two separate AND statements. I was able to achieve my goal using: select distinct(cupcake) from cupcake_rating where cupcake in ( select cupcake from cupcake_rating where delicious_rating = 'Super Delicious' ) and cupcake in ( select cupcake from cupcake_rating where delicious_rating = 'Mouth Heaven' ) This will not be satisfactory because once I add a third type of rating I am looking for, the query will take hours (there are a lot of cupcake ratings).

  Read the article

• Problem with Full text Searching

  - by devendra

  I am searching in resumes weather the word is exist or not i am using the below query Case1: select top(10) c_resume_text from sntbl_candidates where contains(c_resume_text,'"a/dm"') in the above example only it is not working properly .It showing resumes even though there is no text like that. In Messages i am getting the following message. Informational: The full-text search condition contained noise word(s). if i try with Case 2: select top(10) c_resume_text from sntbl_candidates where contains(c_resume_text,'"a/d') i am getting proper results in case 2 can any one suggest me what to do. Thanks

  Read the article

• LINQ to SQL: On load processing of lazy loaded associations

  - by Matt Holmes

  If I have an object that lazy loads an association with very large objects, is there a way I can do processing at the time the lazy load occurs? I thought I could use AssociateWith or LoadWith from DataLoadOptions, but there are very, very specific restrictions on what you can do in those. Basically I need to be notified when an EntitySet< decides it's time to load the associated object, so I can catch that event and do some processing on the loaded object. I don't want to simply walk through the EntitySet when I load the parent object, because that will force all the lazy loaded items to load (defeating the purpose of lazy loading entirely).

  Read the article

• Using AVG() in Oracle SQL

  - by Viet Anh

  I have a table named Student as followed: CREATE TABLE "STUDENT" ( "ID" NUMBER(*,0), "NAME" VARCHAR2(20), "AGE" NUMBER(*,0), "CITY" VARCHAR2(20), PRIMARY KEY ("ID") ENABLE ) I am trying to get all the records of the students having a larger age than the average age. This is what I tried: SELECT * FROM student WHERE age > AVG(age) and SELECT * FROM student HAVING age > AVG(age) Both ways did not work!

  Read the article

• Passing filtering functions to Where() in LINQ-to-SQL

  - by Daniel

  I'm trying to write a set of filtering functions that can be chained together to progressively filter a data set. What's tricky about this is that I want to be able to define the filters in a different context from that in which they'll be used. I've gotten as far as being able to pass a very basic function to the Where() clause in a LINQ statement: filters file: Func<item, bool> returnTrue = (i) => true; repository file: public IQueryable<item> getItems() { return DataContext.Items.Where(returnTrue); } This works. However, as soon as I try to use more complicated logic, the trouble begins: filters file: Func<item, bool> isAssignedToUser = (i) => i.assignedUserId == userId; repository file: public IQueryable<item> getItemsAssignedToUser(int userId) { return DataContext.Items.Where(isAssignedToUser); } This won't even build because userId isn't in the same scope as isAssignedToUser(). I've also tried declaring a function that takes the userId as a parameter: Func<item, int, bool> isAssignedToUser = (i, userId) => i.assignedUserId == userId; The problem with this is that it doesn't fit the function signature that Where() is expecting: Func<item, bool> There must be a way to do this, but I'm at a loss for how. I don't feel like I'm explaining this very well, but hopefully you get the gist. Thanks, Daniel

  Read the article

• SQL Reset Identity ID in already populated table

  - by rockinthesixstring

  hey all. I have a table in my DB that has about a thousand records in it. I would like to reset the identity column so that all of the ID's are sequential again. I was looking at this but I'm ASSuming that it only works on an empty table Current Table ID | Name 1 Joe 2 Phil 5 Jan 88 Rob Desired Table ID | Name 1 Joe 2 Phil 3 Jan 4 Rob Thanks in advance

  Read the article

• Table/column names enclosed in square brackets, even though their names consist of legal characters

  - by AspOnMyNet

  Square brackets allow you to use names for columns or aliases that contain characters not permitted for column names or aliases. a) I’ve noticed that lots of times table and column names are enclosed inside square brackets, even though their names consist of perfectly legal characters. Why is that? b) As far as I know, square brackets enclosing object’s name aren’t actually a part of that name. Thus, if we create a table named [A]: CREATE TABLE [A] ( … ) we can later reference it without using brackets: SELECT * FROM A But why isn’t the same true when I try to reference a column KEY from a table returned by CONTAINSTABLE function? Namely, if I omit the brackets enclosing column name, I get an error: SELECT ct.KEY FROM CONTAINSTABLE(fullText,*,'some_string') as ct thanx

  Read the article

• MSSQL 2000 Stored Procedure to Split Shift Times

  - by JClaspill

  I am being asked to alter a system to include the ability to have pay differentials based on hours worked. The old method included a stored procedure (MSSQL2000 db) that did the basics, but simply knows the start and end of every shift. So, this is the information I start with: EMPLOYEE | TYPE | HOURS | INSTAMP | OUTSTAMP Dave | Hourly | 8.643055 | 2011-01-08 07:57:35.557 | 2011-01-08 16:36:10.120 And I need to turn that into something like: EMPLOYEE | TYPE | HOURS | INSTAMP | OUTSTAMP Dave | Hourly | 4.00 | 2011-01-08 08:00:00.000 | 2011-01-08 12:00:00.000 Dave | ShiftDiff1 | 4.50 | 2011-01-08 12:00:00.000 | 2011-01-08 16:30:00.000 The ShiftDiff's range from hours to certain days, to a combo of both. Should I try to make the SQL2000 SP do this or pass the info back to my ASP.NET(C#) app and let it handle it, then send back?

  Read the article

• Get records using left outer join

  - by Devendra Gohil

  I have two tables as given below Table A Table B Table C ============= ============== ========= Id Name Id AId CId Id Name 1 A 1 1 1 1 x 2 B 2 1 1 2 y 3 C 3 2 1 3 z 4 D 4 2 3 4 w 5 E 5 3 2 5 v Now I want all the records of Table A with matching Id column CId from Table B where CId = 1. So the output should be like below : Id Name CId 1 A 1 2 B 1 3 C 1 4 D Null 5 E Null Can anyone help me please?

  Read the article

• Convert SQL with Inner AND Outer Join to L2S

  - by Refracted Paladin

  I need to convert the below Sproc to a Linq query. At the very bottom is what I have so far. For reference the fields behind the "splat"(not my sproc) are ImmunizationID int, HAReviewID int, ImmunizationMaintID int, ImmunizationOther varchar(50), ImmunizationDate smalldatetime, ImmunizationReasonID int The first two are PK and FK, respectively. The other two ints are linke to the Maint Table where there description is stored. That is what I am stuck on, the INNER JOIN AND the LEFT OUTER JOIN Thanks, SELECT tblHAReviewImmunizations.*, tblMaintItem.ItemDescription, tblMaintItem2.ItemDescription as Reason FROM dbo.tblHAReviewImmunizations INNER JOIN dbo.tblMaintItem ON dbo.tblHAReviewImmunizations.ImmunizationMaintID = dbo.tblMaintItem.ItemID LEFT OUTER JOIN dbo.tblMaintItem as tblMaintItem2 ON dbo.tblHAReviewImmunizations.ImmunizationReasonID = tblMaintItem2.ItemID WHERE HAReviewID = @haReviewID My attempt so far -- public static DataTable GetImmunizations(int haReviewID) { using (var context = McpDataContext.Create()) { var currentImmunizations = from haReviewImmunization in context.tblHAReviewImmunizations where haReviewImmunization.HAReviewID == haReviewID join maintItem in context.tblMaintItems on haReviewImmunization.ImmunizationReasonID equals maintItem.ItemID into g from maintItem in g.DefaultIfEmpty() let Immunization = GetImmunizationNameByID( haReviewImmunization.ImmunizationMaintID) select new { haReviewImmunization.ImmunizationDate, haReviewImmunization.ImmunizationOther, Immunization, Reason = maintItem == null ? " " : maintItem.ItemDescription }; return currentImmunizations.CopyLinqToDataTable(); } } private static string GetImmunizationNameByID(int? immunizationID) { using (var context = McpDataContext.Create()) { var domainName = from maintItem in context.tblMaintItems where maintItem.ItemID == immunizationID select maintItem.ItemDescription; return domainName.SingleOrDefault(); } }

  Read the article

• Get all related products based on their full-text search relationship

  - by MikeJ

  I have a Product table with the fields Id, Title, Description, Keywords (just comma separated list of keywords). Table is full-text indexed. When I view one product, I do a query and search the full-text catalog for any related products based on the Keywords field. select * from Products where Contains(Products.*, @keywordsFromOneProduct) Works like a charm. Now I would like to list all products and all their related products in a big list and I want to avoid calling this method for each item. Any ideas how could I do it? I was thinking about a job that would go through products one by one and build a one-many mapping table (fields ProductId, RelatedProductId), but I wonder is there a better way?

  Read the article

• How to apply Containstable 4 two join table?

  - by jaykanth

  product table pid modelnumber 1 a 2 b 3 c ProductTransation pid name description... 1 ball ball 2 bat cricket bat i create fullText for Modelnumber in product table. " for name & Description in productTrasaction table. Now i want to join this table if i search through modelnumber or name result should be pid name modelnumber 1 ball a

  Read the article

• Separating date ranges into separate segments in SQL

  - by Richard

  I have a table containing 2 date fields and an identifier (id, fromdate and todate) These dates overlap in any and every possible way. I need to produce a list of segments each with a start and end date describing the separate segments in that list. For example: id, FromDate ToDate 1, 1944-12-11, 1944-12-31 2, 1945-01-01, 1945-12-31 3, 1945-01-01, 1945-06-30 4, 1945-12-31, 1946-05-01 5, 1944-12-17, 1946-03-30 Should produce all the segments of all the overlaps: 1, 1944-12-11, 1944-12-16 1, 1944-12-17, 1944-12-31 5, 1944-12-17, 1944-12-31 2, 1945-01-01, 1945-06-30 3, 1945-01-01, 1945-06-30 5, 1945-01-01, 1945-06-30 2, 1945-07-01, 1945-12-09 5, 1945-07-01, 1945-12-09 2, 1945-12-10, 1945-12-31 4, 1945-12-10, 1945-12-31 5, 1945-12-10, 1945-12-31 4, 1946-01-01, 1946-03-30 5, 1946-01-01, 1946-03-30 4, 1946-04-01, 1946-05-01 Or perhaps a diagram might help INPUT 1 <----> 2 <-----------> 3 <-----> 4 <----------> 5 <-----------------> OUTPUT 1 <-> 1 <-> 5 <-> 2 <-----> 3 <-----> 5 <-----> 2 <-> 5 <-> 2 <-> 4 <-> 5 <-> 4 <-> 5 <-> 4 <----> Please help

  Read the article

• Query with many CASE statements - optimization

  - by Nemanja Vujacic

  Hi guys, I have one very dirty query that per sure can be optimized because there are so many CASE statements in it! SELECT (CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id WHEN 2 THEN fw.fw_id WHEN 3 THEN s.sw_Id WHEN 4 THEN id.ia_id END) as Deal_Id, max(CASE pa.KplusTable_Id WHEN 1 THEN sp.Trans_Id WHEN 2 THEN fw.Trans_Id WHEN 3 THEN s.Trans_Id WHEN 4 THEN id.Trans_Id END) as TransId_CurrentMax INTO #MaxRazlicitOdNull FROM #PotencijalniAktuelni pa LEFT JOIN kplus_sp sp (nolock) on sp.sp_id=pa.Deal_Id AND pa.KplusTable_Id=1 LEFT JOIN kplus_fw fw (nolock) on fw.fw_id=pa.Deal_Id AND pa.KplusTable_Id=2 LEFT JOIN dev_sw s (nolock) on s.sw_Id=pa.Deal_Id AND pa.KplusTable_Id=3 LEFT JOIN kplus_ia id (nolock) on id.ia_id=pa.Deal_Id AND pa.KplusTable_Id=4 WHERE isnull(CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA WHEN 2 THEN fw.BROJ_TIKETA WHEN 3 THEN s.tiket WHEN 4 THEN id.BROJ_TIKETA END, '')<>'' GROUP BY CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id WHEN 2 THEN fw.fw_id WHEN 3 THEN s.sw_Id WHEN 4 THEN id.ia_id END Because I have same condition couple times, do you have idea how to optimize query, make it simpler and better. All suggestions are welcome! TnX in advance! Nemanja

  Read the article

• UNIQUE CONSTRAINT on a column from foreign table in MSSQL2008

  - by bodziec

  Hi, I have two tables: create table [dbo].[Main] ( [ID] [int] identity(1,1) primary key not null, [Sign] [char](1) not null ) create table [dbo].[Names] ( [ID_Main][int] primary key not null, [Name][nvarchar](128) not null, constraint [FK_Main_Users] foreign key ([ID_Main]) references [dbo].[Main]([ID]), constraint [CK_Name] unique ([Name], [Sign]) ) The problem is with the second constraint CK_Name Is there a way to make a constraint target column from a foreign table?

  Read the article

• Why hasn't MSSQL made a WHERE clause mandatory by default?

  - by Josh Einstein

  It seems like a no brainer to me. I've heard countless stories about people forgetting the WHERE clause in an UPDATE or DELETE and trashing an entire table. I know that careless people shouldn't be issuing queries directly and all that... and that there are legitimate cases where you want to affect all rows, but wouldn't it make sense to have an option on by default that requires such queries to be written like: UPDATE MyTable SET MyColumn = 0 WHERE * Or without changing the language, UPDATE MyTable SET MyColumn = 0 WHERE 1 = 1 -- tacky, I know

  Read the article

• Question About DateCreated and DateModified Columns - SQL Server

  - by user311509

  CREATE TABLE Customer ( customerID int identity (500,20) CONSTRAINT . . dateCreated datetime DEFAULT GetDate() NOT NULL, dateModified datetime DEFAULT GetDate() NOT NULL ); When i insert a record, dateCreated and dateModified gets set to default date/time. When i update/modify the record, dateModified and dateCreated remains as is? What should i do? Obviously, i need to dateCreated value to remain as was inserted the first time and dateModified keeps changing when a change/modification occurs in the record fields. In other words, can you please write a sample quick trigger? I don't know much yet...

  Read the article

• Problem with update sql with excel

  - by phenevo

  Hi, I have a problem with this query: Update Provinces Set Provinces.DefaultName=T2.Defaultname from Provinces inner join OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\provinces.xlsx;HDR=YES', 'SELECT Code, Defaultname FROM [Arkusz1$]') T2 On Provinces.Code = t2.Code where Provinces.Code = T2.Code I get error: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". What is a reason of this unpleasent situation ?

  Read the article

• SQL - Multiple join conditions using OR?

  - by Brandi

  I have a query that is using multiple joins. The goal is to say "Out of table A, give me all the customer numbers in which you can match table A's EmailAddress with either email_to or email_from of table B. Ignore nulls, internal emails, etc.". It seems like it would be better to use an or condition in the join than multiple joins since it is the same table. When I try to use AND/OR it does not give the behaviour I expect... AND finishes in a reasonable time, but yields no results (I know that there are matches, so it must be some flaw in my logic) and OR never finishes (I have to kill it). Here is example code to illustrate the question: --my original query SELECT DISTINCT a.CustomerNo FROM A a WITH (NOLOCK) LEFT JOIN B e WITH (NOLOCK) ON a.EmailAddress = e.email_from RIGHT JOIN B f WITH (NOLOCK) ON a.EmailAddress = f.email_to WHERE a.EmailAddress NOT LIKE '%@mydomain.___' AND a.EmailAddress IS NOT NULL AND (e.email_from IS NOT NULL OR f.email_to IS NOT NULL) Here is what I tried, (I am attempting logical equivalence): SELECT DISTINCT a.CustomerNo FROM A a WITH (NOLOCK) LEFT JOIN B e WITH (NOLOCK) ON a.EmailAddress = e.email_from OR a.EmailAddress = e.email_to WHERE a.EmailAddress NOT LIKE '%@mydomain.___' AND a.EmailAddress IS NOT NULL AND (e.email_from IS NOT NULL OR e.email_to IS NOT NULL) So my question is two-fold: Why does having AND in the above query work in a few seconds and OR goes for minutes and never completes? What am I missing to make a logically equivalent statement that has only one join?

  Read the article

• Every 3rd Insert Is Slow On Ms Sql 2008

  - by Chris

  I have a function that writes 3 lines into a empty table like so: INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (1, 8, 1) INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (2, 8, 4) INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (3, 8, 3) For some reason only the third query takes a long time to execute - and with each insert it grows longer. Profiler Image I have tried disabling all constraints on the table - same result. I just can't figure out why the first two would run so fast - and the last one would take so long. Any help would be greatly appreciated. Here is the statistics for a query ran MSSMS: Query: ALTER TABLE [dbo].[yaf_ForumAccess] NOCHECK CONSTRAINT ALL INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (1, 9, 1) INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (2, 9, 4) INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (3, 9, 3) ALTER TABLE [dbo].[yaf_ForumAccess] CHECK CONSTRAINT ALL Stats: Stats

  Read the article

• When should I open and close a connection to SQL Server

  - by Martin

  I have a simple static class with a few methods in it. Each of those methods open a SqlConnection, query the database and close the connection. This way, I am sure that I always close the connection to the database, but on the other hand, I don't like to always open and close connection. Below is an example of what my methods look like. public static void AddSomething(string something) { using (SqlConnection connection = new SqlConnection("...")) { connection.Open(); // ... connection.Close(); } } Considering that the methods are inside a static class, should I have a static member containing a single SqlConnection? How and when should I drop it? What are the best practices?

  Read the article

• Improve Log Exceptions

  - by Jaider

  I am planning to use log4net in a new web project. In my experience, I see how big the log table can get, also I notice that errors or exceptions are repeated. For instance, I just query a log table that have more than 132.000 records, and I using distinct and found that only 2.500 records are unique (~2%), the others (~98%) are just duplicates. so, I came up with this idea to improve logging. Having a couple of new columns: counter and updated_dt, that are updated every time try to insert same record. If want to track the user that cause the exception, need to create a user_log or log_user table, to map N-N relationship. Create this model may made the system slow and inefficient trying to compare all these long text... Here the trick, we should also has a hash column of binary of 16 or 32, that hash the message and the exception, and configure an index on it. We can use HASHBYTES to help us. I am not an expert in DB, but I think that will made the faster way to locate a similar record. And because hashing doesn't guarantee uniqueness, will help to locale those similar record much faster and later compare by message or exception directly to make sure that are unique. This is a theoretical/practical solution, but will it work or bring more complexity? what aspects I am leaving out or what other considerations need to have? the trigger will do the job of insert or update, but is the trigger the best way to do it?

  Read the article

• How do I pivot this in T-SQL?

  - by Matt W

  How do I get this: entityid name stringvalue ----------- -------------------- -------------------- 1 ShortDescription Coal 1 LongDescription BlackCoal 1 ShortDescription Gold 1 LongDescription WhiteGold 1 ShortDescription Steel 1 LongDescription StainlessSteel To become this: entityid ShortDescription LongDescription ----------- -------------------- -------------------- 1 Coal BlackCoal 1 Gold WhiteGold 1 Steel StainlessSteel Many thanks everyone, Matt.

  Read the article

• Backing up an online database

  - by Veejay

  I havea 70MB db of my website which is hosted with a provider. I am able to access my db using SSMS 2008 remotely. On a running website, which is the best way I can back up the db locally on machine Thanks

  Read the article

< Previous Page | 334 335 336 337 338 339 340 341 342 343 344 345  | Next Page >