February 5, 2015 at 9:23 am
Good morning..hope somebody can help with this query. At the bottom is a list of physician and surgeon keys. They can be in either the f.AttPhysKey or f.SurgeonKey fields. If any one of these keys are in the f.SurgeonKey field, I need the row. If any one of these keys is in the f.AttPhysKey field, I need the row, but only if any of the other specified keys is not in the f.SurgeonKey field. Hope that makes sense?
Select
f.PatientKey,
f.CompKey,
f.CompNumber,
i.CompName,
f.DischargeDateTime,
f.Qualified,
f.Outcome,
f.AttPhysKey,
f.SurgeonKey,
dateadd(month,datediff(month,0,f.dischargedatetime),0) as MoYr,
FROM Comptable f
left outer join Quality.dbo.Dim_PatientSafetyIndicators i on i.compKey=f.compKey
WHERE
f.Outcome = '1'
and f.DischargeDateTime>='2014-01-01' and f.DischargeDateTime<'2015-01-01'
and f.CompKey in ('2','5','6','7','8','12','13','14','15','16','17')
--Phys and surgeon keys = '9454','888','3690','282','3806','4318','363','3882','878'
February 5, 2015 at 10:06 am
If any one of these keys is in the f.AttPhysKey field, I need the row, but only if any of the other specified keys is not in the f.SurgeonKey field.
Can they have the exact same key, and if so, do you want the row?
February 5, 2015 at 10:12 am
yes, if in the F.AttPhysKey, the same key can be in the F.SurgeonKey and yes, I'd like the row. Thanks!
February 5, 2015 at 10:20 am
Are the physician and surgeon keys I a table?
Russel Loski, MCSE Business Intelligence, Data Platform
February 5, 2015 at 10:34 am
Pretty sure the below is right, logically, for what you are looking for.
Warning that if you need to use indexes on either of those keys it could be quite slow.
Select
f.PatientKey,
f.CompKey,
f.CompNumber,
i.CompName,
f.DischargeDateTime,
f.Qualified,
f.Outcome,
f.AttPhysKey,
f.SurgeonKey,
dateadd(month,datediff(month,0,f.dischargedatetime),0) as MoYr,
FROM Comptable f
left outer join Quality.dbo.Dim_PatientSafetyIndicators i on i.compKey=f.compKey
WHERE
f.Outcome = '1'
and f.DischargeDateTime>='2014-01-01' and f.DischargeDateTime<'2015-01-01'
and f.CompKey in ('2','5','6','7','8','12','13','14','15','16','17')
AND (
(f.AttPhysKey IN ('9454','888','3690','282','3806','4318','363','3882','878')
AND (f.SurgeonKey = fAttPhysKey OR f.SurgeonKey NOT IN ('9454','888','3690','282','3806','4318','363','3882','878')))
OR (f.SurgeonKey IN ('9454','888','3690','282','3806','4318','363','3882','878')
AND (f.SurgeonKey = fAttPhysKey OR f.AttPhysKey NOT IN ('9454','888','3690','282','3806','4318','363','3882','878')))
)
February 5, 2015 at 11:22 am
this works great!..thanks so much for your help!
February 6, 2015 at 8:57 am
Firstly, do you understand the solution you have been given?
Secondly, Nasty nasty had coded values! - what happens if the list of codes changes; can you break them out to a reference table, even if it is a @InMemoryTable or #TempTable for the duration of the query? It would mean that you only have one place to manage the list and it no longer muddies the waters so that it is clearer what the query is trying to achieve. Even better if you can persist the list of codes in a permanent table.
February 8, 2015 at 7:46 am
CELKO (2/7/2015)
>> At the bottom is a list of physician and surgeon keys. They can be in either the F.attphys_key or F.surgeon_key field [sic]. if any one of these keys are in the F.surgeon_key field [sic], I need the row. <<Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
And you need to read and download the PDF for:
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/
What you did post tells us you need a data modeling course. Columns are nothing like fields. Among other things is that a column is a scalar value drawn from one and only one domain. This is not advanced RDBMS; this is the first week of any course. :w00t:
The first principle of ISO-11179 and all data element names is that you name a thing for what it is by its nature, not how or where it is used in one schema or table. That means “_key” cannot ever be what ISO calls an attribute property. That is mixing meta data into the data. Remember the fist week of class, when they defined those terms?
>> If any one of these keys is in the F.attphys_key field [sic], I need the row, but only if any of the other specified keys is not in the F.surgeon_key field [sic]. Hope that makes sense? <<
No, but it is common design error. Unfortunately we have no DDL, so we have to start guessing. I also see you put one field [sic] or keyword per line [punch card]; this is a very strong code smell that tells me you are still in a file system mindset. Is this certain? No, but after 30+ years of fixing bad SQL, it is how I will bet.
Today, an SQL programmer would have grown up with video terminals and would format his code with related data elements on the same line.
My guess is that a surgeon or an attending are roles played by physicians. Where is the role? But you modeled them as different kinds of attributes. Why? Yet you show them as just what I described in your data.
The term “datetime” is either a proprietary data type or a general class in ANSI/ISO Standard SQL; I will guess you meant a TIMESTAMP. And what precision do you really need for it? You show it as a DATE in the sample data.
Your “qualified” is a verb, not a valid data element name. Likewise, something_outcome = '1' implies you are using assembly language flags in SQL; we do not do that. Did you know, today you can write:
AND F.discharge_timestamp BETWEEN '2014-01-01' AND '2014-12-31' with the DATE data type?
What are “comp_key” and “comp_nbr”? One or the other might be a valid key, but why both?
Your “mo_yr” is wrong on several levels. This is a temporal measurement, not an attribute. You wanted “something_month” and an SQL programmer might use the MySQL shorthand with a look up table (you can Google my other posts for details).
OUTER JOINs are fairly rare in a good schema. Why does “Dim_Patient_Safety_Indicators” not always have a match (ensured with a REFERENCES clause or CHECK() constraint? It seems to be a look-up table.
Post DDL and we can try to help you.
“No matter how far you have traveled down the wrong road, turn around.” -- Turkish proverb.
Please follow basic Netiquette and post the links to free sites for each of your standards references.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2015 at 8:29 am
Well said Jeff,
@joe, it would be lovely to live in your SQL bubble where all RDMS is built and managed by people with 20 years experience, a masters degree in computer engineering and no commercial pressures. Unfortunately most of the folks who are posting that sort of question here are noobs, who have inherited an inherantly bad database design built by untrained application developers who are completely reliant on the current data types and structures where the OP has been tasked with doing something is is fundmentally difficult because of the bad design. usually the OP does not have the experience, authority or gravitas to engage for a database change, and even if they did, it does not help them to produce that report by the end of the week.
Don't come stomping in here with you size 12s, making people feel small just to show your superiority in the database design space. I am going to assume that you mean well, but be more understanding of people on the lower rungs of the SQL career ladder please. Otherwise these good meaning folk will simply go to StackExchange and continue to receive bad advice from well-meaning but less knowlegeable forum members for whom the database is simply a tool to hold persistent data and for whom performance and managagement are secondary considerations (if at all)
February 8, 2015 at 8:32 am
aaron.reese (2/8/2015)
Well said Jeff,@joe, it would be lovely to live in your SQL bubble where all RDMS is built and managed by people with 20 years experience, a masters degree in computer engineering and no commercial pressures. Unfortunately most of the folks who are posting that sort of question here are noobs, who have inherited an inherantly bad database design built by untrained application developers who are completely reliant on the current data types and structures where the OP has been tasked with doing something is is fundmentally difficult because of the bad design. usually the OP does not have the experience, authority or gravitas to engage for a database change, and even if they did, it does not help them to produce that report by the end of the week.
Don't come stomping in here with you size 12s, making people feel small just to show your superiority in the database design space. I am going to assume that you mean well, but be more understanding of people on the lower rungs of the SQL career ladder please. Otherwise these good meaning folk will simply go to StackExchange and continue to receive bad advice from well-meaning but less knowlegeable forum members for whom the database is simply a tool to hold persistent data and for whom performance and managagement are secondary considerations (if at all)
+1
February 8, 2015 at 8:32 am
This looks like a very poor design. The hard coded attributes will be very hard to keep track of and maintain. I realize that you might have to work with what you have been given, but this could definitely use a re-design.
Petr
February 8, 2015 at 8:38 am
Agree 100% with Jeff and Aaron. I was "scalded by Celko" once and it feels pretty demeaning to say the very least. I have always appreciated very much help of folks like Jeff Moden and Luiz Cazares. These folks always not only help with the actual solution, but also point and teach in a very polite and understanding manner. This is a great site that makes us all better in SQL development!
Let's not spoil it with egos and looking down on the beginners.
Petr
February 8, 2015 at 9:26 am
Lol..thanks for the replies. I appreciate the help I get from this forum. I'm a data analyst who's very new to the SQL world, so half the advice the gentleman provided above I didn't even understand. I don't create databases. I pull data the best way I know at my current skill level from databases already set up. The questions I ask on these types of forums are in the best format I know to give you the info needed to possibly help find a solution..and I usually expand my knowledge base every time. I'll have to look into this "course" that's spoken of..."correct posting on message boards 101"...lol. Do appreciate the help
February 8, 2015 at 9:28 am
glad that you did not get discouraged!
Petr
February 9, 2015 at 9:17 am
aaron.reese (2/8/2015)
Well said Jeff,@joe, it would be lovely to live in your SQL bubble where all RDMS is built and managed by people with 20 years experience, a masters degree in computer engineering and no commercial pressures. Unfortunately most of the folks who are posting that sort of question here are noobs, who have inherited an inherantly bad database design built by untrained application developers who are completely reliant on the current data types and structures where the OP has been tasked with doing something is is fundmentally difficult because of the bad design. usually the OP does not have the experience, authority or gravitas to engage for a database change, and even if they did, it does not help them to produce that report by the end of the week.
Don't come stomping in here with you size 12s, making people feel small just to show your superiority in the database design space. I am going to assume that you mean well, but be more understanding of people on the lower rungs of the SQL career ladder please. Otherwise these good meaning folk will simply go to StackExchange and continue to receive bad advice from well-meaning but less knowlegeable forum members for whom the database is simply a tool to hold persistent data and for whom performance and managagement are secondary considerations (if at all)
I once saw a poster here essentially say that computers were bad at doing more than one thing at a time, and saw that on this forum, sqlservercentral.com, and no argument whatsoever to the contrary. Additionally, I saw a fellow post an update statement that was pretty much the clearest race condition, therefore failing programming 101 and barely a peep from anyone. Saw that here on sqlservercentral.com too.
Stack exchange is a personal go to for information on a span of subject matter that vastly exceeds anything sqlservercentral will EVER aspire to in the slightest and I can right here and now point to any number of posts there discussing performance in SQL server. And I can say that even as I clearly enjoy reading and posting here as well.
You get good posts and bad posts everywhere. 2 cents.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply