Tag Archives for learning sql

30-Minute Meals [and by "Meals" I mean SQL Queries]

It was an unexpected call and it carved up a 45 minute divot in my afternoon.

On a monthly basis I will get an email from Emily [client reporting] and one from Amy [client billing] for reports out of the Point of Sale system. I could probably set them up with access to the POS app, but considering how finicky it can be, I’d rather just run the reports myself than commit to supporting it on more workstations than I have to.

So I get this call from Amy in Accounting because the cash logs from the store don’t match the bank statements and would it be possible to run that monthly report again. But maybe this time, can I run it individually for each day in January. And can she get it today. Sure, but not without some sarcasm. I told her I’d be happy to do it, but she’s going to owe me a steak dinner. She counter-offered with a cupcake. Turns out someone upstairs brought in a whole bunch of cupcakes and nobody was eating them. I demanded she personally deliver it. She said she didn’t know where I sit. Just go downstairs and walk around until you find someone who looks like me who is expecting to see someone who looks like you holding a cupcake.

The daily report for January 2nd wasn’t helpful at all, so it’s time to dig into the SQL database for a little TRANS JOIN SALE action. I told her I would have to dig into the backend. She giggled. Over the next 5 minutes I was grunting at my computer while trying to find what transaction types were for credit cards. She asked if my computer was being slow or if I just didn’t know what I was doing. So I took a screenshot of SSMS screen with the TRANS table open and sent it to her. Stunned silence. Amy, I’ll call you back. The next 30 minutes was putting together the query and prettying up the results. Probably the most gratifying part of my day. 6 months ago, I would have had the same reaction as Amy: stunned silence. I wouldn’t have had a clue what I was looking at and would have been more than a little intimidated. But today I made it look easy. Feels good.

I never did get that cupcake. But I saved the code. I named it “how-to-get-amy-from-accounting-off-your-back.sql”

Unseasonably Warm

It was a high of 64 degrees today and I’m taking full advantage. I’m going camping. Just as soon as the conversion I’m a part of is finished.

I don’t know why they insist on scheduling these for Fridays. Who likes to work on Friday night? Let alone every other Friday night until July. All I’m doing tonight is backing up the Point of Sale since the Pharmacist in Charge wants to own clearing out the dispensing workflow. Fine by me. In the last two months I’ve gotten plenty of practice backing up SQL databases there shouldn’t be any surprises. One hour from now, I’ll be on my way to a campsite with my sleeping bag, cook kit, and some Diet Mountain Dew to hang out around a campfire with some friends and pretend it’s the middle of summer.

I was tempted [for about 2 seconds] to attempt a campfire bacon weave, but my food poisoning earlier in the week kinda rules that out. I am going to make some red beans and rice though.

HL7 Interfaces & You

We’re updating versions for one of our sites; this one happens to have an HL7 interface to our lab provider so we can send the requests electronically. We’ve been told by our vendor that upgrading will break the interface and they delivered a series of scripts as a solution. So I work for about 90 minutes to backup the database in QA, get my SQL scripts in order and execute them, update a .dll on the application server, restart the service, and figure out exactly how to login to the application and send a test message. I’m somewhat lost in the EMR still, though I seem to be getting acquainted with the table structure of the db.

So it’s all updated, I test, and I get an error message. The error message very clearly tells me that the procedure code I selected has an illegal character. That naughty ampersand (‘&’). So I pick a different code, same error, same naughty ampersand. So I raise my hand for help, someone pulls out of their hat “this script we ran last month” that clears all special characters. Run that, restart the service, same error. I’m in it for about 2 hours at this point. So I do what anyone would: I restore the backup and try again. This time I run the script to remove all special characters right after the script to reformat the order request. I restart the app service, reopen the client, try again, same error. So I take the text of the error message, run a select * from tablename where errormsg like ‘%text%’ and low and behold, I get about a dozen hits; all of them listed as inactive except one. Ouch. it’s not for the same procedure code I’ve used (at this point I’ve used about 10 different ones, it ain’t one of them). So for fun: (1) copy the description field, (2) paste into notepad, (3) get frustrated. ‘and’. not ‘&’. The clear-all-special-chars script works. The data is correct; only Heaven knows where the application is trying to read from. 3 hours of my day.

So I walk on over to the Implementation Specialist who’s been mentoring me and ask the question of the week:

“When you’ve done these conversions, have you ever seen this?”

“We’ve never upgraded this particular interface for this particular version. First time. How’s the testing going?”

So I guess as it turns out, the testing was going well.

UPDATE: turns out a config file had not been updated. The interface was updated but the jobs weren’t running. score 1 for the home team.

First week in a new job

Boy has it been a busy one.

  • Had my first call with our credit card processing vendor’s Technical Support. Also had my second and third calls this week too. The last rep I spoke to was Tom who offered to put together a workflow and walk me through the entire process of the credit card thigh bone is connected to the merchant bank  hip bone (so to speak), so I’ll schedule a call with him next week.
  • Fixed a lingering issue where TIF files lost their file association, so when trying to view an attachment from the web app, you have to Save and Open With. Ended up modifying a few registry keys and pushed that out to about 5 machines.
  • Setup a Webex for another vendor’s offshore programmers to troubleshoot an error message on a daily report. The offshore programmer was working slowly enough that I was able to watch him work and pick up on a few things. Turns out some stored procedures from a previous version needed to be updated. They’ve delivered the script that they tested and I’ll test that out on Monday. If it works, we’ll schedule it for production.
  • Had my first conversation with my old boss as his collegue, not just his direct report. We talked about persistent support issues, the upcoming company-wide Holiday party, and the SEC Championship game. Personally, I’m looking forward to the Holiday IT Pot Luck Breakfast more anyway.

The transition from Helpdesk to Implementations has been very smooth. It’s even been a great opportunity for the two new Support guys to step up and fill the void I created when I “jumped off a cliff” and joined the Implementations team.

The good news is that before my new boss hired me, she and I had a very open and honest conversation about what I didn’t know and what I didn’t have experience in. Not living up to expectations is what haunted my predecessor. I guess what’s worse: bad news or suprise bad news? Working in my favor is that I’ve been with the company for 2.5 years, I’m a known commodity, and proven that I’m a fast learner. Three years ago I was working for a dot com earning a sales commission every month. Two years from now I could probably get a job as a Database Administrator. Not a bad career path so far.

I’m waiting for the day when I’m asked to tackle something that is completely and entirely out of my league and everyone “finds me out”. It didn’t happen this week. My biggest concern is next week. I hope nobody “finds me out”.