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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-07 : 09:59:51
|
| Kim writes "I have a table that has current billing information. It includes date of billing, timestamp, client, and billing amount.When the record is updated, it inserts via a trigger into a historical table, deletes the current billing information from the current table, and inserts the new record.Now I would like to query the historical table for billing information as of a certain point in time. I would like to sum the billing amount by month. I am able to do this, however, the problem I run into is when a billing amount has been removed from one month and put into another. As I loop through the months, I look for the maximum record that meets my date criteria. The maximum record for the MOVED FROM month has a different timestamp than the MOVED TO month....how do I get my query to ignore the MOVED FROM month?select billingDt,sum(billingAmt)from projectBillingHist h,projectHist pwhere h.histId in (select max(h1.histId) from projectBillingHist h1 where billingtimestamp <='05/01/02 23:59:59' and billingtimestamp= (select max(billingtimestamp) from projectBillingHist where projectId=h1.projectId and billingtimestamp<'05/01/02 23:59:59') group by projectId,billingDt)and p.histId in (select max(p1.histId) from projectHist p1 where projecttimestamp <='05/01/02 23:59:59' and Timestamp= (select max(timestamp) from projectHist where projectId=p1.projectId and projecttimestamp<='05/01/02 23:59:59') group by projectId,billingDt)and h.billingtimestamp <='05/01/02 23:59:59'and p.projecttimestamp <='05/01/02 23:59:59'and h.projectId=p.projectIdgroup by billingDt" |
|
|
|
|
|
|
|
Differentiating between historical and current data.