Calculating count 1

[mptwoadmin]

Hi,
I have the following data and am trying to calculate the count based on start & end times..but i cannot think of how to start in SQL. Can someone help me Please?

1.start time "2011-03-07 07:18:36.0000000" 61862
2.end time "2011-03-07 08:03:04.2920000" 471

Around 7:30 the data values reset.

I know the following 61862 - 63456 + 471..But I cannot think of how to do this in SQL correctly.

--Data--
Date/time Value

2011-03-07 07:18:36.0000000 61862
2011-03-07 07:22:38.5720000 62607
2011-03-07 07:26:41.1440000 63456
2011-03-07 07:30:43.7160000 434
2011-03-07 07:34:46.2880000 471
2011-03-07 07:38:48.8600000 471
2011-03-07 07:42:51.4320000 471
2011-03-07 07:46:54.0040000 471
2011-03-07 07:50:56.5760000 471
2011-03-07 07:54:59.1480000 471
2011-03-07 07:59:01.7200000 471
2011-03-07 08:03:04.2920000 471

Thanks

 

[gmmastros]

Based on the sample data you posted, what is your expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mptwoadmin]

If I start at the following

1. 61862 - start
2. 63456 -middle
2. 471 - end
3. 1123 - answer

I'm thinking start with the first time stamp
Then subtract the max value then add the last minimum value.

Thanks

 

[gmmastros]

I'm not completely sure I understand what you are trying to accomplish here, but I will present some code with the hope that it helps you in some way.

The code below creates a table variable with your sample data. You can copy/paste this code to a query window and run it to see how it works.

Declare @Temp Table(D DateTime, Value Int)

Insert Into @Temp Values('2011-03-07 07:18:36.000',    61862)
Insert Into @Temp Values('2011-03-07 07:22:38.572',    62607)
Insert Into @Temp Values('2011-03-07 07:26:41.144',    63456)
Insert Into @Temp Values('2011-03-07 07:30:43.716',    434)
Insert Into @Temp Values('2011-03-07 07:34:46.288',    471)
Insert Into @Temp Values('2011-03-07 07:38:48.860',    471)
Insert Into @Temp Values('2011-03-07 07:42:51.432',    471)
Insert Into @Temp Values('2011-03-07 07:46:54.004',    471)
Insert Into @Temp Values('2011-03-07 07:50:56.576',    471)
Insert Into @Temp Values('2011-03-07 07:54:59.148',    471)
Insert Into @Temp Values('2011-03-07 07:59:01.720',    471)
Insert Into @Temp Values('2011-03-07 08:03:04.292',    471)

Declare @StartTime DateTime,
        @EndTime DateTime

Set @StartTime = '2011-03-07 07:18:36.000'
Set @EndTime ='2011-03-07 08:03:04.292'

Select  (  Select Value
           From   @Temp
           Where  D = @StartTime)
        - (Select Max(Value) 
           From   @Temp
           Where  D Between @StartTime And @EndTime)
        + (Select  Value
           From    @Temp
           Where   D = @EndTime)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mptwoadmin]

Great I'll try this when I have acces to SQL.
I'm just trying to calculate the sum product from the start date.time value and the end date.time value.
But in my db the value is rest at 7:30 or around that time each day. I am just havin trouble visualizing how to do the query and account for this daily value reset.

Thanks

 

[mptwoadmin]

Thanks so much worked great.

 

[mptwoadmin]

I have been running the qry through some server data and have run into a question. The qry does not handle the following type of data.

2011-03-17 07:26:00.0000000 209016 - Start Time
2011-03-17 07:27:00.0000000 209016
2011-03-17 07:28:00.0000000 209016
2011-03-17 07:29:00.0000000 209016
2011-03-17 07:30:00.0000000 209016
2011-03-17 07:31:00.0000000 209016
2011-03-17 07:32:00.0000000 0
2011-03-17 07:33:00.0000000 0
2011-03-17 07:34:00.0000000 0
2011-03-17 07:35:00.0000000 0
2011-03-17 07:36:00.0000000 0
2011-03-17 07:37:00.0000000 0
2011-03-17 07:38:00.0000000 0
2011-03-17 07:39:00.0000000 0
2011-03-17 07:40:00.0000000 1
2011-03-17 07:41:00.0000000 2 - End Time

Using the following qry I get back "209014". I should get back "2". Can you help me understand how to overcome this issue?

Declare @StartTime DateTime,
@EndTime DateTime

Set @StartTime = '2011-03-17 07:26:00.0000000'
Set @EndTime = '2011-03-17 07:41:00.0000000'
Select ( Select Value
From @Temp
Where D = @StartTime)
- (Select Max(Value)
From @Temp
Where D Between @StartTime And @EndTime)
+ (Select Value
From @Temp
Where D = @EndTime)

Thanks again.

 

[mptwoadmin]

I have another example of data that is giving me an incorrect return

2011-03-17 23:27:17.6210000 118168 - Start
2011-03-17 23:28:17.6210000 118713
2011-03-17 23:29:17.6210000 119248
2011-03-17 23:30:17.6210000 119782
2011-03-17 23:31:17.6210000 62
2011-03-17 23:32:17.6210000 605
2011-03-17 23:33:17.6210000 1136
2011-03-17 23:34:17.6210000 1683
2011-03-17 23:35:17.6210000 2187
2011-03-17 23:36:17.6210000 2187
2011-03-17 23:37:17.6210000 2187
2011-03-18 07:10:37.6210000 200018
2011-03-18 07:10:47.6210000 200109
2011-03-18 07:10:57.6210000 200203
2011-03-18 07:11:07.6210000 200282
2011-03-18 07:11:17.6210000 200376
2011-03-18 07:11:27.6210000 200470
2011-03-18 07:11:37.6210000 200552
2011-03-18 07:11:47.6210000 200642
2011-03-18 07:11:57.6210000 200735
2011-03-18 07:12:07.6210000 200827
2011-03-18 07:12:17.6210000 200917
2011-03-18 07:12:27.6210000 201008
2011-03-18 07:12:37.6210000 201088
2011-03-18 07:12:47.6210000 201182
2011-03-18 07:12:57.6210000 201262
2011-03-18 07:13:07.6210000 201262
2011-03-18 07:13:17.6210000 201262
2011-03-18 07:13:27.6210000 201262
2011-03-18 07:13:37.6210000 201262
2011-03-18 07:13:47.6210000 201262
2011-03-18 07:13:57.6210000 201262
2011-03-18 07:14:07.6210000 201262
2011-03-18 07:14:17.6210000 201262
2011-03-18 07:14:27.6210000 201262
2011-03-18 07:14:37.6210000 201262
2011-03-18 07:14:47.6210000 201262
2011-03-18 07:14:57.6210000 201262
2011-03-18 07:15:07.6210000 201262
2011-03-18 07:15:17.6210000 201262
2011-03-18 07:15:27.6210000 201262
2011-03-18 07:15:37.6210000 201262
2011-03-18 07:15:47.6210000 201262
2011-03-18 07:15:57.6210000 201262
2011-03-18 07:16:07.6210000 201262
2011-03-18 07:16:17.6210000 201262
2011-03-18 07:16:27.6210000 201262
2011-03-18 07:16:37.6210000 201262
2011-03-18 07:16:47.6210000 201262
2011-03-18 07:16:57.6210000 201262
2011-03-18 07:17:07.6210000 201262
2011-03-18 07:17:17.6210000 201262
2011-03-18 07:17:27.6210000 201262
2011-03-18 07:17:37.6210000 201262
2011-03-18 07:17:47.6210000 201262
2011-03-18 07:17:57.6210000 201262
2011-03-18 07:18:07.6210000 201262
2011-03-18 07:18:17.6210000 201262
2011-03-18 07:18:27.6210000 201262
2011-03-18 07:18:37.6210000 201262
2011-03-18 07:18:47.6210000 201262
2011-03-18 07:18:57.6210000 201262
2011-03-18 07:19:07.6210000 201262
2011-03-18 07:19:17.6210000 201262
2011-03-18 07:19:27.6210000 201262
2011-03-18 07:19:37.6210000 201262
2011-03-18 07:19:47.6210000 201262
2011-03-18 07:19:57.6210000 201262
2011-03-18 07:20:07.6210000 201262
2011-03-18 07:20:17.6210000 201262
2011-03-18 07:20:27.6210000 201262
2011-03-18 07:20:37.6210000 201262
2011-03-18 07:20:47.6210000 201262
2011-03-18 07:20:57.6210000 201262
2011-03-18 07:21:07.6210000 201262
2011-03-18 07:21:17.6210000 201262
2011-03-18 07:21:27.6210000 201262
2011-03-18 07:21:37.6210000 201262
2011-03-18 07:21:47.6210000 201262
2011-03-18 07:21:57.6210000 201262
2011-03-18 07:22:07.6210000 201262
2011-03-18 07:22:17.6210000 201262
2011-03-18 07:22:27.6210000 201262
2011-03-18 07:22:37.6210000 201262
2011-03-18 07:22:47.6210000 201262
2011-03-18 07:22:57.6210000 201262
2011-03-18 07:23:07.6210000 201262
2011-03-18 07:23:17.6210000 201262
2011-03-18 07:23:27.6210000 201262
2011-03-18 07:23:37.6210000 201262
2011-03-18 07:23:47.6210000 201262
2011-03-18 07:23:57.6210000 201262
2011-03-18 07:24:07.6210000 201262
2011-03-18 07:24:17.6210000 201262
2011-03-18 07:24:27.6210000 201262
2011-03-18 07:24:37.6210000 201262
2011-03-18 07:24:47.6210000 201262
2011-03-18 07:24:57.6210000 201262
2011-03-18 07:25:07.6210000 201262
2011-03-18 07:25:17.6210000 201262
2011-03-18 07:25:27.6210000 201262
2011-03-18 07:25:37.6210000 201262
2011-03-18 07:25:47.6210000 201262
2011-03-18 07:25:57.6210000 201262
2011-03-18 07:26:07.6210000 201262
2011-03-18 07:26:17.6210000 201262
2011-03-18 07:26:27.6210000 201262
2011-03-18 07:26:37.6210000 201262
2011-03-18 07:26:47.6210000 201262
2011-03-18 07:26:57.6210000 201262
2011-03-18 07:27:07.6210000 201262
2011-03-18 07:27:17.6210000 201262
2011-03-18 07:27:27.6210000 201262
2011-03-18 07:27:37.6210000 201262
2011-03-18 07:27:47.6210000 201262
2011-03-18 07:27:57.6210000 201262
2011-03-18 07:28:07.6210000 201262
2011-03-18 07:28:17.6210000 201262
2011-03-18 07:28:27.6210000 201262
2011-03-18 07:28:37.6210000 201262
2011-03-18 07:28:47.6210000 201262
2011-03-18 07:28:57.6210000 201262
2011-03-18 07:29:07.6210000 201262
2011-03-18 07:29:17.6210000 201262
2011-03-18 07:29:27.6210000 201262
2011-03-18 07:29:37.6210000 201262
2011-03-18 07:29:47.6210000 201262
2011-03-18 07:29:57.6210000 201262
2011-03-18 07:30:07.6210000 201262
2011-03-18 07:30:17.6210000 201262
2011-03-18 07:30:27.6210000 201262
2011-03-18 07:30:37.6210000 201262
2011-03-18 07:30:47.6210000 201262
2011-03-18 07:30:57.6210000 201262 - End

I get back 118168 using the qry provided above. Still trying to sort through data. Can you explain a work around for the examples?

Thanks