February 22, 2008 at 3:46 pm
I have a stored procedure in which I pass certain parameters.
Based on my parameter list I want to select rows that meet any of the criteria specified and I also want to find which rows met which of the criteia.
Can anyone suggest me a way of doing this?
This is what my query will look like:
SELECT j2821.RepName, j2821.LogEntryID, j2821.Transaction2821ID, j2821.TransactionPercentOfNetWorth,
j2821.AnnualIncome, j2821.TimeHorizon, j2821.RegistrationType,j2821.ProductType
FROM
Transaction2821Journals j2821 INNER JOIN
Journals j ON j2821.LogEntryID=j.LogEntryID
WHERE
j.JournalTypeID = 8
AND j2821.DateClientSignature >= '2/5/2008'
AND j2821.DateClientSignature <= '2/21/2008'
AND (j2821.RegistrationType = 'Qualified')
OR (j2821.Amount > 1000) OR (j2821.TimeHorizon IN ('5-10 Years') OR (j2821.Income > 1000)
I want to know which of the OR conditions were met for all the rows returned.
Any help appreciated.
February 22, 2008 at 4:09 pm
Please take a few minutes to read the following article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
It would help if you would provide us with table DDL and sample data. This will allow us to test any code we may write before we provide you feedback. In this case, this would be quite helpful.
😎
February 22, 2008 at 4:15 pm
Remember, this code is untested, but you could try this:
SELECT
j2821.RepName,
j2821.LogEntryID,
j2821.Transaction2821ID,
j2821.TransactionPercentOfNetWorth,
j2821.AnnualIncome,
j2821.TimeHorizon,
j2821.RegistrationType,
j2821.ProductType,
case when j2821.RegistrationType = 'Qualified' then 'RegistrationType is Qualified' end,
case when j2821.Amount > 1000 then 'Amount > 1000' end,
case when j2821.TimeHorizon IN ('5-10 Years') then 'TimeHorizon in 5 - 10 Years' end,
case when j2821.Income > 1000) then 'Income > 1000' end
FROM
Transaction2821Journals j2821
INNER JOIN Journals j
ON (j2821.LogEntryID = j.LogEntryID)
WHERE
j.JournalTypeID = 8
AND j2821.DateClientSignature >= '2/5/2008'
AND j2821.DateClientSignature <= '2/21/2008'
AND ((j2821.RegistrationType = 'Qualified')
OR (j2821.Amount > 1000)
OR (j2821.TimeHorizon IN ('5-10 Years'))
OR (j2821.Income > 1000))
😎
February 22, 2008 at 4:25 pm
Thanks for the response.
I have a Big complex query which involves join on 5-6 tables and I want to test the OR condition for 7 columns.
I did try something like the query You had posted but the problem I am facing is I might end up making permutations and combinations of all 7 columns becos i want to capture 'any' column that resulted in the row, which would make my case list very very huge.
Is there a better and cleaner way of doing that.
I am sorry I am not an expert in SQL , I just know how to write basic queries 🙁
February 22, 2008 at 4:33 pm
You are going to have to show me what you are trying to accomplish. Read this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
then following what it recommends and post the table DDL, sample data, and expected results. If you don't (or can't) want to use live data, come up with some ficticious data that mimcs your actual data and what you are trying accomplish.
This will make it easier for us to help you, and could help you as you work through the process outlined in article.
😎
February 22, 2008 at 4:50 pm
Here I have uploaded the DDL and Data
February 22, 2008 at 4:56 pm
All that is missing now is your expected results when the data is queried.
February 22, 2008 at 5:03 pm
I want to pull records that fulfill any of the given conditions:
Client Age (>/</=) certain number
Amount (>/</=) certain number
Annual Income (>/</=) certain number
TransactionPercentofNetWorth (>/</=) certain number
Time Horizon is in given range of values
Registration Type is in given range of values
I want to extract all the rows that meet any of the conditions and along with the data i also want to display a column that displays what all conditions are fulfilled.If all were fulfilled i want to list all(a comma seperated value)
Does this help?
I am badly stuck with the query and have no clue.
I would appreciate if u could help me with this.
February 22, 2008 at 5:07 pm
Oh and along with that there are some conditions that always need to be met...like the producttype and transactiontype...so there are some conditions that will go in an AND conjunction.
February 22, 2008 at 9:19 pm
I'd like to provide you with more help, but you still haven't provided us with the expected output you are looking for from the query. I am not going to try and guess what the return values from the query should be. You need to provide what you want from the query.
Please read the article I posted the link to earlier. If you follow the directions in that article for posting help, there are many of us who will be more than happy to provide you with more help.
😎
February 23, 2008 at 5:00 pm
I am sorry if I didn't make myself clear enough.
Though I don't quite understand what you meant by the expected output.
I need to select all the columns from the table and along with that I want a column that will display (cooma seperated value) all the conditions that were fulfilled.
I want to write my select statement as -
SELECT j2821.RepName, j2821.LogEntryID, j2821.Transaction2821ID, j2821.TransactionPercentOfNetWorth, j2821.AnnualIncome, j2821.TimeHorizon, j2821.RegistrationType,j2821.ProductType, j.LogEntryID, j.BranchNumber
FROM
Transaction2821Journals j2821 INNER JOIN
Journals j ON j2821.LogEntryID=j.LogEntryID
WHERE
j.JournalTypeID = 8
AND j2821.DateClientSignature >= '2/5/2008'
AND j2821.DateClientSignature <= '2/21/2008'
AND (j2821.RegistrationType = 'Qualified')
[highlight=#ffff11]OR (j2821.Amount > 1000)
OR (j2821.TimeHorizon IN ('5-10 Years')
OR (j2821.Income > 1000)[/highlight]
What I am expecting is a list of all the rows that fulfill any of the 3 conditions that have the OR conjunction(highlighted) and a column that list what all conditions were fulfilled out of the 3.
Like if j.2821Amount > 1000 and j2821.Income > 1000 both were fulfilled then the column should display "Amount greater than 1000, Income exceeded 1000"
Does it make sense yet?
February 23, 2008 at 10:16 pm
I think what you are going to need to do is have a separate column for each OR condition, for example:
SELECT
...
,case when <condition one> then 'condition one met' end As condition1
,case when <condition two> then 'condition two met' end As condition2
...
Return the above to the client and have the client combine into a single display value, or you can do the following:
SELECT
...
,condition1 + ',' + condition2 + ', ' + condition3 ...
FROM (SELECT
...
,case when <condition one> then 'condition one met' end as condition1
,case when <condition two> then 'condition two met' end as condition2
...
Which could also be rewritten in SQL Server 2005 as:
;WITH myCTE (col1, col2,..., cond1, cond2, ...) As
(SELECT
...
,case when ...
,case when ...
FROM
...
WHERE
...)
SELECT
...
,cond1 + ', ' + cond2 + ', ' + cond3 ...
FROM
myCTE
Basically, the concept is to identify each possible condition that can match and return a column - then use each column to build the final result.
HTH,
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 25, 2008 at 8:12 am
One (perhaps not very efficient) approach is to have a separate query for each OR condition, which adds a column describing the where criteria and then merge (UNION) the results into a single table. If nothing else, it will make you think about how you want to handle source rows that meet multiple OR criteria.
February 25, 2008 at 9:15 am
The following is untested. I was out on the pitch all day Saturday and Sunday, and didn't have time to create your tables or convert your data into insert statements.
SELECT
j2821.RepName,
j2821.LogEntryID,
j2821.Transaction2821ID,
j2821.TransactionPercentOfNetWorth,
j2821.AnnualIncome,
j2821.TimeHorizon,
j2821.RegistrationType,
j2821.ProductType,
j.LogEntryID,
j.BranchNumber,
substring(
case when j2821.Amount > 1000 then 'Amount > 1000, ' else '' end +
case when j2821.TimeHorizon IN ('5-10 Years') then 'TimeHorizon IN 5-10 Years, ' else '' end +
case when j2821.Income > 1000 then 'Income > 1000, ' else '' end, 1,
len(
case when j2821.Amount > 1000 then 'Amount > 1000, ' else '' end +
case when j2821.TimeHorizon IN ('5-10 Years') then 'TimeHorizon IN 5-10 Years, ' else '' end +
case when j2821.Income > 1000 then 'Income > 1000, ' else '' end) - 2) as MatchCriterea
FROM
dbo.Transaction2821Journals j2821
INNER JOIN dbo.Journals j
ON (j2821.LogEntryID = j.LogEntryID)
WHERE
j.JournalTypeID = 8
AND j2821.DateClientSignature >= '2/5/2008'
AND j2821.DateClientSignature <= '2/21/2008'
AND (j2821.RegistrationType = 'Qualified')
AND ((j2821.Amount > 1000)
OR (j2821.TimeHorizon IN ('5-10 Years'))
OR (j2821.Income > 1000))
😎
February 25, 2008 at 2:53 pm
This was a helpful query.
I tested the query with my tables and data and it gives me the desired result.
Thanks for helping me out with this.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply