I'm trying to use a conditional statement in the where clause.
Here is my table
UID Amount ID PID Amount2
1 30000 8064 NULL NULL
2 30000 8042 8064 30000
What I'm trying to achieve:
If Amount = Amount2 for UID 2 then show UID 1
Greetings,
After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.
I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.
SELECT uniqueID, lob, xdate
FROM mytable
WHERE
CASE WHEN @myparam = 'ALL'
THEN
xdate >= '2007-09-01'
ELSE
xdate >= '2007-09-01' or
lob = @myparm
END
I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.
I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.
Any ideas?
Rob
Hi,
[SQL 2005 Express]
I would like a DropDownList to be populated differently depending on the selected value in a FormView.
If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.
Here is the SQL for case 1:
SELECT AdviserID, AdviserName FROM Advisers WHERE (CompanyID = @CompanyID).
Here's the SQL for case 2:
SELECT AdviserID, AdviserName FROM Advisers WHERE (CompanyID = @CompanyID) AND (TypeID = 3).
Here's my best (failed) attempt to get what I want:
SELECT AdviserID, AdviserName FROM Advisers WHERE IF @CompanyID = 2 THEN BEGIN (CompanyID = @CompanyID) END ELSE BEGIN (CompanyID = @CompanyID) AND (TypeID = 3) END
I've also tried:
SELECT AdviserID, AdviserName FROM Advisers WHERE CASE @CompanyID WHEN 2 THEN (CompanyID = @CompanyID) ELSE (CompanyID = @CompanyID) AND (TypeID = 3) END
and
SELECT AdviserID, AdviserName FROM Advisers WHERE CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID) ELSE (CompanyID = @CompanyID) AND (TypeID = 3) END
I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.
Thanks very much.
Regards
Gary
Hi all,
I have a table QT defined as
CREATE TABLE [dbo].[QT](
[Query] [nvarchar](50) NULL,
[Frequency] [int] NULL
) ON [PRIMARY]
Now based on a parameter I want to include a predicate in the select statement.
Basically I am trying to write something similar to the one below but possible only usinf one select statement.
if @queryString is null then
select query ,sum(frequency)
from qt
group by query
else
select query ,sum(frequency)
from qt
group by query
where query = @queryString.
Now is there a way to achieve this thing without using two separate select? The actual code I am trying to write is much bigger and I am trying to see if there is more compact way of expressing things.
Thanks
Aye.
Is it possible to use a conditional statements in a where clause?
IE: I have 3 paramaters that may or may not be filled.
I would like to do something along the lines of...
Select * From (tables)
WHERE
If @param1 has value
Begin
'run this where statement
if @Param2 has value
'add this to the where clause
if @param3 has value
'add this to the where cluase
Can I use "CASE WHEN ... THEN ... ELSE ... END" in the where clause of a SQL statement? I have sucessfully used it in the select portion of my statment but I would also like to use conditional criteria in the WHERE portion. Any advice is greatly appreciated.
I have a store procedure where i need to use conditionel where clause
View 2 Replies View RelatedhiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedConsider the following two functionally identical example queries:Query 1:DECLARE @Name VARCHAR(32)SET @Name = 'Bob'SELECT * FROM EmployeesWHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name ENDQuery 2:SELECT * FROM Employees WHERE [Name] = 'Bob'I would expect SQL Server to construct an identical QEP under the hoodfor these two queries, and that they would require essentially thesame amount of time to execute. However, Query 1 takes much longer torun on my indexed table of ~300,000 rows. By "longer", I mean thatQuery 1 takes about two seconds, while Query 2 returns almostinstantly.Is there a way to implement a conditional WHERE clause withoutsuffering this performance hit? I want to avoid using the IF...THENmethod because I frequently require several optional parameters in theWHERE clause.Thanks!Jared
View 6 Replies View RelatedMy source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum             PolicyStartdate
123GÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01/01/2012Â Â Â Â
456DÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 02/16/2012Â Â Â Â Â
789AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01/21/2012
163JÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex:Â policynum ='456D'Â Â Â Â It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.
My simple QueryA
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL
My simple QueryB
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL
I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.
Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?
I know it can be done using dynamic SQL construction. But any other ways ?
Also can someone also give in the solution in PL/SQL.
Thanks a bunch.
Jaffery.
I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere if @sports not null user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!
View 10 Replies View RelatedI have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!
View 6 Replies View RelatedHi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement?
Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test]
(
@startRef int,
@endRef int
)
AS
BEGIN
SELECT Count(*) FROM myTable
WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef)))
END
RETURN
Hi all,I have one for all the blackbelters out there: is there a way i canmake a stored procedure where i can control the where statement withvariables? I have to do some complex transformations to get compose afact table for MSAS and there a a lot of similarities between thequeries and a few differences because of different account methodsetc. (booking in starting date, booking stuff on order entry datesetc) I want to put a combination of different rules in differentmembers of dimensions.An example of what i mean:CREATE STORED PROCEDURE dbo.FILLFACT (@PAR1, @PAR2)ASINSERT INTO FactTable (blah blah)SELECTIF @PAR1 = 'OrderDate'SourceView.OrderdateELSESourceView.StartDate,etc etc...FROMSourceViewWHEREIF @PAR2 = 'WholeTable'1=1IF @PAR2 = 'Incomplete'EndDate IS NULL OR EXIST (SELECT * FROM Exceptions WHERE...., etc)This way i could fill my fact table withEXEC dbo.FillFact 'beginDate','Wholetable'EXEC dbo.FillFact 'begindate', 'Rulebook1'EXEC dbo.FillFact 'BeginDate', 'Exceptions'etcetera.This is not an actual SQL script i use, just an example of what i'mtalking about. Or maybe i could pass the where statement entirley as avariable? But i can't use SET @PAR1 = 'EndDate IS NULL' and then useWHERE @PAR1 can I?I hope i'm making sense. Does anyone know if this is possible? Rightnow i have a procedure that is composed of a dozen of sql scripts thatare mostly the same, but i have to copy it for every combination ofsituations and then, of course, new stuff has to be added on 12different places. Again and again.Any thoughts?TIA,Gert-Jan van der Kamp
View 2 Replies View RelatedHi, I've been looling around, but was unable to correctly use a conditional statement in a T-SQL Query.
I have this SQL query and in it how I would do if it was C#. If someone could please help me get
what I need I would appreciate it a lot. Here is the query:
Code Block
SELECT Ficha, Almoxarifado, [Código do Item], Descrição, Unidades.Unidade,
[1ª Contagem], [2ª Contagem], Recontagem, Observações, Cancelar FROM Fichas
INNER JOIN Itens ON [Código do Item] = Código
INNER JOIN Unidades ON Itens.Unidade = Unidades.ID
WHERE Ficha BETWEEN 01 AND 5000 AND Recontagem IS NULL AND
/* What I would do in C#, but need in T-SQL*/
if ([1ª Contagem] > [2ª Contagem])
{
if ([2ª Contagem] / [1ª Contagem] < 0.99)
return true;
else
{
if ([1ª Contagem] / [2ª Contagem] < 0.99)
return true;
}
I really need a help in this. Does anyone know how to accomplish this?
Thanks.
Regars,
Fábio
I'm trying to gather some user statistics based on 3 conditions. First I want to check if the referring querystring is already in the database. If not insert it into the db. Second, if the querystring is already in the database, then check if the ip-address of the user is already in the database. If it is, then check if the ip address was inserted today. If not, update the "refCountIn" field with +1. The problem lies in the third condition where we check if the ip-address was inserted today and if false, update the "refCountIn" field with +1 and if true, do nothing.Below is the code I have until now: 1 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
2
3 ' *** Declare the variables
4 Dim getStatCmd As String
5 Dim updStatCmd As String
6
7 Dim myRef As String = Request.QueryString("ref")
8 Dim myQueryString As String = Request.ServerVariables("QUERY_STRING").Replace("ref=", "")
9 Dim myRemoteAddr As String = Request.ServerVariables("REMOTE_ADDR")
10 Dim myHttpReferer As String = Request.ServerVariables("HTTP_REFERER")
11
12 Dim dtNow As Date = DateTime.Now
13 Dim dtToday As Date = DateTime.Today
14
15 ' *** Conditional INSERT command
16 getStatCmd = _
17 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime = '" & dtToday & "') " & _
18 "BEGIN " & _
19 "SELECT 'This clickin has already been recorded!'" & _
20 "END ELSE BEGIN " & _
21 "SELECT 'Clickin recorded' " & _
22 "INSERT INTO tblReferers(robotName, refIpAddress, refReferer, refTime) " & _
23 "VALUES(" & _
24 "'" + myQueryString + "'," & _
25 "'" + myRemoteAddr + "'," & _
26 "'" + myHttpReferer + "'," & _
27 "'" + dtToday + "')" & _
28 "END "
29
30
31 ' *** Conditional UPDATE command
32 updStatCmd = _
33 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime <> '" & dtToday & "') " & _
34 "UPDATE tblReferers " & _
35 "SET refCountIn = refCountIn + 1, refTime = '" & dtNow & "' " & _
36 "WHERE refIpAddress = '" & myRemoteAddr & "' AND robotName = '" & myRef & "'"
37
38 Dim insConnCmd As New SqlCommand(getStatCmd, New SqlConnection(connSD))
39 Dim updConnCmd As New SqlCommand(updStatCmd, New SqlConnection(connSD))
40
41 insConnCmd.Connection.Open()
42 insConnCmd.ExecuteNonQuery()
43 insConnCmd.Connection.Close()
44
45 updConnCmd.Connection.Open()
46 updConnCmd.ExecuteNonQuery()
47 updConnCmd.Connection.Close()
48
49 End Sub
Anyone with an idea on how to solve this one? I think I need to write a subquery for the third condition, but I don't have a clue on how to handle this.Thanks in advance for your help!
I'm learning SQL and here I'm trying to use two things that I'm not familiar with - IF statements and the Inserted temporary table.
Here's the background - skip this paragraph if you like. I'm working on a tasking system for the Help Desk - they get requests from the web site for various items and I break up the request into Software, Hardware, Accounts, etc tables and list the status of each item as "Requested". I'm also keeping a Tasks table to make work orders for each item requested. I've got triggers on the Accounts and Hardware tables that automatically make a new task for those items but the Software is more tricky because all software for a given request should be just one task. Software installs are all done by one person at the same time.
So I'm trying to make a trigger that creates a new Task when a new Software record is inserted. But if a task already exists with the same RequestID (meaning they requested two peices of software and this is the second one), then I just want to update the task already created. Here's what I got:
1 CREATE TRIGGER [NewSoftwareTask]
2 ON [dbo].[Software]
3 AFTER INSERT
4 AS
5 BEGIN
6
7 -- If a software task already exists for this request
8 -- then update it. Otherwise create a new task.
9
10 if exists(select TasksID
11 from Tasks
12 where Tasks.RequestsID = inserted.RequestsID and
13 TasksType = 'Software')
14 BEGIN
15 UPDATE [BGHelpdesk].[dbo].[Tasks]
16 SET [TasksDescription] = [TasksDescription] + vbcrlf + "Install " + inserted.SoftwareType + ". " + inserted.SoftwareComments
17 WHERE Tasks.RequestsID = inserted.RequestsID and
18 TasksType = 'Software'
19 END
20
21 else
22
23 BEGIN
24 INSERT INTO [BGHelpdesk].[dbo].[Tasks]
25 ([RequestsID]
26 ,[TasksType]
27 ,[TasksSubType]
28 ,[TasksTitle]
29 ,[TasksDescription])
30 SELECT
31 s.RequestsID
32 ,'Software'
33 ,s.SoftwareType
34 ,'New ' + s.SoftwareType + ' Account for Request ' + cast(s.RequestsID AS varchar)
35 ,s.SoftwareComments
36 FROM Software s join
37 inserted ON s.SoftwareID = inserted.SoftwareID
38 END
39 END
40 GO
It keeps balking at lines 12 and 17 saying "The multi-part identifier "inserted.RequestsID" could not be bound." The ELSE statement is what I use on the other tables and it works fine so the inserted temp record seems pretty straightforward but I must be doing something wrong...
Hello dbForumers,
Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?
Thank You!
Table with water consumption per month and customer.
I want to sum up total consumption per customer with a select statement
currently I am creating a total if the date is between two entered dates:
select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount,
I need to check an additional column for ($1.00) or $1.00. If it contains $1.00 then proceed as normal and add to the total. If it contains ($1.00) then subtract one from the total. Any advice?
This is probably quite simple, but it isn't sticking out at me.
In a simple table with two groups, I want to conditionally set the color of the first group to red or green based upon whether ANY value for this field equals a particular string.
I know how to conditionally set the color of the field. However, the below code appears to only compare the FIRST value in Fields!myField.Value to the string "bad". I want it to return "Red" if ANY of the values for Fields!myField.Value = "bad".
Code Block=IIf(Fields!myField.Value = "bad", "Red", "Green")
myField is ouput in the detail scope if that makes any difference.
Is this possible?
Hi,
Is there a work around for the following query on sql server compact edition
IF(Some Condition) --
BEGIN
INSERT INTO @TEMP
SELECT 1, XYZ_ID FROM My_Table
END
Thanks,
Dp
Have a View where I need to enter a conditional IF_THEN_ELSE statement in a new field (field has an alias). If this were an Access query I would do following:
IIf([dbo.AR1_CustomerMaster.EmailAddress] Is Null, "A", "B")
How can I accomplish same in View design??
Hi,I'm trying to do calculations in a SQL statement, but depending on onevariable (a.type in example) I'll need to pull another variable fromseperate tables.Here is my code thus far:select a.DeptCode DeptCode,a.Type Type,(a.ExpenseUnit / (select volume from TargetData b where b.type =a.type)) ExpenseFromcalc1 aThe problem... a.Type can be FYTD, Budget, or Target... and dependingon which one it is, I need to make b either FYTDData, TargetData, orBudgetData. I'm thinking a case statement might do the trick, but Ican't find any syntax on how to use Case in an MS SQL statement. EvenIf statements will work (if that's possible), though case would beless messy.Any suggestions would be much appriciative. Thanks...Alex.
View 4 Replies View RelatedIs it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO
View 5 Replies View RelatedWhat I need to do in seperate a group of numbers into two different categories based on a phase code. I have acheived this through two conditional statements, but when I try to total the numbers that were returned for each group I receive an #error.
This is an example of the switch statement I used in order to return the correct values for the Implemenataion.
=Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, "")
I've tried several different methods for aggregating the numbers that are returned.
=SUM(Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, ""))
I've tried substituting a 0 in for the "" at the end of each statement. I've also tried to take the first statement and put it into its own table field named ImplementationLedger, and them summing it. ie. =SUM(Fields!ImplementationLedger.Value)
Please Help!
Two questions:
1) I am unable to get a detail row of my table to suppress. I am using:
=Iif(IsNothing(Fields!Address2.Value), True, False)
2) I am not able to get an If/Then statement to work. I am able to use the followingl; however, ideally I would want to have multiple if/else statements:
=Iif(Fields!Taxschid.Value = "CUST", 0, Fields!Selling_Price.Value*.08)
Tx
Les
My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.
[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]
Hi All,
I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.
My BASIC Select Statement is:
SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1,
Address2, Suburb, State, Postcode, Phone
FROM Customers
WHERE Customer_No IN
(SELECT Customer_No FROM temp_Customers)
Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below:
SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No
FROM Customers
GROUP BY Customer_No
ORDER BY First(Cust_Ref_No), Customer_No;
There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?
P.S. I really need to run the select statement as one step rather than splitting it up into parts.
Regards, Trog28
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I have attached my data model for reference.
I have the below where clause on a select
where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and ((pa2.df_institutionname not like ('ACT%')
or pa2.df_institutionname not like ('county%')))
It returns records that have either one of the not like statements. If I comment out the line and use below it will work for one of the statements
where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and pa2.df_institutionname not like ('ACT%')
How can I get the two to work together. I thought the parenthesis would work but no luck.
actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.
i have try to use:
if @IssueUserID = ' ' Select....(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services.... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help.....
================================================== ==
SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount,
tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation
FROM tbl_Receipt WITH (nolock) INNER JOIN
tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN
tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN
tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID
WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @DateFrom, 103) AND CONVERT(datetime, @DateTo, 103)) AND
(tbl_Receipt.IssueUserID = @IssueUserID)
ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeID