Calculating amount of records over 2 days.

[soklear]

Greetings!
If I have two columns, the first is a SQL function to provide the time from hour to hour:

declare @HStr int
set @HStr = convert(varchar,datepart(hour,@startTime))+'
- '+convert(varchar,datepart(hour,@startTime)+1)

which gives me the result:

0-1
1-2
2-3
etc...

the second, would be the count of total records from a second column in tblb:

insert into tbla
select @hstr,count(tbla.Inbound)
from tblb
where start_time>@starttime
and start_time <dateadd(hour,1,@startTime)

So, the table would look like this:
Hour Count
0-1 1
1-2 5
2-3 4


The above works fine when I the start date begin time and end time are witin a 24 hour period. However, when the start date/time beginning and end are more then 24 hours, I get duplicate results.

Im stumped on this one. I do not know how to take a sum of the total for the hours (say 0-1 over a 48 hour period).

What is the SQL Function that would allow me to calculate the total amount of records in a table column between dates?

I think the problem is with the dateadd function, but am not sure. Can anyone help?

 

[zhavic]

I am not sure, but this may be something you want.

You can create one table, let say thours with one column hour

CREATE TABLE thours ( hour integer )

than insert into that table numbers from 0 to for example 47 for 48 hour period

than use this:

DECLARE @StartTimeAndDate datetime

/* there you need to set start date and time, from which you want to return 48 hour period */
SET @StartTimeAndDate = '18.07.2004'

INSERT INTO tbla
SELECT CAST( thours.[hour] AS VARCHAR ) + '-' + CAST( thours.[hour] + 1 AS VARCHAR ),
       COUNT( tbla.Inbound ) 
FROM tblb
	CROSS JOIN thours
WHERE start_time > dateadd( hour, thours.[hour], @StartTimeAndDate )
	AND start_time < dateadd( hour, thours.[hour], start_time )
GROUP BY CAST( thours.[hour] AS VARCHAR ) + '-' + CAST( thours.[hour] + 1 AS VARCHAR )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[ESquared]

Hi soklear,

I think that your problem can be solved but I need more information in order to help you.

Would you please provide sample data, the incorrect results you are seeing, and what results you want to see?

The solution likely lies in some GROUPing BY.

 

[soklear]

Zhavic - Thanks for the response. This is almost what I need, howver, I am attempting to take the SUM of the totals from a period over two days. Say for example I have a period of two days that I need totals from. Day 1 and Day 2.

During Day 1, in tbla.Inbound I have 15 records that were inserted during a specified time (1pm - 2pm). In the same time on a different sequential day (day 2) I have 30 records created into tbla.Inbound.

I need to be able to add tbla.inbound(month-day1-04 13:00) and tblb.inbound(month-day2-04 13:00) together and insert that into the new table.

So, the result table would look something like this:"

PERIOD SUM OF RECORDS
1-2 45

Sorry if I am being too confusing.

Bob

 

[ESquared]

SELECT
   HourRange = Convert(varchar(2),DatePart(hh,@StartTime))+' - '
      +Convert(varchar(2),DatePart(hh,@StartTime)+1),
   Sum(Inbound)
FROM tbla
WHERE
   start_time >= @StartTime
   AND start_time < DateAdd(hh,2,@StartTime) + 1 -- day interval
   AND DatePart(hh,hournum) >= DatePart(hh,@StartTime)
   AND DatePart(hh,hournum) <= DatePart(hh,@StartTime) + 1 -- hour interval
GROUP BY
   Convert(varchar(2),DatePart(hh,@StartTime))+' - '
      +Convert(varchar(2),DatePart(hh,@StartTime)+1)

 

[soklear]

Esquared - thank you for your response.

I have a main table (main_table) that contains about 13 rows and a temp table (tmptbla) that I pull data from to make the query faster.
This tmp table (tmptbla) table has 4 columns and about 1300 rows.

The columns are:

ClientID, DNIS, Start_Time and End_Time

A sample of the tmptbla data is below:

CLIENTID DNIS Start_Time End_Time
TAS1542 4156621542 11-12-03 11:34:32 11-12-03 11:35:00
TAS6742 4157773212 11-12-03 11:36:15 11-12-03 11:38:13
TAS9843 4159989843 11-12-03 11:36:43 11-12-03 11:45:04
<and so on until the next day where the columns continue>
TAS8876 4158878876 11-13-03 13:45:33 11-13-03 13:46:33
TAS9843 4159989843 11-13-03 13:46:29 11-13-03 14:02:00
TAS8603 4158658603 11-13-03 14:21:12 11-13-03 14:24:15

There would be about 300 more records from the above table with the ClientID, DNIS, Start_Time and End_time change varying on when the records where inserted on those two days. (between ClientID TAS9843 and TAS8876)

My code is:

-- Begin SQL
-- I. Declare Time Variables
Declare @HStr varchar(20)
Declare @StartTime DateTime
Declare @EndTime DateTime

-- II.Time Period Variables
set @StartTime = '11-13-2004 00:00'
set @EndTime = '11-14-2004 23:59'

-- III.Create Tables for Report

Select top 0 * into tmptbla
From main_table
insert into tmptbla
select * from main_table
where start_time > @starttime
and end_time < @endtime
and DNIS is NOT Null
--

Create table abobstbl(
Hr varchar(20),
ttlcls int null)


---
while dateadd(hour,1,@startTime) <= @EndTime
begin
set @HStr = convert(varchar,datepart(hour,@startTime))+' - '+convert(varchar,datepart(hour,@startTime)+1)
--
insert abobstbl
select @HStr, Count(DNIS)
from tmptasclhdr
where
Start_time >@StartTime
and start_time <dateadd(hour,1,@startTime)

set @StartTime = dateadd(hour,1,@StartTime)
end

SELECT abobstbl.HR as 'Period',
abobstbl.ttlcls as 'Total Calls'

FROM abobstbl

**The result set for a 24 hour period is (say if I chose the startdate and enddate to be between 11-13-04 00:00:00 and end on 11-13-04 23:59:59:**

Period Total Calls
0 - 1 28
1 - 2 25
2 - 3 25
3 - 4 19
4 - 5 12
5 - 6 7
6 - 7 16
7 - 8 72
8 - 9 127
9 - 10 136
10 - 11 121
11 - 12 115
12 - 13 155
13 - 14 140
14 - 15 128
15 - 16 114
16 - 17 102
17 - 18 88
18 - 19 53
19 - 20 67
20 - 21 56
21 - 22 41
22 - 23 57
--------------------------------
**The result set for a > 24 hour period is:The result set for a 48 hour period is (say if I chose the startdate and enddate to be between 11-13-04 00:00:00 and end on 11-14-04 23:59:59:****
Period Total Calls
0 - 1 28
1 - 2 25
2 - 3 25
3 - 4 19
4 - 5 12
5 - 6 7
6 - 7 16
7 - 8 72
8 - 9 127
9 - 10 136
10 - 11 121
11 - 12 115
12 - 13 155
13 - 14 140
14 - 15 128
15 - 16 114
16 - 17 102
17 - 18 88
18 - 19 53
19 - 20 67
20 - 21 56
21 - 22 41
22 - 23 57
23 - 24 36
0 - 1 31 <----- DAY 2 START, This is the problem I would like to have it added to the <> 24
hour result above to equal 59
1 - 2 50
2 - 3 20
3 - 4 13
4 - 5 14
5 - 6 6
6 - 7 17
7 - 8 35
8 - 9 69
9 - 10 86
10 - 11 63
11 - 12 79
12 - 13 67
13 - 14 62
14 - 15 75
15 - 16 77
16 - 17 48
17 - 18 71
18 - 19 76
19 - 20 44
20 - 21 35
21 - 22 23
22 - 23 29

So, what I ultimately want is to calculate the total calls between 1,2,3 or even 99 days into one result set of what a 24 hour selection would look like.

I hope this makes sense.

Let me know if you need any further info.

PS I didnt include a lot of this because I didnt want to overburden anyone and didnt know if there was a quick answer.

Thanks in advance -

Bob

 

[ESquared]

Bob,

You just need a group by clause to collapse the same hours on top of each other. See my last post.

If you still need help, let me know, and I'll see what I can do further.

 

[ESquared]

Oh, and you can remove some of the criteria I put in, I misunderstood what you wanted.

For future reference, it's usually good to give as much information as you can. Your last post is a good example, I think, for how to ask for detailed help in the future.