Query Postcode Issue (IF Statement)?

Feb 25, 2008

Hi Everyone,

Basically i've got a postcode query problem, i have a column with postcodes like:

abc 123
abcd 123
ab 123

What im trying to do is query only the first set on digits without the 123.

So my output should be:

abc
abcd
ab

Ive used the following technique:

select TRIM(substring(postcode,1,if(instr(postcode," ")=0,length(postcode),instr(postcode," ")))) from dbTest;

This works perfect in MySQL but not in sql enterprise, as it doesnt like the if syntax as well as the instr();

Any solutions please?? any suggestions would be helpful!

Thanks

View 6 Replies


ADVERTISEMENT

Using Postcode As Primarykey

Aug 10, 2007

Hello

I've taken over a database where for many tables the postcode field (equivilent to the US Zip Code) is the obvious primary key. [Mapping postcodes to distict or various types of area for statistical analysis]. However the people who set up the database have continually used an auto generated number for the primary key.

There are never any duplicate postcodes in the various tables, so my question is what is the advantage of either using or not using the postcodes as primary keys.

View 2 Replies View Related

Substring Of A Postcode

Nov 13, 2007



Hello,

Does anyone have some code that can do the following:

Postcode N12 5FF
Postcode NG4 5FF

I need to get the postcode N12 5FF based on the 'N' bt leave the NG4 5FF postcode behind. So obviously using LEFT will not work, does any have any code that will strip of the first alpha characters?

Thank you.

View 7 Replies View Related

Inserting Space In A Postcode

Jan 30, 2014

We have many postcodes in our database that are UK ones. Some cutomers enter then as one string. Example BA124XT

In the SQL table we have two columns PostCode and Country. Is it possible to write a SQL script that will insert a space three characters from the right of the postcode where the country is UK?

So before it would be BA124XT and after BA12 4XT for them all?

View 5 Replies View Related

Postcode Radius Searching

Jul 20, 2005

Hi - I know this isn't really a specific SQL Server question but Ican't think of a better forum.I want to implement a more sophisticated UK postcode search on a site- "LIKE 'NW%'" etc, is not bringing back good enough results.A feature that would allow me to ask: "give me all outward postcodesin a 30 mile radius of NW10" would be ideal.Has anyone had to do this before? - any advice on how muchappreciated. Some of the postcode products available don't reallyseem to offer this feature.NH

View 2 Replies View Related

Australian Postcode Db With Longitude/latitude

Jul 23, 2005

not sure exactly where I should ask this question, but im looking fora database of Australian Postcodes that included thelongitude/latitude.Does anyone know of such a thing? Preferably free...fingers crossed.many thanksjack

View 1 Replies View Related

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

The If ... Else Statement In SQL Query.. Please Help To Correct Query.. Thank You

Jan 3, 2008

  1 SELECT distinct A.EAIndex,
2 A.ChainCode AS ChainCode,
3 A.JobDate,
4 C.BillerName,
5 B.BusinessCode,
6 D.ChainName,
7 A.BillBegDate,
8 A.BillEndDate,
9 A.PostDate,
10 A.BillCount,
11 A.ChAmount,
12 A.PostAmount,
13 A.ProChargeFee,
14 A.BalanceStatus AS BalanceStatus,
15 B.BillerInfoCode AS BillerInfoCode ,
16 CONVERT(varchar(10), A.BalanceDate, 112)BalanceDate,
17 A.AdjustDate,
18 C.BillerCode ,
19 (a.ChAmount- a.ProChargeFee) AS Fee,
20 E.EABillerCode, E.RelAdjustDate
21 FROM ZT_EAccount A
22 Inner Join ZT_BillerInfo B On Right('00000'+Rtrim(Ltrim(A.BillerCode)),5) = Right('00000'+Rtrim(Ltrim(B.BillerInfoCode)),5)
23 Inner Join ZT_Biller C ON B.ParentCode = C.BillerCode
24 Inner Join ZT_Chain D On A.ChainCode = D.ChainCode
25 Inner Join ZT_EAccountAdjust E ON a.EAIndex=E.EAIndex and a.BillerCode=E.BusCode
26 Where A.JobDate BETWEEN '20071001' AND '20071005' AND C.CompanyCode='533'
 In line 13 I want to add a Query to make sure if ZT_BillerInfo.Rmflag = 1, if it's =1 then ProChargeFee =0if ZT_BillerInfo.RmFlag =0 , then ProChargeFee = a.ProChargeFee.I add this line ă€?Case B.RmFlag = '1' then 0 else A.ProChargeFee 】instead of Line 13 (A.ProChargeFee)but I execute Sql I got error message on the line I have jsut modified..can you please help me to know why and how to correct it? thank you very much

View 4 Replies View Related

How To Add A If..else Statement To SQL Query?

Oct 15, 2007

  I want to Add a if .. else statement to make sure if the Data is exist in Table                       if (exist ) { do a update query} else{ do a Insert Query}
 I Know how to update and how to insert but how to check if the data is exist in Table and how to add a if ..else to query?
 thank you very much
 

View 9 Replies View Related

If Statement In Query

Jan 26, 2006

In access you can use an imediate if statement in the query. can you do the same thing in SQL server?

View 3 Replies View Related

Run An If Statement Within A Query

Aug 27, 2004

Hi all;

I have searched the net for the following but am not too sure how it's done. I am trying to create an if statement within a sql query where based on the results it will stop or continue something like:

count(*) from test_table
where test_column = 0
go
if result = 0 then
ALTER TABLE test_table DROP COLUMN test_column
go
select into test_table_2
from test_table
go

and so on and so forth

any will help will be apprecated

thanks

View 3 Replies View Related

Sub Query As A Value For An Insert Statement

Oct 10, 2007

Hello! I have this stored procedure:ALTER PROCEDURE [dbo].[spAddToFavorites]
 @Userid uniqueidentifier OUTPUT,
@KeyID uniqueidentifier
 
ASINSERT INTO Associations (Userid,BusinessID) values (@Userid, (SELECT Userid from UserProfile WHERE KeyID= @KeyID))
 
RETURN
But it says that I can't do it like that. I need to select that userid based on the keyid I am gonna pass in. Any help on this appreciated.

View 4 Replies View Related

Need Help With Sql Query Statement In ASP Form

Jan 28, 2008

I have an ASP form that takes the information that is entered on the form and inserts it into a Microsoft Access database.  It works great.  In addition to the fields from the form, I also want to add the current date into the InitDate field.  How would I modify the SQL query below to insert the current date into the COS database? conn.execute SqlQry        Sql =         "INSERT INTO COS ([Name of School], [Director of COS], [Address], [City], [State], [zip], [PhoneNumber], "        Sql = Sql &    "[general_notes], [type], [DEPT], ) "  
     Sql = Sql &    "VALUES ('" & m_CompanySchoolName & "',
'" & m_FullName & "', '" & m_StreetAddress & "', '"
& m_City        Sql = Sql &    "', '" & m_State &
"', '" & m_Zip & "', '" & m_TelephoneNumber & "', '"
& m_Message & "', 'COSMETOLOGY', '"        Sql = Sql  & m_Department &    "', '" & m_EmailAddress & "')"                response.write Sql        response.end        conn.execute Sql       

View 14 Replies View Related

Need Help With Query Statement 'IN' Clause.

Jun 29, 2005

I have a column in the database that stored moduleId that are seperated by '|' (pipes).  For Example: '527|343|454'

I need to add a where clause to a query that pulls the data based on a
ModuleId. For Example: select * from table where 527 in [column above]

Does anyone know how I can do this in a query?  Normally I could
use an IN statement, ex: select * from table where 527 in (527,343,454)

How can I get the column in that format?

Thanks for the help in advance,

KM

View 2 Replies View Related

Update Query Statement ?

Jul 16, 2004

Hi,

The below query has some problem :

update (table1 inner join table2 on table1.sno = table2.sno) inner join table3 on table2.sno1 = table3.sno set table1.sno1 = 2

