>> I'm open to all kinds of suggestions.<<

>> This represents a table in our sales data where it is tracking the dollar value of a sales opportunity over time. <<

>> The Id is the primary key, ..<<

>> the OpportunityID defines an opportunity and is a foreign key in this table. <<

>> the TimeStamp [reserved word!!] refers to the actual time where there was a change in the Amount field [sic; columns are not anything like fields] of the Opportunity (in another table) as a result of which it wrote a record [sic: rows are not records] to the above table with the TimeStamp

>> Here's my business issue that I'm trying solve - the sum of amounts on all opportunities at any given point of time - that's we call our pipeline. <<

>>The trick is to do this in a way that it reflects the latest timestamp record [sic] for each opportunity that is less than or equal to the snapshot time that I require. <<

>> The big issue is that I want a single query (or a combination of a few) to return to me this result not for a single snapshot at a time but for a series of snapshots - for example, every Monday for the past 5 years. <<

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1178928789.615348.88610@h2g2000hsg.googlegroups.com...
>>> I'm open to all kinds of suggestions.<<
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>
> Since you have a timestamp (a reserved word that is never used for a
> column name) the magical, vague "id" column is less than useless.
> Let's use relational keys.
>
> CREATE TABLE OpportunityHistory
> (opportunity_id CHAR(3) NOT NULL
> REFERENCES Opportunities (opportunity_id),
> opportunity_starttime DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> opportunity_endtime DATETIME, -- null means current
> CHECK (opportunity_starttime < opportunity_endtime),
> PRIMARY KEY (opportunity_id, opportunity_time,
> opportunity_amt DECIMAL (12,4) NOT NULL
> CHECK (opportunity_amt >= 0.0000) ,
> etc.
> );
>
>>> This represents a table in our sales data where it is tracking the
>>> dollar value of a sales opportunity over time. <<
>
> That is a classic history table. You need to show durations and not
> Chronons of time; how long was the "window of opportunity" in the real
> world?
>
>>> The Id is the primary key, ..<<
>
> No, it is not. It is a sequential numbering that is out of
> chronological order, used for PHYSICAL access and has nothing to do
> with the data model.
>
>>> the OpportunityID defines an opportunity and is a foreign key in this
>>> table. <<
>
> We need DRI, then ..
>
>>> the TimeStamp [reserved word!!] refers to the actual time where there
>>> was a change in the Amount field [sic; columns are not anything like
>>> fields] of the Opportunity (in another table) as a result of which it
>>> wrote a record [sic: rows are not records] to the above table with the
>>> TimeStamp
> and the new Amount. <<
>
> you are mimicking a sheet of paper with a log on it, and not using a
> relational design. The sign-in/sign-out sheet has "half a fact" on
> each line of that paper form. We want a complete fact in each row.
>
>>> Here's my business issue that I'm trying solve - the sum of amounts on
>>> all opportunities at any given point of time - that's we call our
>>> pipeline. <<
>
> You need to construct a Calendar table for reporting and many other
> things. With the DDL I have given you, just use a BETWEEN predicate :
>
> WHERE @my_date BETWEEN opportunity_starttime
> AND COALESCE (opportunity_endtime,
> CURRENT_TIMESTAMP)
>
>>>The trick is to do this in a way that it reflects the latest timestamp
>>>record [sic] for each opportunity that is less than or equal to the
>>>snapshot time that I require. <<
>
> The most current row is defined by (opportunity_endtime IS NULL) and I
> would put that in a VIEW to make life easy
>
>>> The big issue is that I want a single query (or a combination of a few)
>>> to return to me this result not for a single snapshot at a time but for
>>> a series of snapshots - for example, every Monday for the past 5 years.
>>> <<
>
> Now you need to do some work. Create a table of reporting periods
> (period_name, cal_date) or
> (period_name, start_date, end_date) so can have things like
> ('Mondays', ' '2007-05-07') or ('Bikini Season' , '2007-05-01',
> '2007-08-30'). You can tune the reporting periods to the exact fit
> you need.
>
> SQL is a **data** language and not a procedural or computational
> language.
>

"nisheeth29" <nisheeth29@discussions.microsoft.com> wrote in message
news:2D7DFE1F-480F-46B3-832F-E1CD1E25F068@microsoft.com...
> So here's my question:
>
> I have a table like this:
>
> Id OpportunityID TimeStamp Amount
> 1 A 2007-05-11 15:30 $10
> 2 A 2007-05-11 12:30 $20
> 3 A 2007-05-11 10:30 $15
> 4 A 2007-05-10 18:06 $11
> 5 A 2007-05-10 10:00 $16
> 6 A 2007-05-09 23:00 $25
> 7 B 2007-04-25 15:00 $20
> 8 B 2007-04-25 10:00 $36
> 9 B 2007-04-01 12:00 $94
> 10 B 2007-04-01 10:00 $510
> 11 B 2007-03-31 09:00 $14
> 12 C 2007-05-01 23:59 $19
> 13 C 2007-05-01 00:00 $87
> 14 C 2007-04-07 12:00 $46
> 15 C 2007-04-07 00:00 $30
> 16 C 2007-04-03 00:00 $25
> 17 D 2006-05-10 00:00 $2
> 18 D 2006-05-07 13:00 $81
> 19 D 2006-05-07 00:00 $84
> 20 D 2006-05-02 00:00 $20
>
>
> This represents a table in our sales data where it is tracking the dollar
> value of a sales opportunity over time.
>
> The Id is the primary key, the OpportunityID defines an opportunity and is
> a
> foreign key in this table. The TimeStamp refers to the actual time where
> there was a change in the Amount field of the Opportunity (in another
> table)
> as a result of which it wrote a record to the above table with the
> TimeStamp
> and the new Amount.
>
> Here's my business issue that I'm trying solve - the sum of amounts on all
> opportunities at any given point of time - that's we call our pipeline. I
> want to be able to write a query on this dataset that can give me
> snapshots
> at different points of time. The trick is to do this in a way that it
> reflects the latest timestamp record for each opportunity that is less
> than
> or equal to the snapshot time that I require. The big issue is that I want
> a
> single query (or a combination of a few) to return to me this result not
> for
> a single snapshot at a time but for a series of snapshots - for example,
> every Monday for the past 5 years.
>
> So for a snapshot of Jan 1, 2007 - this query should pick up only record
> 17.
> Snapshot of Apr 2, 2007 should return records 17 and 9. And a snapshot for
> Apr 29, 2007 should return records 7, 14, and 17.
>
> I'm open to all kinds of suggestions.
>

On Fri, 11 May 2007 16:07:01 -0700, nisheeth29 wrote:

>So here's my question: