Eric, So, if your table may has rows like below, what do you want as a result? Col1, Col2 1, 15 1, 23 If you want only one instance of Col1 = 1, then you have to choose what Col2 will be, MIN, MAX, SUM, AVG, and so forth. By recasting as a group by instead of a distinct you co...more >>

Thank you in advance for any and all assistance. I am trying to do a comparison of 2 tables and I am expecting the result to be what ever is not in one of the tables. The table I need the result from has 2 columns of data that I want returned based on col1 being distinct because I know it ha...more >>

Ryan, One easy way is: SELECT 'FEDERAL', Federal FROM MyTable UNION ALL SELECT 'FICA1', FICA1 FROM MyTable UNION ALL SELECT 'FICA2', FICA2 FROM MyTable If you are on SQL Server 2005, you might also look at the UNPIVOT operator. RLF "Ryan Mcbee" <RyanMcbee@discussions.microsoft....more >>

I have a table that has the following three fields and values that I am trying to build a query on; Column name-FEDERAL, FICA1, FICA2 Values- 1 , 100 , 200 2 , 300 , 400 How can I get a query to look like this?; Column- TaxCode, ...more >>

On Fri, 26 Oct 2007 09:40:00 -0700, Matt M wrote: (snip) >Probably not the most efficient way to do it but it got the job done and my >numbers came out exact. Hi Matt, This way is probably more efficient: SELECT Main.SOPTYPE, Main.SOPNUMBE, Main.ORIGNUMB, Main.DOCID, ...more >>

I ended up figuring it out. Thanks for all your help. See my last reply to Hugo for my solution and let me know if you have any pointers. "Russell Fields" wrote: > Matt, > > I am having a little trouble following your narrative. (Sorry.) Sample > tables and sample data often help with t...more >>

I couldnt figure out how to get your query suggestion to work with this but after reading your blog I figured out my problem. I was comaparing my results in my Case statement with Null. So instead of using '=' I used EXISTS and that did the trick. My resulting query came out to be: SELECT ...more >>

On Wed, 24 Oct 2007 08:59:01 -0700, Tsheth wrote: >Hi, >I have a text column in a table and data stored in it contains formatted >text (with carriage returns, tabs, and spaces). When I perform a select on >that column, data returned is non formatted. How do get the formatted data? Hi Ts...more >>

On Thu, 25 Oct 2007 07:30:01 -0700, Matt M wrote: (snip) >Does this help? Your sample query you showed me looks promising but it may >take me a little bit to wrap my brain around it. Hi Matt, The main reason that this query is ntroublesome to come up with, is that the design is bad. I'm...more >>

Yes I agree that the design is very hard to work with but unfortunately I am stuck with it. I havent had a chance to look into your suggestion yet but did you see my other post. I have a query that is working halfway (See my other post in this thread for explanation). Here is the query: SE...more >>

Ok this may help. The query below works if the Orignal Order is located in Table1 (even though my CASE statement seems backwards to me). If it was moved to table2 then I just get a Null value. But I think this might give you a better idea of what I need to do. I left the original table name...more >>

I know, it took me a while to understand what they wanted to do as well. Basically, the database is a Great Plains database. Not sure if you are familiar with GP at all? Anyway, I'm going to try my best to show you some sample data. Table2 SOPTYPE SOPNUMBE ORIGNUMB DOCID ...more >>

Matt, I am having a little trouble following your narrative. (Sorry.) Sample tables and sample data often help with this problem. If I understand correctly, for anything you are querying there is always a row in Table2 and sometimes a row in Table1. Only if Table2 does not have a value ...more >>

Matt, I believe that you are looking for a UNION, such as: SELECT OriginalOrderNum, Column1, Column2, Column3 FROM Table1 WHERE ... UNION ALL SELECT OriginalOrderNum, Column1, Column2, Column3 FROM Table2 WHERE OriginalOrderNum NOT IN (SELECT OriginalOrderNum FROM Table1) AND ....more >>

Sounds promising but I'm not sure if it will work. The Invoices I am looking up are in Table2. What I need to do is pull all invoices for let's say the month of September, and look up the corresponding Original Order with each Invoices Original Order Number in Table2 first. If it is not in ...more >>

Hi, I have a text column in a table and data stored in it contains formatted text (with carriage returns, tabs, and spaces). When I perform a select on that column, data returned is non formatted. How do get the formatted data?...more >>

Note: I am using this to create a report with SRS I need to pull some data that could possibly be sitting in one of two tables. Table1 is the "Current" table that holds Orders that have not shipped yet. Once all of items on the Order have shipped the Order moves to Table2 and an invoice is ...more >>

Russell, Thanks, this helps out a lot. I have been trying to figure this one out for a while. Ryan "Russell Fields" wrote: > Ryan, > > To get the quarter, assuming January to March is the first quarter" > > select (datepart(Month,MyDateColumn) + 2) / 3 > > This depends on how i...more >>

Ryan, To get the quarter, assuming January to March is the first quarter" select (datepart(Month,MyDateColumn) + 2) / 3 This depends on how integer match works. Or you can create a calendar table (Google for that) to record this kind of information for every date. RLF "Ryan Mcbee...more >>

I am trying to build a query that will return what quarter the data is in. How would I begin to build this logic? In my table, all I have is a single date of the transactions. My date column looks like this; "2014-02-01 00:00:00.000". Thanks in advance, Ryan...more >>

Mike, The index stores values in separate columns and is ordered by the columns in the order defined. That is, in your case 1 accountNo 2 FirstName 3 last name 4 dateOfTrans There is additional structural information for the index, of course. This index is ordered to work for que...more >>

thanks alot Russel...i will read the books online... thanks again Mike "Russell Fields" <russellfields@nomail.com> wrote in message news:upGH$PoEIHA.1212@TK2MSFTNGP05.phx.gbl... > Mike, > > The index stores values in separate columns and is ordered by the columns > in the order defined. ...more >>

Im trying to figure out the best way and/or fastest way to Match some data comming in....example below the below is all the incomming information i recieve to match to a row in our system, we do send out a TransactionId but reports comming back in do not reflect this id.. so i have to match th...more >>

On Wed, 17 Oct 2007 21:14:13 GMT, Sasikumar wrote: >Hi, > >I have a Table with rows as below > >A 1 10 >B 1 15 >A 2 3 >A 1 5 >A 3 10 > >Since the 1st & 4th row has values of A & 1 in first & second column, I wanted to aggregate the values in third colum. I wanted the result to be > ...more >>

Hi, I have a Table with rows as below A 1 10 B 1 15 A 2 3 A 1 5 A 3 10 Since the 1st & 4th row has values of A & 1 in first & second column, I wanted to aggregate the values in third colum. I wanted the result to be B 1 15 A 2 3 A 1 15 A 3 10 How can this be done? From ...more >>

On Mon, 15 Oct 2007 07:29:05 -0700, Ryan Mcbee wrote: >I am writing a query in which I want to select a field and not have the >trailing blank spaces. Here is my query: select employid, lastname + ',' + >frstname as name from upr00100 > >The results look like this "West ,P...more >>

I have been using this computer program for a couple weeks now and i am very pleased with the results so far. its a software fatloss program, if your looking for a diet/weightloss plan i reccomend you check this place out first: http://fatloss9.50webs.com ...more >>

Hugo, Of course, your answer makes more logical sense than mine. - RLF "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message news:t7b7h3t6bvmmt49f6qr503c9h70744foa3@4ax.com... > On Mon, 15 Oct 2007 07:29:05 -0700, Ryan Mcbee wrote: > >>I am writing a query in which I want ...more >>

Ryan SELECT employeeid, REPLACE(lastname+','+firstname,SPACE(1),'') as name FROM upr00100 RLF "Ryan Mcbee" <RyanMcbee@discussions.microsoft.com> wrote in message news:2A995187-4FD1-4956-9CF1-6160765C4A4D@microsoft.com... >I am writing a query in which I want to select a field and not ...more >>

I am writing a query in which I want to select a field and not have the trailing blank spaces. Here is my query: select employid, lastname + ',' + frstname as name from upr00100 The results look like this "West ,Paul" or "Mcbee ,Ryan" How can I get results...more >>