SQL Date Ranges

I run across a lot of scenarios in my work where I have to return a result set for a given time period. A simple example is that a website might need closing prices for the last day, week, month, year, etc.


select *
from PricingTable
where thedate is between
case when @DateRange = 'Weekly' then DATEADD(wk,-1,getdate()) --@WeekDate
when @DateRange = 'Monthly' then DATEADD(mm,-1,getdate()) --@MonthDate
when @DateRange = 'Yearly' then DATEADD(yy,-1,getdate()) --@YearDate
end and @CurrentDate --getdate()


Problems arise however because the date logic does not take into account weekends and holidays when the underlying data might not exist. To solve this problem, we have a UDF which essentially does the following, but in lieu of one, here's what you can do.

1. Set @CurrentDate = current date (getdate())
   
2. Set @WeekDate, @MonthDate and @YearDate = to dateadd -1 time period
   
3. Here's where it gets cool. You want to set each time period = to the highest date from the underlying table as long as it is less than or equal to the raw date you selected before.

   select @WeekDate = MAX(thedate)
from PricingTable
where thedate <= @WeekDate

   That way if the date is a valid day of the week, the date stays the same. Otherwise you get the closest date to it that is valid in the table. Depending on the requirements of your site, you can do something similar to say the minimum date where it is greater than or equal to the raw date, to shift the date the other direction
   
4. repeat this for the dates you have, and you then have working date ranges that are guaranteed to work for the underlying data.
   

In some scenarios, this much level of precision doesn't matter, but I've seen a wide range of problems arise from not using a technique like this that can manifest in strange ways, and be difficult to troubleshoot depending on the complexity of the procedure. If the underlying table is indexed on [thedate], it will speed up these seeks as well, and the extra code can save you some big headaches down the line.

EDIT 9/8/2011:
An alternative to this method is to simply say if the day it lands on is Saturday, subtract 1 day or if Sunday, subtract 2 days if you don't care about holidays. There are other function which can be written to account for US holidays, but this method works in the specific scenario where you a) need to account for holidays and b) don't have a means (yet) of accounting for holidays (or if you deal with international data where holidays could vary greatly).

Guacamole of the Gods

This is the guacamole recipe I've made since I was a little kid, and it still blows my mind. Some of the proportions can vary based on your own personal preferences (for example, I'm a garlic fiend, so I usually use a bit more of that), but I'll try to give you a good balanced version.

What you'll need:

• 2 Ripe Avocados


• Juice of half a lime


• 1 tsp Cumin


• 1 tsp Chili Powder


• 3-4 Tbs Salsa (While I wish I could say some obscure random salsa from a mom and pop farm in some obscure city is the best, Pace Chunky medium salsa really works the best in my opinion)


• 2 cloves garlic, pressed through a garlic press. If you don't have a garlic press, you can mince it real fine, but a press tastes way better. Try to avoid using garlic powder

What you do:

1. Cut the avocados in half, remove the pit and scoop their delicious guts into a bowl, mash with a fork.


2. Add all the other ingredient and stir with a fork until evenly blended.

Notes: I've made this in a food processor before to make a bigger batch, but it changes the consistency quite a bit, which personally I wasn't a fan of. Play around with it by adding slightly more or less of various ingredients, or by stirring it longer or shorter for a more pureed or chunky guacamole.

Storage:
Guacamole goes brown really easily. To prevent that, cover it with Saran wrap, and press it down so that it is completely flush with the guacamole, eliminating as many air pockets as possible. This prevents most of the oxygen from reaching the guacamole and oxidizing it.

SQL Server vs. The World

I recently had a conversation with a good friend of mine about the merits SQL Server and other databases, and I felt rather unqualified to really comment on any differences between them. I work with SQL Server on a daily basis, know it well and love it. but at the same time, I got by just fine before I got a cellphone when I was 12, just because I didn't know any better.

As such, I decided to start teaching myself LAMP development as a side project ((L)inux (A)pache (M)ySQL (P)HP for those who don't know). I'ts been a lot of fun, and I'm still far from being able to really speak to the operational differences between the two, but I do have some initial impressions about MySQL.

Pros
- Easy to get started (free download, easy install, up and running quickly)
- For everything I've used it for thus far, it performs, on a bad day, just as well as SQL Server.
- A lot of freely available support software such as a GUI, admin tool, etc
- Easily integrates with LAMP technology (there is a lot of collaboration between the communities of Linux Apache MySQL and PHP so that they flow pretty flawlessly)

Neutral (depending on viewpoint)
- Advanced syntax much different from TSQL,
- More complex configurations can require a strong understanding of the linux terminal and various command line utilities.

Cons
- The amount of documentation and helpful resources available for MySQL (and many of the LAMP technologies) pales in comparison to that available for SQL Server, making learning much more challenging
- Complex data integration tools, and limited BI support.

Conclusion: I don't have one yet. Functionally, it seems to run just as well for all intents and purposes. Since I'm still in the learning process of it, it's significantly more challenging for me than it is for me to do TSQL right now, but that also makes it a lot of fun to work on. If I had to venture a comment comparing the two right now, I would say that MySQL is free, but harder to learn, SQL Server (versions other than Express) is not free, but much more easy to learn. At the risk of sounding cliche, it comes down to personal choice.

Also, I'll freely admit that I chose to work with SQL Server initially because there is more money in it than doing LAMP Development (usually).

At an enterprise level, I have no idea which works better and for what scenarios, but if you are a smaller organization, or just someone looking to do database development, pick what you like, and get really good at it. It's a lot of fun when you start to think in terms of a programming language, be it TSQL, MySQL, or PL/SQL

Stalkers. Now that's true love

Now thats amore baby!

(source: http://www.trulylovable.com/media/738-love-is-to-think.jpg) ... f'n hipsters

Sry

I retract my previous comment about TV shows. It was insensitive and ignorant, and I apologize. Sorry.

I hate everything

HAHAHA omg, did u see wat Paris said on hur show last night? Shes leik "omg poor people should lern how to leik do things better!"

Holy fucking shit, is this what we are idolizing? No wonder our science programs are going down the tubes. No wonder we have to pretend to graduate people who are literally retarded. Because people feel like the opinion of someone born into incomprehensible riches has ANYTHING even remotely intelligible to say about ANYTHING. Fuck, this isn't a commentary about America, this is a commentary about humanity. If you find ANYTHING Paris Hilton does cheeky or entertaining, you should throw yourself off a fucking cliff. If you don't know where to find one, let me know, and I'd be happy to Google that for you

Chasing our own tail

I was watching a documentary about the big bang, quantum entanglement, and other such niftyness, and they said something which has been stated before, but made me think of something neat. Since all the matter in the universe was originally a singularity, and all the energy in the universe is linked together in some way, it made me think peaceful, awe inspired thoughts. When I thought about things like war and violence, it made me think of a dog chasing its own tail. A dog sees something weird out of the corner of his eye, and decides it is his enemy. He then spins around in circles trying to get it, not realize that it's actually itself. In a strange way, thats kind of what violence, racism and war boil down to. People see something they don't understand, so they try to chase it, hurt it, destroy it, not realizing that in reality, it's really the exact same matter and energy as ourselves. Can't help but think if people recognized this and worked together how much wasted energy could be used for something greater.