June 28, 2011 at 10:08 am
hi friends,
I have got this code where it is looking for certain code in the table. It needs to look for any code (primary, secondary etc. etc.).
I am just thinking there must be a better way of writing this code. Do you have any suggestions? it works at the moment and quick but it could be improved where i dont need to write all codes (same things) many times. I am looking for a function but just thinking will it be quick?
select * from England.dbo.HealthData
where sex = 'Male'
and hospital = 'Cleavland'
and (left (OPERTN_01 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_02,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_03 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_04 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_05 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_06 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_07 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_08 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_09 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_10 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_11 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_12 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_13 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_14 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185')
or left(OPERTN_15 ,4) in ( 'J181', 'J182', 'J183', 'J184', 'J185'))
Any suggestions?
Thanks,
Vijay
June 28, 2011 at 10:12 am
Table design is wrong. You should only have one column meaning operator or whatever that means.
This is precisely why you shouldn't have a table built this way.
June 28, 2011 at 10:14 am
By having a normalized structure, your query would be greatly simplified.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 28, 2011 at 10:21 am
We have to have a separate column for each operation code. Any patient can have more than one operation and all listed from operation 1 to operation 14. It is right design to fit in what we do. We have all operation column which puts each operation in order (oprtn1,2,3, etc.) and looks like 'J132, J343, J342, J232' etc. It doesnt help simplifying this query.
I can do something like all_operation like '%J132%' or all_operation like %'J343%'. i think it will take more time as it is searching for pattern.
June 28, 2011 at 10:22 am
Since you're probably stuck with that table, try this:
select * from England.dbo.HealthData
where sex = 'Male'
and hospital = 'Cleavland'
and left(OPERTN_01 ,4)+
left(OPERTN_01 ,4)+
left(OPERTN_02 ,4)+
left(OPERTN_03 ,4)+
left(OPERTN_04 ,4)+
left(OPERTN_05 ,4)+ (... rest of your columns)
left(OPERTN_15 ,4)
like '%J18[1-5]%';
If you add all the Left(x,4) pieces together into a single string, and then use Like and a pattern-match, you will probably get the right results. It will depend on the way that those substring are built.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 28, 2011 at 10:49 am
GSquared,
I hate myself for not thinking this. It is one of the best solution. There must be other alternative where i can Unpivot it and then write the select statement.
It is applicable only if i am looking for the similary code like J181, J182 but if it different code then it is bit difficult. As per example if i need to consider J181, J182, P342, P332, R231, D222 etc. I think the function will be good but it needs to be written for each code. It might be slow.
Thank you very much.
June 28, 2011 at 10:52 am
dva2007 (6/28/2011)
GSquared,I hate myself for not thinking this. It is one of the best solution. There must be other alternative where i can Unpivot it and then write the select statement.
It is applicable only if i am looking for the similary code like J181, J182 but if it different code then it is bit difficult. As per example if i need to consider J181, J182, P342, P332, R231, D222 etc. I think the function will be good but it needs to be written for each code. It might be slow.
Thank you very much.
Nothing short of a redesign will be optimal. Sorry for the bad news.
And not this design is not optimal. It works but is not "theorically" correct. You'll be hitting bumps like this one often as you work with this table.
June 28, 2011 at 10:59 am
Ninja's_RGR'us,
We have a table with each patient record where they can have multiple opeation code (up to 15 operation). it has to be in a separate column as most of the time we do work on primary and secondary operation. Some times we need to consider all operation. We do hvae column called All_Operation which puts everything in one column with comma separated for each operation.
We have all information of patient record in a different table but combined most used information in one table so that every one else can use it easily.
What do you suggest to improve this design of patient record?
Thanks.
June 28, 2011 at 11:04 am
Create table PatientOperations
(PatientId, OperationId, OperationDate (for order by))
Then your query simply becomes
Select * from PatientOperations where Operation IN (Ids 1,2,3,4,5)
Join back to base table(s) to get the rest of the columns.
What you're talking about here has to do with data presentation which is the front end's job to handle, not sql server.
June 28, 2011 at 11:09 am
Ninja's_RGR'us (6/28/2011)
Create table PatientOperations(PatientId, OperationId, OperationDate (for order by))
Then your query simply becomes
Select * from PatientOperations where Operation IN (Ids 1,2,3,4,5)
Join back to base table(s) to get the rest of the columns.
What you're talking about here has to do with data presentation which is the front end's job to handle, not sql server.
Furthermore, a patient operation is no longer limited to just 15 with this design. This also means that redesign of the current table is not needed if you ever need more than 15 operations.
This is why Ninja and I recommend normalizing your data structure.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 28, 2011 at 11:22 am
Thank you both.
We will never have operation code which will go more than 15. Also by normalizing this way it will help writing some of the queries and could be faster but at the same time people here just thinks it is too much normalized version. They want most relevant information at once place and we do have code in the table but they need to join on to the code description table to find out actual description of the operation. It is not in normalised form but people have to use this information all the time so it is quick to query it if it is at one place in the right format. We have to consider milions of records if we run for longer time and if we join many tables, it could be slow. I will definately try to implement your idea but not sure if i will be successful.
thanks.
June 28, 2011 at 11:30 am
No offense but I run reports on 10s of millions of record in subseconds with 10-40 tables in joins on a 2 core 4 GB ram server... not slow when you know your stuff.
June 28, 2011 at 11:37 am
dva2007 (6/28/2011)
Ninja's_RGR'us,We have a table with each patient record where they can have multiple opeation code (up to 15 operation). it has to be in a separate column as most of the time we do work on primary and secondary operation. Some times we need to consider all operation. We do hvae column called All_Operation which puts everything in one column with comma separated for each operation.
We have all information of patient record in a different table but combined most used information in one table so that every one else can use it easily.
What do you suggest to improve this design of patient record?
Thanks.
As the others have said, this isn't ideal as it makes querying more difficult.
Databases are designed to be able to handle many to one relationships (one patient, many operations). You achieve that not by adding multiple operation fields to the patient, but by having a table with the patient_id (so you can cross reference to patient) and one record for each operation the patient has.
You can still easily cross tab that to show the data as you have it now. But it would make the kind of query you want infinitely quicker and easier to write.
June 28, 2011 at 11:52 am
Thanks very much guys. very useful information.
June 28, 2011 at 11:56 am
dva2007 (6/28/2011)
GSquared,I hate myself for not thinking this. It is one of the best solution. There must be other alternative where i can Unpivot it and then write the select statement.
It is applicable only if i am looking for the similary code like J181, J182 but if it different code then it is bit difficult. As per example if i need to consider J181, J182, P342, P332, R231, D222 etc. I think the function will be good but it needs to be written for each code. It might be slow.
Thank you very much.
Create a table (temp table or table variable) with the codes you want to look up in it. Then join using a Like operator.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply