Anyone GOOD with SQL?

General Banter for everyone
Kink
Peon
Posts: 20
Joined: 27 Jun 2006, 11:13
Class:

Anyone GOOD with SQL?

Postby Kink » 25 Aug 2006, 12:17

I need to come up with some funky statements, could use a hand. Should be quite simple for someone who is experienced with it =)
Rogue Class Master.

Kink
Peon
Posts: 20
Joined: 27 Jun 2006, 11:13
Class:

Postby Kink » 04 Sep 2006, 11:18

oh come on now.... must be someone here who knows SQL at a half decent level =)
Rogue Class Master.

Agrippa
Ogre
Posts: 299
Joined: 02 Aug 2006, 01:22
Class:
Location: on Ashtrai's nerves

Postby Agrippa » 04 Sep 2006, 13:33

What do you need to do?

Kink
Peon
Posts: 20
Joined: 27 Jun 2006, 11:13
Class:

Postby Kink » 04 Sep 2006, 13:56

Ok, simplified version is:

Table with 3 Rows. RecStartTime, RecStopTime, SpeechFileLen

Table name= ThroughPutData
Rcgstart = the recognition start time
Rcgstop = the recognition stop time
SoundFileLen = the length of the sound files =)

I want to write a statement that will add the sum of the sound file lengths between a specified amount of time. I have the following statement:

SELECT SUM(SpeechFileLen) AS SumSpeechFileLen
FROM ThruPutData
WHERE (RecStartTime >= 1156845634) AND (RecStopTime <= 1156849234)


The length of time I am looking for is 1 hour. The values for RecStartTime are in seconds from something like the year 1950 or some freaky stuff like that. Either way the numbers themselves do not matter, only that they stop time value is 1 hour after the start time (so start time+3600=stop time value)

Only problem is, executing the above statement gives me the result of SumSpeechFileLen=24hours+ which is impossible.


The whole idea of this is to work out how many seconds of recorded speech the system can recognise in 1hour.
Rogue Class Master.

Agrippa
Ogre
Posts: 299
Joined: 02 Aug 2006, 01:22
Class:
Location: on Ashtrai's nerves

Postby Agrippa » 04 Sep 2006, 14:30

I had somebody at work here check it out. He says your statement should work. He asks if you are sure your data is correct.

Kink
Peon
Posts: 20
Joined: 27 Jun 2006, 11:13
Class:

Postby Kink » 04 Sep 2006, 14:37

thats whats I am checking now.

Problem is, some of the sound files are over 40minutes in length and take over 2 hours to recognize. But from what I can tell, this should lower my results as these files will not be considered in any statement that requires the start and stop time to be both within a specified 1 hour period.

So I am utterly confused atm. Ty for checking the statement tho, I will keep checking the data and see if I can find an error somewhere,
Rogue Class Master.

Agrippa
Ogre
Posts: 299
Joined: 02 Aug 2006, 01:22
Class:
Location: on Ashtrai's nerves

Postby Agrippa » 04 Sep 2006, 14:45

Do you have an example record? Maybe it's something with the time conversion.

Kyria
Newbie
Posts: 2
Joined: 27 Jun 2006, 19:25
Class:

Postby Kyria » 04 Sep 2006, 15:03

zomg
Kyria 60 Hunter - Class Master

Image

Kink
Peon
Posts: 20
Joined: 27 Jun 2006, 11:13
Class:

Postby Kink » 04 Sep 2006, 15:49

The time conversion is the seconds from 1970 (thats the recinitstart and recinitstop values). The speechfile sound length is in ms. I worked out the issue though. Everything works fine. There is one calculation that was confusing me because somewhere in the system it is being worked out totally wrong =) and comparing that value with my results showed me something was wrong. But foolishly I assumed it was my mistake not someone elses /sigh =)

Ty for the confirmation tho =)

And Schatzi, isnt my work FUN =)
Rogue Class Master.


Return to “Public General”

Who is online

Users browsing this forum: No registered users and 3 guests

cron