'Pretty-up' the code

Question

Post reply

'Pretty-up' the code

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try this as an alternative:

;WITH CTE_Filter AS (

SELECT drugid

FROM dphadrugdata d

WHERE d.usagetype LIKE '%Non%'

AND d.active = 'Y'

GROUP BY drugid -- <<<---- dedupes, might not need this

)

SELECT 'Meds' AS Sourcebook, m.drugid, m.ordersetid, m.orderid

FROM CTE_Filter f

INNER JOIN doeordersetphapcsmeds m ON m.drugid = f.drugid

UNION ALL

SELECT 'Carriers' AS Sourcebook, c.drugid, c.ordersetid, c.orderid

FROM CTE_Filter f

INNER JOIN doeordersetphapcsivcarriers c ON c.drugid = f.drugid

UNION ALL

SELECT 'Additives' AS Sourcebook, a.drugid, a.ordersetid, a.orderid

FROM CTE_Filter f

INNER JOIN doeordersetphapcsivadditives a ON a.drugid = f.drugid

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 2

Hey Pharmboy!

I didn't - it was a guess. I live in Minneapolis / Saint Paul, and it is a Medical Community Mecca here. The U of M has always been in the top 3 for their Pharm School, and I have many friends who are PharmD's. I know what it must have taken you to get accepted and do what you did, so my hat off to you sir! A PharmD is a Doctor of Pharmacy, and in my book - just as important (if honestly not moreso) than an M.D.

Anyways - as for a BS-Pharm, I am guessing that the Bachelors of Science is what you are eluding to there. Most of my friends did 3 years of Pre-Pharm and then went directly into the program.

pharmboy4u SSC-Addicted Points: 474 More actions · Answer 3

Ya a BSPharm is a bachelors and the PharmD is a doctorate. Actually, since 2003 or so there are only PharmD degrees available, the BS has gone by the wayside. I graduated as a BSPharm in '01, but then went back to school non-traditional to get my doctorate w/ my wife (who is a ~~legal drug dealer~~ pharmacist too). Glutton for punishment I guess.

But data manipulation and acquisition is becoming evermore important in hospital health care, and large amounts of resources are being focused towards that end. As a pharmacist who is primarily a computer guy since ~5yrs old w/ my brother's awesome Commodore64 or Amiga, I hope to find a niche in the serious computer program/software side of pharmacy in the future.

Some manager someday has to understand that me utilizing all the clinical skills of health care-reasoning I have in conjunction with all the LIVE informational data reporting possible from being a 'good geek' makes me a huge asset. That's the dream I keep telling myself at least... 😉

Thanks for the help guys.

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 4

That's incredibly impressive. I was reconsidering my career and life not that long ago, and was beginning to go back to school to take Pre-Pharm / Business classes. Ultimately, I decided to go back to my roots, and thus have become a DBA, but if I was younger and in this generation of students, I would have gone through Pharm to become a medical researcher (go to Rainforest's in Brazil and do research in the soils and plants for cures for anything from Cancer to AIDS/HIV, Alzheimer's, etc...).

If that is your desire, I hope you wind up doing something in medical research. I know what the loans alone for a Pharmacy student run over a 4 year period of time, so again - my hat off to you in all that you are doing. I know how busy you folks get!

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Thanks for the education, folks.

As a sidebar, I have a pharmacist that told me that there are a whole lot of MD's that don't even know how to spell PDR never mind use one. Considering that one MD prescribed prescription Naproxen Sodium for $125 for a 2 week supply instead of saying you could get a month's supply of smaller tablets over the counter for about $12 for the generic stuff, I'd have to say I suspect that may be true.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aureolin Hall of Fame Points: 3520 More actions · Answer 6

Jeff Moden (3/2/2011)
Thanks for the education, folks.
As a sidebar, I have a pharmacist that told me that there are a whole lot of MD's that don't even know how to spell PDR never mind use one. Considering that one MD prescribed prescription Naproxen Sodium for $125 for a 2 week supply instead of saying you could get a month's supply of smaller tablets over the counter for about $12 for the generic stuff, I'd have to say I suspect that may be true.

Heh. I thought that only happened to us. The wife went to see a specialist who's first comment was "Well, what do you want me to do about it" followed by a prescription for 600mg Ibuprofen. When she asked "why don't I just buy a bottle of ibuprofen and take 3 of them?" he just sort of gaped at her, nor was he able to tell her what he expected the Ibuprofen to do for her. Needless to say we haven't been back.

:crazy:

Remember: 50% of all doctors graduated in the bottom half of their class!

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

aureolin (3/4/2011)
Remember: 50% of all doctors graduated in the bottom half of their class!

Oh! Now THERE's a scary thought, for sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aureolin Hall of Fame Points: 3520 More actions · Answer 8

Jeff Moden (3/4/2011)
aureolin (3/4/2011)
Remember: 50% of all doctors graduated in the bottom half of their class!
Oh! Now THERE's a scary thought, for sure.

Back on topic: It applies to DBA's as well. 😛

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 9

Yeah, but when I prescribe that my developers not do SELECT * from their factories, it won't be hazardous to their health (unless they make me really angry by not taking my advice!)

😛

Just kidding, but point well taken.