May 31, 2012 at 4:32 am
I'm trying to write a formula so it basically brings back all the primary diagnosis fields (hospital data) except the following shown below - The problem I'm having is combining both a wildcard (like) and 'between' statements.
example;
DIAG1 (Principal Diagnosis) not between C00* - C97* OR not between D37* - D48*
Please hep 🙂
May 31, 2012 at 5:20 am
I'm trying to write a formula so it basically brings back all the primary diagnosis fields (hospital data) except the following shown below - The problem I'm having is combining both a wildcard (like) and 'between' statements.
example;
DIAG1 (Principal Diagnosis) not between C00* - C97* OR not between D37* - D48*
Your C* and D* fields are not integers and cannot be incremented or compared against as if they are - so your BETWEEN won't work. You could unpick the string instead by separating out the letter and number (check for CAST and CONVERT in BOL) and doing your BETWEEN operation then.
Also don't forget (brackets), and that NOT will take precedence over OR.
EDIT: Can you post the table structure please? I'll work on an example for you.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
May 31, 2012 at 6:48 am
That's really kind of you - I've attached a copy of the table structure.
Cheers
Steve
May 31, 2012 at 7:59 am
steviemoxford (5/31/2012)
That's really kind of you - I've attached a copy of the table structure.Cheers
Steve
What would be far better than a spreadsheet is to post ddl (create table statements) and sample data (insert statements) then desired output based on your sample data. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2012 at 10:00 am
You could do something like:
Where left(Diag1,3) > 'C00' and left(Diag1,3) < 'C97' ...
or you could create a CTE that extracts the left 3 characters of your diagnosis code (please forgive any syntax errors, I'm doing this from memory):
with codes(diag_code)
as (select left(diag1,3) as diag_code from .... )
...
Where codes.diag_code between 'C00' and 'C97' ...
Caveat: Note that YMMV depending on your data and schema.
If you regularly use the first 3 characters of the diagnosis code, you might consider creating a view, and giving things clear and understandable names.
June 1, 2012 at 10:13 am
aureolin (6/1/2012)
You could do something like:Where left(Diag1,3) > 'C00' and left(Diag1,3) < 'C97' ...
This would be non-sargable so performance could very easily become a big issue.
or you could create a CTE that extracts the left 3 characters of your diagnosis code (please forgive any syntax errors, I'm doing this from memory):
with codes(diag_code)
as (select left(diag1,3) as diag_code from .... )
...
Where codes.diag_code between 'C00' and 'C97' ...
Caveat: Note that YMMV depending on your data and schema.
If you regularly use the first 3 characters of the diagnosis code, you might consider creating a view, and giving things clear and understandable names.
This is not too bad. It is at least sargable.
It is hard to say what the best approach but actually just using string comparisons will probably work fine if the actual data is like the brief sample posted.
Just make your where clause something like this:
where DIAG1 not between 'C00' and 'C97'
AND DIAG1 not between 'D37' and 'D48'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2012 at 12:34 pm
steviemoxford (5/31/2012)
I'm trying to write a formula so it basically brings back all the primary diagnosis fields (hospital data) except the following shown below - The problem I'm having is combining both a wildcard (like) and 'between' statements.example;
DIAG1 (Principal Diagnosis) not between C00* - C97* OR not between D37* - D48*
Please hep 🙂
I'm having a bit of a problem figuring out your meaning with the BETWEEN. This is where posting DDL, sample data, and expected results really helps. Part of this means making sure your sample data includes data that will be excluded from the results set.
June 1, 2012 at 12:41 pm
I think this will do it:
WHERE
DIAG1 NOT LIKE 'C[0-8][0-9]%' AND
DIAG1 NOT LIKE 'C9[0-7]%' AND
DIAG1 NOT LIKE 'D3[7-9]%' AND
DIAG1 NOT LIKE 'D4[0-8]%'
Or, some people prefer this style:
WHERE NOT (
DIAG1 LIKE 'C[0-8][0-9]%' OR
DIAG1 LIKE 'C9[0-7]%' OR
DIAG1 LIKE 'D3[7-9]%' OR
DIAG1 LIKE 'D4[0-8]%' )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 1, 2012 at 9:07 pm
steviemoxford (5/31/2012)
I'm trying to write a formula so it basically brings back all the primary diagnosis fields (hospital data) except the following shown below - The problem I'm having is combining both a wildcard (like) and 'between' statements. DIAG1 (Principal Diagnosis) not between C00* - C97* OR not between D37* - D48*
Can you not join to an ICD-10 reference table and exclude neoplasms? That would seem to be a more robust and readable solution than trying to write complex NOT LIKE BETWEEN statements.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply