|
|
Posted by --CELKO-- on 04/22/06 18:10
>> I have some monthly data which is being encoded as Varchar...Say for the month of January 2005 it would look like '200501'. For each of my month I have a columns which gives me datetime and volume of the job. <<
Wrong on two levels. Temporal data needs temporal data types. And if
you know the string is fixed length, why make it VARCHAR(n)? Do you
really want bad data? Did you write a proper CHECK() constaint to
prevent things like '200613'? I would bet not.
The right way is to put the range pairs into a Reporting Ranges table.
CREATE TABLE ReportRanges
(start_date DATETIME NOT NULL PRIMARY KEY,
end_date DATETIME NOT NULL,
CHECK (start_date, end_date));
INSERT INTO ReportRanges
VALUES ('2005-01-01 02:00:00.000', '2005-01-31 06:00:00.000);
etc.
Use a spreadsheet to build the data, then use a simple join. 36525
rows will cover ten years of reporting.
Navigation:
[Reply to this message]
|