SQL Server 2000, throws a syntax error :
'Incorrect syntax near keyword '(' ' on executing the above statement.

Please advice 'what is the error in the above statement ? '

Thanks,
Sam

View 2 Replies View Related

How To Get A Sql Statement In Query Analyzer

Jun 1, 2004

I am trying to troubleshoot some high activity on one of my SQL Servers, and when I run sp_who2 'active' I get the current activity, and spid number. I check under Process Info for the spid, and wHEN i double click the spid, I get part of the sql statement run by that trouble spid. Is there a way to get the whole sql statement in Query Analyzer without running SQL Profiler. Thanks in advance for any help.

View 3 Replies View Related

Print Statement For Query

Nov 18, 2014

How to write query with PRINT Statement for below query.

WITH CTE1 AS (SELECT * FROM table1),
CTE2 AS (SELECT * FROM table2),
CTE3 AS (SELECT * FROM table3)

In between to use PRINT statement seeing where my current query is.

So once we process "SEELCT * FROM table1" and gets completed I want to print as "Table table1 completed".

View 1 Replies View Related

If Statement Inside Sql Query??

Jun 7, 2008

Hello,

I have an if statement for one of my columns in my query.
I want to write the if statement as a part of my select statement.
How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.

I could use the "decode function" but

I have something like this:
if column1 = '1' or column2 = '2' then select "Yes" etc..

I tried doing select decode (column1 or column2, , , ) but it doesn't work.
Any ideas?

View 1 Replies View Related

If Statement Inside Sql Query??

Jun 7, 2008

Hello,

I have an if statement for one of my columns in my query.
I want to write the if statement as a part of my select statement.
How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.

I could use the "decode function" but

I have something like this:
if column1 = '1' or column2 = '2' then select "Yes" etc..

I tried doing select decode (column1 or column2, , , ) but it doesn't work.
Any ideas?

View 1 Replies View Related

Select Statement Query

Oct 4, 2006

Hi,

I have a table called notes, with thousands of rows of "notes" entered by customer services agents. Each row has an account number, date and username columns.

An account can have many notes on thetable.

How can I select the last two notes on the table left for each account? Trying to use select top 2, but of course it's only giving me the top 2 notes for ALL accounts.

Please help.

Thanks very much.

View 2 Replies View Related

Determining If SQL Statement Is A Query

Jul 27, 2007

Hi,

I have a C# server application which clients can send arbitrary SQL statements to. These can be absolutely anything - creating tables/views, selecting from tables/views, inserts, updates, deletes, you name it.

There are two return parameters from the server method which executes the SQL - a results set containing the data, and a count of the rows that were updated - (either one or the other should be populated depending on the type of command sent to it). To deal with this, what I planned on doing was (pseudocode follows..):

try
{
reader = OpenReaderCursor(statement)
}
catch
{
updatedCount = ExecuteNonQueryCommand(statement)
}

Unfortunately this doesn't really work, as OpenReaderCursor is able to execute Non Queries (eg. UPDATE/INSERT/DELETEs etc) but doesn't give me a row count, and trying the other way round, ExecuteNonQueryCommand is happy to execute SELECT statements, but I can't then of course return a results set as I don't have access to it.

My question then, if you will excuse the waffle above, is "Is there a simple way of determining if a string containing an SQL statement is a query?" - or will I have to come up with some way of dealing with this in my application code?

Please don't slate the design (ideally I would have two methods on the server, one for queries which returns results and the other for nonqueries which returns an updatecount) but there's nothing I can do, this is how it must be done (the interface was defined long long ago)

Thanks for your time, all ideas appreciated

Jim

View 1 Replies View Related

Client Statement Query

Feb 27, 2008

Hi all - having a prob trying to figure this one out! It's an accounts aplication and I need to produce a monthly statement for clients which will be mailed automatically at the end of the month...

I have 3 tables (simplified below):

Invoice
invoiceid int
invoicedate datetime
clientid int
subtotal decimal(19,2)
paid int

Credit
creditid int
creditdate datetime
clientid int
subtotal decimal(19,2)
paid int

Payments
paymentid int
paymentdate datetime
amount decimal(19,2)
allocated int

The report I need to generate has the client's details at the top and then a list of unpaid invoices, credits and unallocated payments - all sorted by date - with the following columns (sample data supplied)

TYPE|DATE|OUR REF|CREDIT|DEBIT
Invoice | 01-mar-2008 | Inv01 | | £100.00
Credit | 02-mar-2008 | Cre23 | £50 |
Payment | 03-mar-2008 | P04 | £40 |

Would be really grateful for any help!

Thanks in advance,

Stephen.

View 3 Replies View Related

Using Query In CASE Statement

Mar 4, 2008

Why couldn't they make SQL syntax error mistakes a little less vague.

Anyway, I was wondering, is it possible to use a set in your case statement?

CASE
(
select distinct tbhtg.TrainingBlockHistoryTypeGroupingCd
from tblTrainingBlockHistory tbh
inner join tblTrainingBlockHistoryType tbht on tbh.TrainingBlockHistoryTypeCd = tbht.TrainingBlockHistoryTypeCd
inner join tblTrainingBlockHistoryTypeGrouping tbhtg on tbht.TrainingBlockHistoryTypeGroupingCd = tbhtg.TrainingBlockHistoryTypeGroupingCd
where (select dbo.fnTrainingBlockStatus( 1234, getdate())) = tbht.TrainingBlockHistoryTypeCd
)
WHEN 'S' then (COUNT(DISTINCT TRD.TrainingBlockHistoryId) = COUNT(DISTINCT SWT.TrainingBlockHistoryId)) end


This is giving me an error on the WHEN statement. The error is "Incorrect syntax near '='" Have no idea how to fix this.
But the select statement seems to work, and as far as I can tell, that is how you write a CASE statement.

Also, this CASE statement is inside the HAVING clause - is that going to be a problem?

View 3 Replies View Related

Query Help With Update Statement, Thanks!

Feb 15, 2006

Hi Group!I am having a problem of using SUM under UPDATE statement. I understandthat SQL does not allow me to use SUM in UPDATE, but unfortunately, Ican not find a way to get around it. Can someone please give me someidea? Thanks a million!--Here is the criteria:Under the same PORTFOLIO_ID, if the ACCOUNT_OPENDATE is in the samemonth as the PORTFOLIO_OPENDATE, then sum the account_openamt for thesame PRODUCT, and update the CHECKING_OPENAMT, SAVINGS_OPENAMT,CD_OPENAMT, etc in table PORTFOLIO. For other accounts opened NOT inthe same month as the PORTFOLIO_OPENDATE, just ignore them.--Here are the tablescreate table portfolio(portfolio_id int,portfolio_opendate smalldatetime,checking_openamt money,savings_openamt money,cd_openamt money)insert into portfolio values(1,'2/15/2005',0,0,0)create table account(portfolio_id int,product varchar(20),account_opendate smalldatetime,account_openamt money)insert into account values(1,'checking','2/15/2005',2000)insert into account values(1,'checking','2/20/2005',3000)insert into account values(1,'savings','2/20/2005',3000)insert into account values(1,'cd','5/15/2005',5000)--Ideal Output--id portfolio_opendatechecking_openamtsavings_openamtcd_openamt1 2/15/2005500030000--Here is my query:update portfolioset checking_openamt=sum(b.account_openamt) --problem appears!from portfolio as ajoin account as bon a.id=b.idand year(a.portfolio_opendate)=year(b.account_opendate )and month(a.portfolio_opendate)=month(b.account_openda te)and product ='checking'--and product='savings'--and product='cd'Thanks again!!

View 3 Replies View Related

Select Statement Query

Jan 29, 2008

In my select statement, I return a column for 'datediff' using a CASEquery. I call this column 'Elapsed_days'is there anyway I can use this result later on in the same select? IEI want to refer to 'elasped days' in another CASE query rather thanhave to re-write something which incorporates the original one.Simpler the better - I'm new!Make any sense?Hope so

View 1 Replies View Related

Is It Possible Write In 1 Query Statement?

Aug 20, 2007

Hi guys



I'll appreciate any help with the following problem:

Table 1: TBSALESHEADER
PK: SALESNO

Table 2: TBSALESENTRY

FK: SALESNO


This query is return 2 rows.
SELECT PAYMTDID AS TYPE, CONVERT(CHAR(8),SALESDATE,112) AS DATE, SUM(SALESAMT) AS AMOUNT FROM TBSALESHEADER A, TBSALESENTRY B WHERE A.SALESNO = B.SALESNO AND CONVERT(CHAR(8),SALESDATE,112) = '20070701'
GROUP BY PAYMTDID,CONVERT(CHAR(8),SALESDATE,112)


TYPE DATE AMOUNT
----------------------------------------------------------
0 20070701 3041.29
1 20070701 1442.20


Issue:
Instead of retrieving 2 rows showing 2 type of records, I want to display the 2 type in 2 columns (need to return in 1 row).


Can I get this desired result as following in 1 query statement:

DATE AMOUNT (TYPE 1) AMOUNT (TYPE 2)
---------------------------------------------------------------------------------
20070701 3041.29 1442.20

Please help.

Thanks.

View 5 Replies View Related

IIF Statement In Query Designer

Jul 23, 2007

Hello,



I am trying to use following IIF Statement in Query designer but getting error message saying "Incorrect syntax

near '>')

Can anyone please help, perhaps correct the statement for me?



IIF([Expected Receipt Date] > now(), 1, 0) AS EXPR1



Thank you in advance



-- Praf

View 4 Replies View Related

SQL Statement - Query A List Of Values

Oct 31, 2006

Hello  I have a newbie question.  If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen 

View 4 Replies View Related

How Do You Hold The Value Of A CASE Statement For Further Use In The Query??

Feb 18, 2007

I have a select statement and I would like to know which when clause(1st set of whens) was fired and the value of the then for further use in my query.  I am wondering if this is possibly, and how it would be done.  If i try and set a @variable at the beginning of the case statament i get an error, here is my query         SELECT PIE.productID, PIE.quantity, CEILING((PIE.width/12.0)/0.5)*.5 as width, CEILING((PIE.length/12.0)/0.5)*.5 as length,                 ***throws an error**** @sqft = CASE  --- but the then clause of this CASE is the value i want for later use in this same query                                 WHEN CEILING((PIE.width/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.length/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.width/12.0)/0.5)*.5) * 1.5) *                                     CEILING((PIE.length/12.0)/0.5)*.5)/1.0)*1.0                                 WHEN CEILING((PIE.length/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.width/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.length/12.0)/0.5)*.5) * 1.5) *                                     CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0                                 ELSE CEILING((CEILING((PIE.length/12.0)/0.5)*.5) * (CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0                         END AS sqft,                         CASE                                 WHEN @custMarkup = 1 THEN (SELECT PML1 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 2 THEN (SELECT PML2 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 3 THEN (SELECT PML3 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 4 THEN (SELECT PML4 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 5 THEN (SELECT PML5 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                         END AS markup         FROM ProductsInEstimate PIE         WHERE estID = @estid

View 1 Replies View Related

Trouble Using Group By Statement With Query

Mar 20, 2007

Hi,
I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view.
Problem: I can't associate a field with a column that i am returning with my record set.
Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname)  that is associate with that loan agent and that loan state.  Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header.
if the query works, my Gridview should look like this ('Name' refers to the borrower name)
UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met 
Bob           |                     |                  |                     |    Name      |                |               |                   |
Bob           |                     |      Name   |                     |                   |                |               |                   |      
Bob           |                     |                  |                     |                   |                |               |      Name    |        
Steve         |                     |                  |      Name      |                   |                |               |                   | 
Steve         |                     |                  |                     |                   |                |    Name  |                   |
 
Here is my sql call: 
SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
 

View 2 Replies View Related

Logic Statement Using Select Query

May 29, 2007

I'd like to make a logic statement, that would take as arguments result of the sql select query. In more details: I would like to create a local Bool variable that would be false if some value is NULL in the table (or select query).Query example:select taskID from Users where Login=@usernameWhich classes/methods should i use to solve this problem? I use SqlDataSource to get access to database and i think i should use something like SqlDataSource.UpdateCommand and SqlDataSource.UpdateParameters but dont know how to build from this a logic statement.Thanks in advance 

View 8 Replies View Related

Can Anyone Tell Me How To Use 'Case' Statement In Select Query In SQL ?

Mar 14, 2008

I need to pull one field from one table and one field from  another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table  but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement???
is this correct ?? its showing me errors
Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ???
(case when 'DataSource =1' then 'ABC' then 'BCD' endcase)
Where .....
 Pls correct me
Thanks

View 8 Replies View Related

Query To Sum The Same Field Twice In The Select Statement

Mar 17, 2008

 Hello friends ,    I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values  'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output
Trans Date, total deposits, total withdrawls, closing balance
i.e. Trans Date,  sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved