Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tiss0183
Starting Member
18 Posts |
 Posted - 2008-01-19 : 20:24:34
|
| My data is mainly updated each month. Most of my tables have a month and year column. I have many statements such as this when I need to join tables:SELECT equity FROM vMonthlyFirmSummary WHERE MonthlyInputs.year = vMonthlyFirmSummary.year AND MonthlyInputs.month = vMonthlyFirmSummary.monthIs this bad database technique? Do you think instead I should try to create a string key field such as 2007-01 (Year - Month)? Please give any insight on how to make the DB simpler. Thanks. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-19 : 21:18:08
|
| nothing wrong with an integer type to hold months and years. when this is done, i like to add a check constraitn to make sure only valid months (1--12) and years can get in.don't use a character type.only good change would be to make field a date type (datetime or smalldatetime), in which u get the benefit of all the built in date math functions. but, having it the way u do performs great...with the caveat that u have to write your own date math methods |
 |
|
|
subrata4allfriends
Starting Member
24 Posts |
Posted - 2008-01-20 : 11:51:19
|
quote:
Originally posted by russell
nothing wrong with an integer type to hold months and years. when this is done, i like to add a check constraitn to make sure only valid months (1--12) and years can get in.don't use a character type.only good change would be to make field a date type (datetime or smalldatetime), in which u get the benefit of all the built in date math functions. but, having it the way u do performs great...with the caveat that u have to write your own date math methods
Yes, I also agree with russell.But Tiss u can use datetime as column datatype ..... and in case of join u can use the column as given below...."SELECT equity FROM vMonthlyFirmSummary WHERE YEAR[MonthlyInputs.Date]= YEAR[vMonthlyFirmSummary.DATE]AND MONTH[MonthlyInputs.DATE]= MONTH[vMonthlyFirmSummary.DATE]".......Russell am I right  Thanks & Regards,4allfriends."Life is not a bed of roses." |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-01-21 : 09:18:39
|
| It is better you have datetime datatype and store it as dates with first day of the monthMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Year, Month columns