I create a stored procedure that creates reports based on data in an SQL database and stores these reports in a separate database. Reported data is the total time in the date range during which the engine has been running. I want to be able to determine whether the time I'm reporting is overlapping with previous reports, depending on the time.
For example, we have the following table:
Row-----Value-----StartDate------------------EndDate
1-------12--------2010-01-21 00:00:00.000----2010-01-21 11:59:99.997
2-------12--------2010-01-22 00:00:00.000----2010-01-22 11:59:99.997
3-------12--------2010-01-22 12:00:00.000----2010-01-22 23:59:99.997
4-------12--------2010-01-23 00:00:00.000----2010-01-23 11:59:99.997
5-------12--------2010-01-24 00:00:00.000----2010-01-24 11:59:99.997
If I had to insert the following rows into this table:
INPUT:
1. Value = 12, StartDate = 2010-01-21 6:00:00.000, EndDate = 2010-01-21 17:59:99.997
2. Value = 14, StartDate = 2010-01-21 11:00:00.000, EndDate = 2010-01-22 0:59:99.997
3. Value = 13, StartDate = 2010-01-20 12:00:00.000, EndDate = 2010-01-21 0:59:99.997
CONCLUSION:
1. Value = 6, StartDate = 2010-01-21 12:00:00.000, EndDate = 2010-01-21 17:59:99.997
2. Value = 12, StartDate = 2010-01-21 12:00:00.000, EndDate = 2010-01-21 23:59:99.997
3. Value = 12, StartDate = 2010-01-20 12:00:00.000, EndDate = 2010-01-20 23:59:99.997
As you can see, start and end dates are customizable and extra time is deleted. It is important to note that the results are that only one row is added, and not all 3 at the same time.
, , . , , , , , .
: , , lap ?
!
* *
, value = 6 1 - , 1 6 . .
- 1, EndDate + 3 (timespan = 6 ).
- 1 2, EndDate 1 + 3 StartDate 2 - 3 . (Timespan = 12 )
- 1, EndDate 1 - 3 . (Timepsan = 12 ).
1 , , .