Need a View to calculate Occupancy

Question

Need a View to calculate Occupancy

Viewing 15 posts - 16 through 30 (of 33 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Matter of preference I guess. I find cte's help me keep the code cleaner actually instead of using derived tables in the FROM clause. Keeps it more like tables there without a lot of extra code to have read through.
It also has the benefit of allowing me to build the query in steps. If I know I would find myself using a derived table, I can build the query for the derived table, quickly turn it into a cte, and continue from there.

Yeah, I am still getting familar with CTE and am still a little reluctant to use them.

msaint,

Does this have to be a view? You can make this a stored procedure and pass in the dates to recieve the data. I think that would be better than a view, but I do not know exactly how you plan to use the results.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 2

Lynn Pettis (2/26/2008)
This may not answer your question, but it may help you figure out what you need to do

Nice!

--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)

Michael Saint SSC Veteran Points: 293 More actions · Answer 3

It was my hope to use this as a view so that clients who create their own reports with a variety of report writers would be able to derive a value from the view. In many cases, the report writer either is not capable of using a stored procedure or the user does not know how to call it.

ALL of you guys have posted great solutions to this issue.

Thanks,

Michael Saint

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

CTE... derived table... doesn't matter to me... they both do the same thing. I will admit a bit of a preference towards CTE's because it allows "top down" programming and, perhaps, a more modular style for documentation purposes... you solve the inner most problems first and go from there. With derived tables, you have to think "bottom up". No bigee but it's different.

I love these temporal counting problems... you get to see some really sharp solutions come out in people's code. Thanks, folks.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Jeff Moden (2/26/2008)
Lynn Pettis (2/26/2008)
This may not answer your question, but it may help you figure out what you need to do
Nice!

Jeff,

Thanks for the comment. Sometimes it isn't giving the OP the answer, but giving them some ideas to pursue on their own. I learn better if given a direction rather than just handing me the answer.

😎

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

msaint (2/26/2008)
It was my hope to use this as a view so that clients who create their own reports with a variety of report writers would be able to derive a value from the view. In many cases, the report writer either is not capable of using a stored procedure or the user does not know how to call it.
ALL of you guys have posted great solutions to this issue.
Thanks,
Michael Saint

It's too bad you can't force them all to use the SSRS report builder as you can use an SP for the data model.

As far as CTE's versus derived tables, I am starting to lean towards CTE's for readability. I am still learning all the new functionality in SQL 2005 so I am still trying to grasp how and when to use CTE's. The how is almost there, the when is the hard part. It's too easy to pick a tool and use it for everything, even when it may not be the best tool for the job. That is what I am trying to get now. When is a CTE the best tool for the job.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Lynn Pettis (2/26/2008)
Jeff Moden (2/26/2008)
Lynn Pettis (2/26/2008)
This may not answer your question, but it may help you figure out what you need to do
Nice!
Jeff,
Thanks for the comment. Sometimes it isn't giving the OP the answer, but giving them some ideas to pursue on their own. I learn better if given a direction rather than just handing me the answer.
😎

That is a good idea in some cases, but I know when I post a question here I am looking for an answer that works because I have already spent a lot of time trying to figure it out on my own and have either totally failed, found a very inefficient answer, or gotten so frustrated I want to toss the monitor across the room.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Jack Corbett (2/26/2008)
Lynn Pettis (2/26/2008)
Jeff Moden (2/26/2008)
Lynn Pettis (2/26/2008)
This may not answer your question, but it may help you figure out what you need to do
Nice!
Jeff,
Thanks for the comment. Sometimes it isn't giving the OP the answer, but giving them some ideas to pursue on their own. I learn better if given a direction rather than just handing me the answer.
😎
That is a good idea in some cases, but I know when I post a question here I am looking for an answer that works because I have already spent a lot of time trying to figure it out on my own and have either totally failed, found a very inefficient answer, or gotten so frustrated I want to toss the monitor across the room.

True, and there are times I also provide an answer. Wouldn't you also agree, however, that someone giving you an idea that you may not have thought of during your struggles (has happened to me), that sparks a sudden insight into what you are trying to accomplish?

In this case, I was trying to provide some help, while still looking at fleshing it out some. To finish the work, there are some questions that really need answering before coming up with a final solution. Do you count the date in as a day of occupancy, check in on the 1st, leave on the 2nd; is that 1 day or 2? What if someone checks in to one room, complains and is moved to another room for some reason?

Everything we have given so far is really only a start for something that may need to be more elaborate, depending on the requirements that need to be met.

😎

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

Lynn,

All true, and are the reasons why I try to be very specific when I post a problem. Sometimes I am even a little too verbose.

BTW, my comment was not a criticism, just a note on how I look at most posts. Usually it is pretty obvious when someone just wants us to do their job.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Jack,

I didn't take it as a criticism. The more detailed the request, the more info provided about what has been tried, and what is expected, the more likely you are going to get a direct answer to your request for help. Two reasons for that. One, you have demonstrated that you have tried everthing (including the kitchen sink); and two, you have included what you are trying to achieve.

That's why I have pointed quite a few people toward Jeff's article on "How to ask for help." If more people followed that format, they would get a lot more appropriate assistance.

Also, I don't know if you can ever be too verbose in providing background information. More is usually helpful. So when you need help, keep doing what you do. Personally, I can't remember a time I've been on this site and seen you ask for help. Your solutions I have seen, and they are well thought out and presented (something mine sometimes are lacking).

😎

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

Lynn,

Thanks for the kind words. I definitely have looked for help on this site, although usually for SSIS, Design, or Admin advice. I can usually put a hack together in T-SQL that works. Then I come here and find out there is a much better way:cool:

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 12

Jack Corbett (2/26/2008)
When is a CTE the best tool for the job.

Just about anytime, Jack. Like I said, internally they're no different than a derived table and they seem to be easier to read because of the top down nature.

And, not my job to judge, but nice job on the code.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Jeff,

It may not be your job to evaluate peoples code, but I personally take it as a great compliment when you have complimented me on my code. I'm sure others feel the same way.

Keep those compliments coming! (Also, I don't mind any constructive critisism you may have as well. You do a great job in that area as well!)

😎

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 14

Heh... no problem... I just like thoughtful code that helps the OP... the approaches to the problem were interesting.

--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)

Michael Saint SSC Veteran Points: 293 More actions · Answer 15

Hi Jack,

This code will need to run on both 2000 and 2005, so I assume that I do not have the CTE option. Am I correct?