time overlap = total
Posted by ti_nhatrang, 02-07-2011, 10:21 AM Hi guys, I got this table with call logs start and end date and time. I just basically want to figure out how many concurrent calls went on when a certain was dialed. I just don't know how to do the overlapping checking and stuff. here's what I have so far: the code above didn't get me anywhere... so please guide me. Thanks in advance.
Posted by pmwebster, 02-07-2011, 04:38 PM Guessing you're using mysql, you should use the 'between' function -- http://dev.mysql.com/doc/refman/5.0/...erator_between select * from reports where time = yesterday and dialed between x and y
Posted by coderiser, 02-07-2011, 05:13 PM shouldn't that be "select * from reports where time BETWEEN StartTime AND EndTime and dialed = '121334'"
Posted by pmwebster, 02-07-2011, 05:37 PM ah, good point coderiser. For some reason I was thinking dialed was a timestamp of some sort
Posted by ti_nhatrang, 02-09-2011, 03:16 AM Ok, thanks all for helping, still working on this, but on a side now... how would I pull all records from this day now and last month, so 2/8/2011 all the way to 1/8/2011... how do i go about doing this? Here's what I have: I just don't know how to put in the day 8th in there, please help.
Posted by ti_nhatrang, 02-09-2011, 03:18 AM i honestly want to put in the years too if it maters...
Posted by ti_nhatrang, 02-09-2011, 03:25 AM so basically i want to get the sum of the minutes each day and gauge it before another billing cycle of the 8th coming around...
Posted by ti_nhatrang, 02-09-2011, 03:59 AM The problem I'm confused about is that for example this month the 8th has already passed, then I would want it to do the 8th of the current month, all the way to the next month of the 8th...
Posted by nelsondawson, 02-09-2011, 07:02 AM select sum(minutes) from calls where call_start between CURDATE() AND "DATE Required"
Posted by nelsondawson, 02-09-2011, 07:04 AM You may also try DATE_SUB(CURDATE(), Interval number_of_Days)
Posted by ti_nhatrang, 02-10-2011, 10:18 AM ok, let me try those, in the meantime, what am i doing wrong here? I tried doing NOT LIKE, but it didn't return anything... please help...
Posted by nelsondawson, 02-10-2011, 10:29 AM Would really help to get a structure of the table you are trying to retrieve from
Posted by nelsondawson, 02-10-2011, 11:17 AM just tried it something my end not in does not work the way expected you want want to use not in with a combination of like in sub query's
Posted by ti_nhatrang, 02-10-2011, 07:56 PM I'm just trying to pull an array or a list of WHERE NOT IN and it doesn't seem to work
Posted by ti_nhatrang, 02-11-2011, 06:45 AM how do i do multiple LIKES?
Posted by pmwebster, 02-11-2011, 03:03 PM You can use multiple likes in different ways, where x LIKE '%test%' and z LIKE '%test%' where x Like y OR z... It has a lot to do with how you wrap them (algebraically). Here's a better explanation -- http://www.w3schools.com/sql/sql_and_or.asp
Posted by foobic, 02-11-2011, 09:27 PM A change in table structure would really help you here, particularly if you want the application to be scalable. Can you store the area codes in another table and just use a key to that table in this one? ie.
Posted by ti_nhatrang, 02-17-2011, 08:50 PM I tried the below and it doesn't seem to work, can you please help? perhaps I will be running this script via cronjob on the 8th of each month, and have it look back exactly 1 month... Not sure if this is a good idea, but it seems pretty logical and easy on my inexperience programming part. Here's what i have thus far and it doesn't seem to work:
Posted by coderiser, 02-18-2011, 12:38 PM for the multiple likes you will have to use a union to get both at once SELECT * FROM users WHERE name LIKE '%1%' UNION SELECT * FROM products WHERE name LIKE '%2%'
Posted by ti_nhatrang, 02-18-2011, 03:50 PM I did not know this or even understood it, but thanks, I will give this a try. Any opinions on use REGEX ('likes|likes2|likes3') ?
Posted by ti_nhatrang, 02-20-2011, 10:08 AM The above doesn't work right. Can somebody please show me the correct way of doing multiple Wheres of the same columns?
Posted by ti_nhatrang, 02-20-2011, 10:14 AM Ok, finally going somewhere, but for some reason something is not right because sometimes even my adding to the column doesn't even do it. Please help me, and I thank you in advance: [/php]