sql query relative time

wyntrblue · 1 December 2008 16:20

hiya folks,

the new job is prooving to be a challenge and now i have run into a problem i cannot solve on my own, so i turn to you wise people…

i have a database (sql 2000)
the important fields are “calltime” and “callduration”

i need to work out how meny calls occured between

1-dec-2008 13:15 and 1-dec-2008 13:30

that total time was longer than 60seconds but relative

eg

call comes in at 13:14 but takes 20mins that needs to show up in the results. any ideas what the query is gonna be coz im stumped

thanks loads for your insight

Spaceboy · 1 December 2008 16:36

scratch my first attempt… it’s been too long

erm, nested select statements comes to mind but the syntax eludes me - something like…

select <field1>,<field2> from
{
select * from <tablename> where calltime > 13:15 and calltime < 13:31;
}
where callduration > 60;

or something ??

wyntrblue · 1 December 2008 16:49

ok so i transposed what you have put in there spaceboy…
this is what i came up with

SELECT     calltime, callduration
FROM         {
                          SELECT     *
                           FROM         Calls
                           WHERE     calltime > 13 : 15 AND calltime < 13 : 31 }
WHERE     callduration > 60;

and i get this error
[microsoft][odbc sql server driver] syntax error or access violation

DoubleTop · 1 December 2008 16:52

put the time in quotes?

been to the pub so wait until tomorrow :wasted: is it bad that a pub lunch ended up with a bit of a crawl and tipsy by 4:30pm ?

Coffee time

DT.

PMM · 1 December 2008 16:55

I don’t know MS SQL but process is pretty much per spaceboy post as I believe the selection SQL is pretty much the same.

I have this feeling that Step2000 is your man here…

PMM · 1 December 2008 16:58

{ I am sure should be a (

wyntrblue · 1 December 2008 16:59

thanks for the suggestion DT i went on to try

SELECT     calltime, callduration
FROM         {
                          SELECT     *
                           FROM         Calls
                           WHERE     calltime > '01-dec-2008 13:15' AND calltime < '01-dec13-2008 13:31' }
WHERE     callduration > 60;

and got the same error

wyntrblue · 1 December 2008 17:05

right had a word with my manager in case he could fix the query… he agreed with you lot about the {} so now i have…

SELECT     callTime, callDuration
FROM         (SELECT     *
                       FROM          Calls
                       WHERE      calltime > '01-dec-2008 13:15' AND calltime < '01-dec13-2008 13:31') DERIVEDTBL
WHERE     (callDuration > 60)

which give me

“the conversion of the char data type to a datetime data type resulted in an out of range datetime”

any more awsome thoughts?

/edit when i run the query it does NOT have the “DERIVEDTBL” bit, it is added when i try and run the query. i have no idea what this is all about :S

PMM · 1 December 2008 17:19

01-dec13-2008 should be 01-dec-2008

wyntrblue · 1 December 2008 17:22

thats close it gives me a list of calls that are long enough but only after the 13:15 time, i also need to know for example

if a call was made at 13:10 and lasted for 20mins so ending at 13:30 i need to see that too…

any cunning ideas?

Damski · 1 December 2008 17:30

when dealing with dtg in sql you need to encase in # not ',so your code becomes

SELECT callTime, callDuration
FROM (SELECT *
FROM Calls
WHERE calltime > #01-dec-2008 13:15# AND calltime < #01-dec13-2008 13:31#) DERIVEDTBL
WHERE (callDuration > 60)

be aware though when messing with dtg in sql you can get messed about by locale settings if they are not all the same, I strugle here because the local machine is set to US and the user is set to UK.

hope that helps

bambamrubble · 1 December 2008 17:34

give this a whirl might help if not i will give it another shot :o)

SELECT *
FROM callTime, callDuration

WHERE (calltime > CONVERT(DATETIME,‘2008 - 12 - 01 13 : 15 : 00’))
AND (calltime < CONVERT(DATETIME,‘2008 - 12 - 01 13 : 31 : 00’)) AND
WHERE (callDuration > 60)

PMM · 1 December 2008 17:45

[QUOTE=wyntrblue;430577]thats close it gives me a list of calls that are long enough but only after the 13:15 time, i also need to know for example

if a call was made at 13:10 and lasted for 20mins so ending at 13:30 i need to see that too…

any cunning ideas?[/QUOTE]

Just follow it by a 2nd sub Query… Modify the time and the CallDuration i would suggest like the date you set a range for the CallDuration and maybe slap in a OrderBy Time.

If your not used to SQL have a look at this it should help you a bit… http://www.sql-tutorial.net/SQL-tutorial.asp

wyntrblue · 1 December 2008 18:34

thanks guys i will explore this tomorow when i eventualy get to work

Gee · 1 December 2008 19:11

to clarify…
i need to work out how meny calls occured between
1-dec-2008 13:15 and 1-dec-2008 13:30

Does this include calls both

calls starting [Question 1]
calls ending [Question 2]
within this period that match the > (or for that matter >= [Question 3] ) criteria?

What about calls that start at 13:14 and finish at 13:31? [Question 4]
i.e. both start and end times outwith your time frames but the call itself took place over the period being enquired about.
included or not?

You say you have
“calltime” and “callduration”
So if CALLTIME is your start time do you also have an end time field? [Question 5]

So lots of different solutions possible depending on what it is you need exactly.

Scream · 1 December 2008 21:41

[QUOTE=wyntrblue;430566]hiya folks,

the new job is prooving to be a challenge and now i have run into a problem i cannot solve on my own, so i turn to you wise people…

i have a database (sql 2000)
the important fields are “calltime” and “callduration”

i need to work out how meny calls occured between

1-dec-2008 13:15 and 1-dec-2008 13:30

that total time was longer than 60seconds but relative

eg

call comes in at 13:14 but takes 20mins that needs to show up in the results. any ideas what the query is gonna be coz im stumped

thanks loads for your insight :D[/QUOTE]

Can I just ask. If you are picking a query to search between 13:15 and 13:30, you are not going to pick up any calls that start at 13:14.

To pick up calls that are between those start times, you would need the following:

Assumptions: Table name is called: CALLS

select calltime, callduration, * from CALLS (nolock)
where calltime between ‘01-dec-2008 13:15’ and ‘01-dec-2008 13:30’

This will pick up the calls between those start times. If you want to pick up all the calls between those start times and anything over 60 seconds regardless of the start time, you’ll need something more like this:

select calltime, callduration, * from CALLS (nolock)
where ((calltime between ‘01-dec-2008 13:15’ and ‘01-dec-2008 13:30’)
or callduration > 60))

This will pick up all calls where the calltime is between 13:15 & 13:30 and all calls on any day where the duration was greater than 60 seconds.

This all depends on the setup of the fields in the database. Can you let me know how the duration field is set up, (ie, datetime/text/number?)

PM the information and I’ll sort it for you. (after all, I do sql on a daily basis for my job!)

STEP2000 · 1 December 2008 23:38

# always around date values. On the time since it is with the same string you could do a mid on the variable to pull that part out, and store in another point. The issues at hand would be pulling time vars out of a date string from what I see. If your looking for more of happen at X time then just do a count on those time points needed.

You could also try doing a string like Var < Minutes but > Minutes since I’m sure your looking for long calls. Getting the count for each day would then be easier.

I would need to think this one thru some more…have a head cold so sorry the medicine is not helping me focus…and DT I didn’t write any code today because of that…LOL!

Kevin · 2 December 2008 13:51

SELECT *
FROM tblTest
WHERE ((CallTime<=CONVERT(datetime,‘02/12/2008 13:15’,103))
AND (DATEADD(minute,CallDuration,CallTime)>=CONVERT(datetime,‘02/12/2008 13:15’,103))

OR (CallTime<=CONVERT(datetime,‘02/12/2008 13:30’,103))
AND (DATEADD(minute,CallDuration,CallTime)>=CONVERT(datetime,‘02/12/2008 13:30’,103))

OR (CallTime>=CONVERT(datetime,‘02/12/2008 13:15’,103))
AND (DATEADD(minute,CallDuration,CallTime)<=CONVERT(datetime,‘02/12/2008 13:30’,103)))

AND CallDuration > 1

Assumes CallDuration is in minutes.

STEP2000 · 2 December 2008 14:12

Select * from tbl where calltime >#00/00/0000#<#00/00/0000# and callduration>60

00/00/0000 place your date info in these ZEROs

Hope that’s what you need.

wyntrblue · 3 December 2008 10:27

sadly none of those have worked for me either… guess im gonna do it manualy using something way better than pc…

my brain and some paper lol

thanks anyway folks