Hi, My SQL is a bit bad. I got a query that when I run it I return for example 10 rows but there are 15 in my where clause, how do I identify those 5 that I can't find? Off course I can dump it in MS Excel but how do I use SQL? Its a simple query: select * from customers where username in ("21051", "21052"... Nothing else in the whe...
Hi Based on the following table ID Effort Name ------------------------- 1 1 A 2 1 A 3 8 A 4 10 B 5 4 B 6 1 B 7 10 C 8 3 C 9 30 C I want to check if the total effort against a name is...
suppose I have tables Employee, Locations. and I have a view viewEmpLocation which is made by joining Employee and Locations. Now If I update the view, will data in original table get updated. ...
I have a table that stores my Customer hierarchy with a nested set (due to the specific design of the application, I wasn't able to leverage just a Customer/Parent Customer mapping table). To simplify maintenance of this table, I've built a couple of stored procedures to handle moving nodes around and creating new nodes, but it's signif...
I have a table of names and addresses, which includes a postcode column. I want to strip the spaces from the postcodes and select any that match a particular pattern. I'm trying this (simplified a bit) in T-SQL on SQL Server 2005: SELECT Replace(Postcode, ' ', '') AS P FROM Contacts WHERE P LIKE 'NW101%' But I get the following error;...
t-sql, sql server 2008 My application must check connection status to database every 5 seconds. I did it as code below: static bool Check() { using (SqlConnection conn = new SqlConnection("Server=WS-01\\ex1; User id=Admin; pwd=123; database=database")) { try { conn.Open();...
Hello all, I am just reading this article and I came across this: Filter: Remove any functions in the WHERE clause, don't include views in your Transact-SQL code, may need additional indexes. If I do not use views, what are the alternatives? I mean, in my situation, I want to select some data from a table and then use a few ...
I have a varchar(1000) column declared as field that contains all numbers, as shown below. And I want to execute the following script. I need this to work please Declare @PostalCode varchar(1000)=0 set @PostalCode ='7005036,7004168,7002314,7001188,6998955' Select hl.* From CountryLocation cl INNER JOIN refPostalCodes pc ON pc.Pos...
I'm working on creating a SQL query that will pull records from a table based on the value of two aggregate functions. These aggregate functions are pulling data from the same table, but with different filter conditions. The problem that I run into is that the results of the SUMs are much larger than if I only include one SUM function....
Hi based on the following table Name --------- A A A B B C C C I want to add another column to this table called 'OnGoing' and the values should alternate for each group of names. There are only two values 'X' and 'Y'. So the table will look like Name OnGoing ---------------- A X A X A X B Y B ...
Is there an IDENTITY like column type that generates alphanumeric values? Like 023904F? ...
Hi i want to display join result of two tables but don't want to show matching column separately. I want it one instead of two. So please tell me what query should i use for this. I am using SQL Server 2008 and my query is like: select * from Customer_Order, optRelation where Customer_Order.orderNumber = optRelation.orderNumber AND...
I am wondering if there is a way to change the schema that I am working in while inside Management Studio. For instance I may have a default schema of dbo. But there are times I may want to query objects in say the accounting schema. It would be nice if I could issue a command and make it so I no longer must include the accounting before...
Have an image field and want to insert into this from a hex string: insert into imageTable(imageField) values(convert(image, 0x3C3F78...)) however when I run select the value is return with an extra 0 as 0x03C3F78... This extra 0 is causing a problem in another application, I dont want it. How to stop the extra 0 being added? The ...
Hi All, I'm trying to work out a query that self join itself on a table using the eventnumber. I've never done a self join before. What i'm trying to query is when a client has started off in a city which is chester to see what city they moved to. But I dont want to be able to see if they started off in another city. I would also like...
How to loop through a select statement results to have a formatted text? for example the select is like: select name from table and we want a variable @names like this: "name1,name2,name3" Database is SQL Server 2005 ...
I'm looking for help to create a query, to solve the following problem: Let's imagine the row: Name StartDate EndDate Paid James 10-10-2010 17-02-2011 860 And heres the schema for the table as requested: payment_details (name VARCHAR(50) NOT NULL, start_date DATETIME NOT NULL, end_d...
I have a table with the following records id name city 1 aaa NY 2 bbb NY 3 ccc LA 4 ddd LA 5 eee NY I want the table with an additional column by comparing the 'city' column. The values in the col4 should have '1' for every unique value in 'city' column and '0' for the repeating values in 'city' column. id ...
HI I have the following code and a massive problem: WITH CALC1 AS ( SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST FROM @[email protected] WHERE OTIT01 <> '' UNION ALL SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST FROM @[email protected] WHERE OTIT02 <> '' UNION ALL SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQC...
Hi folks, I wish to add some type of business logic constraint to a table, but not sure how / where. I have a table with the following fields. ID INTEGER IDENTITY HubId INTEGER CategoryId INTEGER IsFeatured BIT Foo NVARCHAR(200) etc. So what i wish is that you can only have one featured thingy, per hubId + categoryId. eg. 1, 1, 1...