Creating date chronology from "not so specific" date field 1

[glgcag1991]

I'm sure I'm not the only one who has to do this, but I'm not sure the best approach and can't find anything specific about this, so here's my quandary:

I have projects that are assigned a completion date that may or may not be specific. If we know the completion date because the contract is set in stone for the project, then we enter the exact date. For projects where we have an estimated completion date but nothing in stone, users need to be able to enter non-specific dates. Currently we are using several date indicators such as Summer 2009, Holidays 2008, March-May 2009, etc. This means the date field is free text and can't be used to create a report with chronology of project completions for future projects, which, of course, the CFO wants to be able to estimate future revenue. Is there a way to do this with existing tools or should I create my own "meaningful" ranges such as the following:

Field 1: Completion Date (set in stone, therefore is used if it exists)
Field 2: Completion Range (drop down of months, defined ranges such as Summer or Holidays, etc.)
Field 3: Completion Range 2 (drop down of months to indicate the end of month range in case user chose a month from Field 2)
Field 3: Completion Range Year (if user is using a range)

Now I would have to define in the query that if a range of Sept-Nov 2008 was selected, where I insert it into the chronology report. (In Sept-Nov, I would list it after all "set in stone" completion dates.) It seems like a lot of work but I'll do it if there isn't a better way with existing SQL Server tools.

Your help is appreciated!

 

[ousoonerjoe]

To keep it simple, what about using the last day of the range in a date field and a Description field that could be used to explain the ranges. Have a BIT field to tell you if the project has a "Range Date" or hard date.

ProjectID INT
DueDate DATETIME
IsRange BIT
Description VARCHAR(255)

This should allow you to generate some "Future Expectations" with relative ease. DueDate can also be used as a "Mile-Marker" for the project and expected payments.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[glgcag1991]

So the DueDate field would be either a date (1/1/2009) OR a range (11/1/2008-1/1/2009). (Can I store a date range in a DATETIME field?) Then the range would have a description of Holidays which I would concatenate on the report with the YYYY of the DueDate field?

Definitely sounds easier than my solution.

 

[ousoonerjoe]

if you really want to store the range, then add another field "StartDate DATETIME" and let that be the beginning of the range.

StartDate = begining of the range.
DueDate = End of specific range. Exact DueDate.

Rather than allowing NULLS and dealing with those, for ease of retievals, on projects with only an exact Due Date, place that date in both DueDate and StartDate. It becomes ineffect a zero day range.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[glgcag1991]

OK, that's what I thought. I'll give that a go, thanks!