ChrisM@Work (12/13/2013)
Greg Edwards-268690 (12/13/2013)
Chris -I agree with a lot of your Why's. See this all the time.
If you could look at this, run the view for next of kin info, and tell me a better way, I'd appreciate it.
Not exactly the same as what the poster has, but I think close enough in concept.
Thanks.
I've never been a big fan of views Greg, a device designed to simplify coding almost always ends up doing the exact opposite through overuse -mostly ignorance. Your approach is nice though. The views could easily fit into a query which visibly models the business case. Here's the other side of that coin:
"What are you trying to do in this crazy 12,000 line stored procedure?"
"This and this and that and that and then something else then back to the beginning and repeat for each branch, and then go on to the next month, and that's only this year! It's really really complicated and it's taking like 48 nested cursors to process each piece!"
"What are you really trying to do?"
"Get last year's gross and net sales figures aggregated by branch and month and compare with what we've got so far for this year".
😀
To me, the view can be a way to break down the coding at times. They (when used properly) can keep business logic in 1 place, where sometimes queries are replicated several times, and usually end up tweaked at some point, giving different results.
Part of my view usage comes from the DW background, and usually we would have a table with 1 current record, and older records. Many times only the current record was of interest, so that was a view exposed to the users. As you can see, it can greatly simplify things when you have outer join needs, but have to select a certain record type out of the right hand file.
Tried your cross apply, but was seeing a cartesian join giving more results.
Since you have my sample, if you have a few minutes, I would be very interested in seeing a query giving the same results off the base tables without the views. I know I have a lot to learn.
Felt the same pains as you many times.
Your last year to this year was something we ran across many times.
At the end of the day, usually what they want is to be able to predict are we coming in ahead or behind the prior year.
Our answer was daily rates in most cases, especially as our fiscal calendar was a 4 4 5 week arrangement, with 52 weeks in every year.
Why over complicate? Some think it's job security. To me, some of the most elegant solutions are the simplest.