February 21, 2008 at 7:40 am
I'm using sql server 2000, and I'm trying to build a db of customer information for a PR firm. I have a table with client information (ID, name, address, etc.) and I would like to keep track of their quarterly goals. These are entered by analysts manually, so a client would have 5 goals (integers) for each quarter. The table would look something like this:
ClientID Quarter goal1 goal2 goal3 goal4 goal5
1 q12007 10 15 20 10 10
2 q22007 15 10 10 15 20
I just want to make sure I structure this table correctly with the proper way to store the date, etc. Any help is greatly appreciated, thanks!
February 21, 2008 at 10:23 am
There isn't a "proper" way to do this. There are better and worse ways depending on what you are doing.
You can store the date, like the beginning of quarter (1/1/08) and then use datepart to get the quarter from there. Any date within Q1 will return one from datepart with the quarter parameter. That assumes that you are matching the calendar year.
I've done something similar and I've stored the Q1, Q2, etc. in one column and the year in another. This makes it easier to query by year. If you don't use calendar years, I might store an "F" (fiscal) or "C" in another column to denote which one applies. Companies sometimes switch things around, so you might need to track this.
February 21, 2008 at 10:34 am
Yes, thank you. I already store the companies fiscal year date in their table as a date, for example 1/1/08 as 'Fiscal_Year_Begins'.
I was assuming that if I know when their fiscal year begins, and just store the quarterly goals table as "Q1_2008", "Q2_2008", "Q3_2008" and "Q4_2008", then I can figure out (somehow) which quarter each company is in by comparing the current date to the date of the companies "fiscal year begins"?
This is why I wasn't sure of the best way to structure this, to use actual dates or to use a field like "Q2" to specify the 2nd quarter.
thanks again!
February 21, 2008 at 10:37 am
Like Steve mentioned - without an understanding of what you intend to DO with those numbers it's hard to say what's "right".
What can be said though it that you're considering a form of denormalization, which is usually to be avoided, since it hurts your flexibility. It can also increase your "support" of this data. For example - if Goal1-Goal5 are the goals by PR analyst - what happens when you hire another analyst (answer: you have to alter the table and add a column)/fire an analyst (answer - you have to remove a column/possibly rename columns)? Makes for some very messy (and as you add more data to your system, resource-intensive) maintenance tasks.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 21, 2008 at 10:42 am
As to your quarters: I'd personally store the quarter info as a datetime (say - either the first or the last day of the quarter in question), and "reverse engineer" the Quarter (possibly with a computed column).
Otherwise - store the year and the # of the quarter separately, so that you can quickly calculate the relevant date again.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 21, 2008 at 10:48 am
If you want dates, use datetime columns because then you can use all date-related functions, order by, ...
Like Steve mentioned. replace Q1 with an actual date.
In datawarehouses it is common practise to just create a table that
contains only date-related info.
e.g. date, quarter, semester, weekno, fiscalyear, ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 21, 2008 at 10:49 am
Thanks again for the replies! What I plan to do with the information:
These are for PR firms and we're tracking the number of media hits and other things they get. So we're tracking the number of Google news items each quarter, the number of requests from media outlets for briefings, the number of press releases, etc.
So out of these other tables, per month or per quarter, I can already produce:
Google Hits: 25
Press Releases: 15
analyst briefings: 7
Now for each client, each quarter, (before the quarter begins) my associate will go in and enter each clients goals for the quarter: 20 google hits, 20 press releases, and 10 analyst briefings into this quarterly goals table.
The end result is that I would like to produce a report (or a scorecard / reportcard) for each quarter that compares the actual number of Google hits to what was their goal for their specific financial quarter.
Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply