Is this possible??? (1 Viewer)

I

imtheodore

Registered User.
Local time
Today, 09:32
Joined
Jan 20, 2007
Messages
74
Ok, maybe I overcomplicated it...

I have a table with a phone, start time and duration of call in 3 separate fields

Field 1 [PH17] being the phone
Field2 [2008-12-29 08:50:00] being the time
Field 3 [100] being the duration (minutes)

I need to know how many Phones are in use at any given time (0-24 hours in hour increments)

So I need to know how many phones were occupied at 0 thru 23 oclock
I can easily determine when the calls came in, but how do I know if they are still occupied the next hour and how do I show it?
 
Last edited:
P

Peter Reid

Registered User.
Local time
Today, 14:32
Joined
Dec 3, 2003
Messages
134
[posts quick reply] - edit, didn't realise this was in the SQL Server but have left answer regardless

I would build a query showing your 3 fields and for each hour, a field to calculate if the phone was in use for any part of that hour, something like;

iif(or(Hour(StartTime)=X,Hour(EndTime)=X,1,0)

where X is 0-23 (you may have to do a little bit more work to cover the durations that cover midnight).

You could then use the above query to give you a report of the counts in each hour per day
 
Last edited:
S

sidharth

Registered User.
Local time
Today, 06:32
Joined
Jan 19, 2009
Messages
19
imtheodore said:
I have a table with a phone, start time and duration of call in the following format:

PH17 2008-12-29 08:50:00 100

PH17 being the phone
2008-12-29 08:50:00 being the time
100 being the duration (minutes)

I need to know how many Phones are in use at any given time (0-24 hours in hour increments)
I tried a pivot table but it will only show WHEN each call started and cannot tell me when the phone was vacant

Date 0 1 2 3 4 5 6 7 8 9 101112131415161718192021222324
06/20/08 1 0 0 0 0 0 1 18 3 6 3 6 4 4 4 2 2 2 1 1 1 0 2 1
06/13/08 1 0 0 0 0 0 1 17 4 6 3 5 8 4 2 1 3 1 0 1 0 0 1 0
06/25/08 1 0 1 1 0 1 0 18 7 1 8 1 7 4 2 1 3 3 0 1 2 0 0 1
06/02/08 1 0 0 0 1 0 0 16 7 7 3 4 7 5 6 0 2 0 2 1 2 1 1 0
06/14/08 0 0 0 0 0 0 0 3 0 1 2 1 1 1 0 1 0 1 0 0 2 1 0 0
06/07/08 0 0 0 0 0 0 0 3 0 0 0 1 2 1 0 1 0 1 0 0 0 0 0 0
06/19/08 1 0 1 0 0 0 0 15 4 6 4 5 3 5 5 1 5 0 0 0 0 0 0 1
06/08/08 0 0 0 0 0 0 0 3 0 2 0 0 1 1 0 2 1 0 0 0 0 1 0 0
06/22/08 0 0 0 0 0 0 0 2 0 0 0 0 1 0 0 0 0 0 0 1 0 1 0 1
06/27/08 0 0 0 0 0 0 0 18 2 7 5 6 5 6 1 4 2 0 1 0 2 1 1 0
06/26/08 1 1 0 0 0 0 0 15 6 4 4 2 5 5 3 1 1 2 1 1 0 1 0 2
06/11/08 1 1 1 0 1 0 0 17 1 6 5 6 3 7 3 2 4 2 0 2 2 1 2 2
06/05/08 1 0 1 1 0 1 0 18 5 5 5 7 6 9 7 3 3 0 1 2 0 3 2 0
06/04/08 0 0 0 0 0 0 0 18 1 3 7 4 7 3 4 1 1 1 1 2 2 0 1 0
06/09/08 0 0 0 0 1 0 0 16 4 6 4 8 6 4 5 1 3 2 0 0 0 1 1 0
06/23/08 0 0 1 0 0 0 0 13 3 3 8 3 7 1 2 4 3 2 0 2 0 1 1 1
06/12/08 0 1 0 0 1 0 0 18 6 3 6 4 6 7 3 1 2 3 1 0 1 1 0 0
06/24/08 0 0 0 0 0 0 1 20 3 3 8 7 8 5 3 3 5 1 1 2 2 1 2 0
06/17/08 0 0 0 0 1 1 0 14 9 5 6 7 8 7 3 2 2 3 1 0 2 1 0 1
06/29/08 1 0 1 2 0 0 0 2 0 0 2 0 0 0 0 1 0 0 1 1 0 0 1 0
06/06/08 1 0 0 0 0 0 0 17 3 5 5 4 5 4 7 0 3 3 1 1 1 1 0 0
06/18/08 0 1 0 0 0 0 0 17 3 2 4 7 5 3 2 1 2 1 0 2 1 1 1 1
06/16/08 0 0 1 0 0 0 0 17 4 9 4 3 7 4 8 3 2 1 1 1 1 2 0 0
06/01/08 0 0 0 1 1 0 0 2 1 1 1 1 1 1 1 0 1 1 0 0 0 1 0 0
06/28/08 0 0 0 0 0 0 0 2 1 1 2 0 1 1 2 0 1 1 1 0 2 1 0 0
06/21/08 0 0 0 0 0 0 0 3 1 1 1 1 0 0 0 0 0 0 1 1 1 0 0 1
06/10/08 0 0 0 0 0 0 0 14 4 2 8 4 6 6 4 6 2 0 3 1 1 0 1 1
06/03/08 1 0 0 0 0 0 0 18 5 5 4 5 9 6 3 5 2 1 2 2 0 1 1 1
06/15/08 1 0 0 0 0 0 1 3 0 2 0 1 2 1 0 0 0 0 0 1 0 0 1 1
Click to expand...


I am really confused...:confused:
http://www.webdesigningcompany.net/
 
SQL_Hell

SQL_Hell

SQL Server DBA
Local time
Today, 14:32
Joined
Dec 4, 2003
Messages
1,360
Me too,

the reason no-one has answered is probably because nobody really understands the question and / or it is open to ambiguity.

Please try and explain the issue a little more clearly
 
I

imtheodore

Registered User.
Local time
Today, 09:32
Joined
Jan 20, 2007
Messages
74
Ok, maybe I overcomplicated it...

I have a table with a phone, start time and duration of call in 3 separate fields

Field 1 [PH17] being the phone
Field2 [2008-12-29 08:50:00] being the time
Field 3 [100] being the duration (minutes)

I need to know how many Phones are in use at any given time (0-24 hours in hour increments)

So I need to know how many phones were occupied at 0 thru 23 oclock
I can easily determine when the calls came in, but how do I know if they are still occupied the next hour and how do I show it?
 
I

imtheodore

Registered User.
Local time
Today, 09:32
Joined
Jan 20, 2007
Messages
74
Ok, I've gotten a bit further, I created a start and end time for each call.
I can now visually display the data, but I need help totaling the number of x's in each column (hour).

select phonedate,
case
When 0 Between onphone And offphone then 'x' else '' end as [0],
case
When 1 Between onphone And offphone then 'x' else '' end as [1],
case
When 2 Between onphone And offphone then 'x' else '' end as [2],
case
When 3 Between onphone And offphone then 'x' else '' end as [3],
case
When 4 Between onphone And offphone then 'x' else '' end as [4],
case
When 5 Between onphone And offphone then 'x' else '' end as [5],
case
When 6 Between onphone And offphone then 'x' else '' end as [6],
case
When 7 Between onphone And offphone then 'x' else '' end as [7],
case
When 8 Between onphone And offphone then 'x' else '' end as [8],
case
When 9 Between onphone And offphone then 'x' else '' end as [9],
case
When 10 Between onphone And offphone then 'x' else '' end as [10],
case
When 11 Between onphone And offphone then 'x' else '' end as [11],
case
When 12 Between onphone And offphone then 'x' else '' end as [12],
case
When 13 Between onphone And offphone then 'x' else '' end as [13],
case
When 14 Between onphone And offphone then 'x' else '' end as [14],
case
When 15 Between onphone And offphone then 'x' else '' end as [15],
case
When 16 Between onphone And offphone then 'x' else '' end as [16],
case
When 17 Between onphone And offphone then 'x' else '' end as [17],
case
When 18 Between onphone And offphone then 'x' else '' end as [18],
case
When 19 Between onphone And offphone then 'x' else '' end as [19],
case
When 20 Between onphone And offphone then 'x' else '' end as [20],
case
When 21 Between onphone And offphone then 'x' else '' end as [21],
case
When 22 Between onphone And offphone then 'x' else '' end as [22],
case
When 23 Between onphone And offphone then 'x' else '' end as [23]
Phonedata

RESULTS.....(Date, then Hour 0-23)

Dec 12 2008 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 x x x x x 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x x x 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x x x 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x x x 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x x x 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x x x x x x x 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 x x x x x x 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 x x x x x x x x 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 x 0 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 x 0 0 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 x x x x x x 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 x x x x x x x 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 x x x x x x 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 x 0 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 x x 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x x 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x 0 0 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x x x 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x 0 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x x x 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 x x 0 0
Dec 12 2008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

How do I count each column to get total of x's per hour?
 
P

Peter Reid

Registered User.
Local time
Today, 14:32
Joined
Dec 3, 2003
Messages
134
Change the 'x' to 1 (and '' to 0) and use sum, grouping on date?
 
SQL_Hell

SQL_Hell

SQL Server DBA
Local time
Today, 14:32
Joined
Dec 4, 2003
Messages
1,360
Where is the time in this data?

Not going to be possible to group by hour without having the time in the data
 
P

Peter Reid

Registered User.
Local time
Today, 14:32
Joined
Dec 3, 2003
Messages
134
SQL_Hell said:
Where is the time in this data?

Not going to be possible to group by hour without having the time in the data
Click to expand...

After the date field, there are 24 time calculations determining whether there was a call during the hour.

Something else may have to be done because the same extension may be used twice in one hour but I think the above calculation (when grouped on date) would show a count of 2 when (I'm guessing) it should only read 1
 
SQL_Hell

SQL_Hell

SQL Server DBA
Local time
Today, 14:32
Joined
Dec 4, 2003
Messages
1,360
Ok, I didn't read the other posts properly,

I'll go back to sleep ;-)
 
I

imtheodore

Registered User.
Local time
Today, 09:32
Joined
Jan 20, 2007
Messages
74
Peter Reid said:
After the date field, there are 24 time calculations determining whether there was a call during the hour.

Something else may have to be done because the same extension may be used twice in one hour but I think the above calculation (when grouped on date) would show a count of 2 when (I'm guessing) it should only read 1
Click to expand...

You are correct, I may have to make 48 time calculations based on each half hour...Hopefully I don't have to do 15 minute increments...

How can I subtract 30 minutes from a value?
 
SQL_Hell

SQL_Hell

SQL Server DBA
Local time
Today, 14:32
Joined
Dec 4, 2003
Messages
1,360
to subtract 30mins (example)

Code: 
DECLARE @now datetime
SET @now = GETDATE()
 
SELECT DATEADD(minute, -30, @now)
 
I

imtheodore

Registered User.
Local time
Today, 09:32
Joined
Jan 20, 2007
Messages
74
SQL_Hell said:
to subtract 30mins (example)

Code: 
DECLARE @now datetime
SET @now = GETDATE()
 
SELECT DATEADD(minute, -30, @now)
Click to expand...


Ok, this has brought on a a new problem...

What would be the best way to convert
2008-12-29 08:50:00
into a 24 hour format time without the date?

I'm using convert(varchar(2),onphone,8) as onphone
So I cannot DATEADD to a varchar
 
SQL_Hell

SQL_Hell

SQL Server DBA
Local time
Today, 14:32
Joined
Dec 4, 2003
Messages
1,360
In order to convert that time value into 24hr clock you need to have AM or PM in the data, otherwise how will your conversion know whether it's night or day?

Should be like this: 2008-12-29 08:50:00am

Are you sure it's not in 24hr format already? GETDATE() by default is 24hr format
 
I

imtheodore

Registered User.
Local time
Today, 09:32
Joined
Jan 20, 2007
Messages
74
Maybe the easiest way to ask for help is show a table of what I have and my results. I was able to make the table into 3 columns, Cdate, OnPhone,OffPhone.
This sample uses only one date.
I need to know who is on the phone and when each hour in a 24 hour format.
For example I need to display that at 7am there are 16 phones in use...

Sample Day.... shows the data for one day
Results... shows what I am currently getting (I need to be able to sum the columns)
 

Attachments

  • sample day.zip
    2.7 KB · Views: 148
  • Results.zip
    3.3 KB · Views: 145
You must log in or register to reply here.

Similar threads

K
Solved Report Group by Day with a Start time of 4:00:00am, End time 3:59:59AM the next Day
Replies
5
Views
358
kjbrack
K
mloucel
Solved Hide subform Does Not Work
2
Replies
20
Views
746
mloucel
mloucel
MattBaldry
Logged Times Challenge
Replies
9
Views
394
MattBaldry
MattBaldry
W
Solved Duplicate Items in Query
Replies
5
Views
334
wmix
W
N
Solved Issues with Pie Chart
Replies
4
Views
328
nashaz
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom