Decode360's Blog

          業精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

            BlogJava :: 首頁 :: 新隨筆 :: 聯系 ::  :: 管理 ::
            302 隨筆 :: 26 文章 :: 82 評論 :: 0 Trackbacks
          <2009年3月>
          22232425262728
          1234567
          891011121314
          15161718192021
          22232425262728
          2930311234

          公告

          常用鏈接

          隨筆分類(299)

          隨筆檔案(299)

          文章分類(26)

          新聞分類(24)

          收藏夾(5)

          Blog列表

          IT網站

          My Link

          最新隨筆

          最新評論

          1 . FISCAL YEAR TABLES
          ?
          ??? Let's write some CREATE TABLE statements that are as complete aspossible. This little exercise is important because SQL is a declarativelanguage and you need to learn how to specify things in the databaseinstead of in the code.
          ?
          ??? The table looks like this:
          ??? CREATE TABLE FiscalYearTable1
          ??? (fiscal_year INTEGER,
          ???? start_date?DATE,
          ???? end_date??? DATE);

          ?
          ??? It stores date ranges for determining what fiscal year any given datebelongs to. For example, the federal government runs its fiscal year fromOctober 1 until the end of September. The scalar subquery you woulduse to do this table lookup is:
          ??? (SELECT F1.fiscal_year
          ?????? FROM FiscalYearTable1 AS F1
          ????? WHERE outside_date BETWEEN F1.start_date AND F1.end_date)

          ?
          ??? Your assignment is to add all the constraints you can think of to thetable to guarantee that it contains only correct information.
          ?
          ??? While vendors all have different date and time functions, let's assumethat all we have is the SQL-92 temporal arithmetic and the function:
          ??? EXTRACT ([YEAR | MONTH | DAY] FROM <date expression>), whichreturns an integer that represents a field within a date.
          ?
          ?
          Answer #1
          ?
          ??? 1. First things first; make all the columns NOT NULL since thereis no good reason to allow them to be NULL.
          ?
          ??? 2. Most SQL programmers immediately think in terms of addinga PRIMARY KEY, so you might add the constraint ?PRIMARYKEY (fiscal_year, start_date, end_date) because the fiscal year isreally another name for the pair (start_date, end_date). This isnot enough, because it would allow this sort of error:
          ??? (1995, '1994-10-01', '1995-09-30')
          ??? (1996, '1995-10-01', '1996-08-30') <== error!
          ??? (1997, '1996-10-01', '1997-09-30')
          ??? (1998, '1997-10-01', '1997-09-30')

          ??? You could continue along the same lines and fix some problemsby adding the constraints UNIQUE (fiscal_year),UNIQUE (start_date), and UNIQUE (end_date), since we donot want duplicate dates in any of those columns.
          ?
          ??? 3. The constraint that almost everyone forgets to add because it isso obvious is:
          ??? CHECK (start_date < end_date) or CHECK (start_date <=end_date), as is appropriate.
          ?
          ??? 4. A better way would be to use the constraint PRIMARY KEY(fiscal_year) as before, but then since the start and end datesare the same within each year, you could use constraints onthose column declarations:
          ??? CREATE TABLE FiscalYearTable1
          ??? (fiscal_year INTEGER NOT NULL PRIMARY KEY,
          ???? start_date?DATE NOT NULL,
          ???? CONSTRAINT valid_start_date
          ???????CHECK ((EXTRACT (YEAR FROM start_date) = fiscal_year - 1)
          ?????????AND (EXTRACT (MONTH FROM start_date) = 10)
          ???????? AND (EXTRACT (DAY FROM start_date) = 01)),
          ???? end_date????DATE NOT NULL,
          ???? CONSTRAINT??valid_end_date
          ?????? CHECK ((EXTRACT (YEAR FROM end_date) = fiscal_year)
          ?????????AND (EXTRACT (MONTH FROM end_date) = 09)
          ???????? AND (EXTRACT (DAY FROM end_date) = 30)));

          ??? You could argue for making each predicate a separate constraintto give more detailed error messages. The predicates onthe year components of the start_date and end_date columnsalso guarantee uniqueness because they are derived from theunique fiscal year.
          ?
          ??? 5. Unfortunately, this method does not work for all companies.Many companies have an elaborate set of rules that involve takinginto account the weeks, weekends, and weekdays involved.They do this to arrive at exactly 360 days or 52 weeks in theiraccounting year. In fact, there is a fairly standard accountingpractice of using a “4 weeks, 4 weeks, 5 weeks” quarter withsome fudging at the end of the year; you can have a leftoverweek between 3 and 11 days. The answer is a FiscalMonthtable along the same lines as this FiscalYears example.
          ?
          ??? A constraint that will work surprisingly well for such cases is:
          ??? CHECK ((end_date - start_date) = INTERVAL 359 DAYS)
          ?
          ??? where you adjust the number of days to fit your rules (i.e., 52 weeks * 7?days = 364 days). If the rules allow some variation in the size of the fiscalyear, then replace the equality test with a BETWEEN predicate.
          ??? Now, true confession time. When I have to load such a table in adatabase, I get out my spreadsheet and build a table using the built-intemporal functions. Spreadsheets have much better temporal functionsthan databases, and there is a good chance that the accountingdepartment already has the fiscal calendar in a spreadsheet.
          ?
          ?




          -The End-

          posted on 2009-03-20 21:21 decode360-3 閱讀(190) 評論(0)  編輯  收藏 所屬分類: Toy
          主站蜘蛛池模板: 临漳县| 江源县| 灵丘县| 江孜县| 巴东县| 昭苏县| 贺兰县| 永和县| 建平县| 苍南县| 白银市| 抚顺市| 化德县| 寻甸| 兴仁县| 防城港市| 徐闻县| 新干县| 化隆| 庆阳市| 新龙县| 河东区| 定西市| 缙云县| 秦安县| 德惠市| 河源市| 乌恰县| 宁阳县| 鄯善县| 沂源县| 西林县| 玛纳斯县| 鄂伦春自治旗| 达拉特旗| 曲阜市| 东源县| 封丘县| 芦溪县| 乳山市| 天长市|