Multiple Condition in Where Clause for the same column
How do I write a MS SQL statement for the below condition?
I have a form that allows users to enter dates (
toDate) and ID (
toID) in range. The fields can be blank for all OR enter only either from or to field OR enter both from and to fields. Selection is based on the entered values to select. Below are the conditions checking in where clause for value entered.
no value entered => skip all conditions
value entered in fromDate only => Date = frDate
value entered in toDate only => Date <= toDate
value entered in both fromDate & toDate => Date between fromDate and toDate
Condition is applied to ID field as well.
Any advice is highly appreciated. Thanks in advance.
You can solve your problem using dynamic query. Your question is not fully clear. Here i'm giving you a solution which will help you to solve your problem. Try this:
1. Create Dynamic query in a Store Procedure
CREATE PROCEDURE sp_YourSPName /* Input Parameters */ @FromDate DATETIME , @ToDate DATETIME AS SET NOCOUNT ON /* Variable Declaration */ DECLARE @SQLQuery AS NVARCHAR(4000) DECLARE @ParamDefinition AS NVARCHAR(2000) /* Build the Transact-SQL String with the input parameters */ SET @SQLQuery = 'Select * From YourTableName where (1=1) ' /* check for the condition and build the WHERE clause accordingly */ IF (@FromDate IS NOT NULL) AND (@ToDate IS NOT NULL) SET @SQLQuery = @SQLQuery + ' And (YourDate BETWEEN @FromDate AND @ToDate)' IF (@FromDate IS NULL) AND (@ToDate IS NOT NULL) SET @SQLQuery = @SQLQuery + ' And (YourDate <= @ToDate)' IF (@FromDate IS NOT NULL) AND (@ToDate IS NULL) SET @SQLQuery = @SQLQuery + ' And (YourDate = @FromDate)' /* Specify Parameter Format for all input parameters included in the stmt */ SET @ParamDefinition = '@StartDate DateTime, @EndDate DateTime' /* Execute the Transact-SQL String with all parameter value's Using sp_executesql Command */ EXECUTE sp_Executesql @SQLQuery, @ParamDefinition, @FromDate, @ToDate IF @@ERROR <> 0 GOTO ErrorHandler SET NOCOUNT OFF RETURN(0) ErrorHandler : RETURN(@@ERROR) GO
2. Execute Store Procedure:
EXEC sp_YourSPName '01 Oct 2018', '01 Oct 2018'
SQL: Combining the AND and OR Conditions, select PropertyVal from your_table where PropertyID = 7 and RecordID in ( select RecordID from your_table where (PropertyID = 13 AND Enter the name of the table to which you want to apply the filter using the multiple conditions. 3: condition1: You can specify the first condition with the operator, columns, and value. 4: condition2: Specify the second condition with the operator, columns, and values to apply in the WHERE clause.
You can use IS NULL to check param has value
SELECT * FROM Table WHERE (@FromDate IS NULL OR Date > @FromDate) AND (@ToDate IS NULL OR Date < @ToDate)
Same type of query will be used for Id
Your WHERE clause does not make much sense, because it is applied to each row individually and it is pointless to check if the same value is sql - Multiple conditions on the same column in the WHERE clause - Stack Overflow. I have a table something like this - RecordID PropertyID PropertyVal--------------------------------------------------3215 7 john doe3215 11 Ch Stack Overflow. Products.
Below is the EXAMPLE and not the exact query you can try to put it in your way using CASE statements
SELECT values FROM Table WHERE CASE WHEN fromDate = null & todate = null THEN WHEN fromDate != null & toDate != null THEN Date between fromDate and toDate WHEN fromDate != null THEN Date = frDate WHEN toDate != null THEN Date = toDate
How do I select two values from the same column in SQL? I want to pick values from the same column using an and clause query, that looks something like this:. select * from degree_detail_result where course_id=1 and course_id=2 and course_id=3
How do I get one row from multiple records in SQL? The key thing is that the counting of t.flag needs to equal the number of arguments in the IN clause. The use of COUNT(DISTINCT t.flag) is in case there isn't a unique constraint on the combination of contactid and flag -- if there's no chance of duplicates you can omit the DISTINCT from the query:
Using an OR condition enables you to specify several alternative values to search for in a column. This option expands the scope of the search and can return more rows than searching for a single value. You can often use the IN operator instead to search for multiple values in the same data column. It means that the two values have to exist in the same row. The second query is a non-pairwise comparison and the two values can exist in different rows as each other. As Teradata cannot do pairwise comparisons, the best answer is probably the one below showing to concatenate the values into a combined key.
You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. The problem with your original query was that the condition in the subquery did not depend on the specific record. The subquery returned the name of every employee who is the boss of someone else. Please note that this solution usually has a worse performance than the join because it has to execute the subquery for every item.