Work Out Daily Interest Rate Based On 2 Tables
Hi
I have the following DB structure
charges
cID [bigint]
cpID [bigint]
amount [decimal 3,2]
date_charge [datetime]
petitions
pID[bigint]
interestrate [decimal 3,2]
date_claim [datetime]
And need to do the following calculation:
($amount * ($date_claim - $date_charge) * ($interestrate / 36500))
The ($date_claim - $date_charge) section needs to work out the difference in days
Is something like that possible?
View Complete Forum Thread with Replies
Related Forum Messages:
Sql Statement To Return Daily Rate Between Start And End Dates?
I am working on a reservation application and am trying to figure out how best to store rate information in the database. Ideally a start and end date and the rate will be entered for that range. in how to select the rate for each day entered? It is possible there will be different rates for some of the days in the range queried. For example, in the database we have the following rate information: START END RATE 3/1/2009 3/10/2009 50 3/11/2009 3/20/2009 80 How do I return the rate for each day for a query which spans both ranges? If I searched for rates from 3/9/2009 through 3/12/2009 I should get the following results: 3/9/2009 - 50 3/10/2009 - 50 3/11/2009 - 80 3/12/2009 - 80
View Replies !
Rate To Tables
How do i find the read rate and write rates to a database table. The read rate should be the rate at which queries like select are processed. And the write rate should be the rate at which updates and inserts are processed.
View Replies !
Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this: WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00")) Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like: Day Min Max 2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59 2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68 where at this point i`m getting min=40 and max=68
View Replies !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
Loading Rate
I'm working on a project where we need to load several 10s of thousands of rows per second (roughly 50 bytes/row) into MySQL tables. We're using LOAD DATA INFILE, which works fine. The problem is, that we have to write this data from our application into ASCII files so load data infile can read them. The bottleneck is the writing of the data. We were thinking that if we could write the data in binary format, the writing might be quicker. Is there a way to load binary data into mysql tables with something akin to load data infile?
View Replies !
Implement 'Rate A Song' Functions
Im developing a multimedia-contents website... All audio/video files are stored in mySQL (file location/path. Im not using BLOB due to performance reason). I want to be able to rate a particular song/file. Based on my reading, this could be implemented using database approach. Every time user rate a song, the rate value in the database/table will be updated and some computation will take place and finally display the average rating.. Can anyone explain this approach as im totally stuck?? Any other way to perform similar actions??
View Replies !
Why Doesn't This Work? - SHOW TABLES NOT LIKE 'history%'
Well my question is simple - I want to exclude tables with a certain prefix using this: SHOW TABLES NOT LIKE 'history%' With the prefix obviously being 'history'. I cannot understand why I get a nasty syntax error - it works when I have just LIKE rather than NOT LIKE but I want all the tables that are NOT like it.
View Replies !
SELECT Based On 2 Tables
I'm writing some custom stuff to pull from a WordPress install I have one one of my servers. For one function I want to SELECT all the rows from one table based on criteria from that table and another table (they are related by the row ID of the first table). Here is the SQL I have now.... sql Code: Original - sql Code SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC It seems to be returning too many results. It's just returning all the rows from wp_posts that match the post_type and post_status criteria and ignoring the term_taxonomy_id (from wp_term_relationships) requirement.
View Replies !
Joining Tables Based On Condition
I want to have a field determine what table the field should be joint on. For example, I have a field that is one of eight different values (contacts, leads, accounts, ect...). I want to be able to join on the specific table specified by the field because this field determines what type the id field corresponds to.
View Replies !
Windows Based GUI To Manage Tables
What is best GUI to utilize to connect to my db to make edits to tables, etc...? I thought MYSQL Administrator could do it but I can't connect to my database on a shared server... if this is to the right tool.
View Replies !
Query Based On Data Of 3 Tables
this is what I've been trying to implement: I have, say, three tables. One is a user table, with id, username, etc Second is a question table with Question ID, Question, Category etc And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns. What I want to do is this: Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.
View Replies !
How To Query Multiple Tables Based On Value Difference
I 5 tables all with equal columns,but with different values. Per example: time_start,time_end,num_a,num_b,price etc. How can I retrieve the values of all columns `price` between tableX and tableY where time_start in table Y is higher as a datetime than time_start in X. So,the query is about finding the values of `price` between 2 dates in differnt tables.
View Replies !
Join Or Combine Two Tables Based On Unique Column
I use MySQL query browser and have made two tables in one database: First table: Test Column names: Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255)) Second table: Hashtest Column names: Filename (Varchar(255)), RowNumber (Integer), MD5 (Varchar(255)), SHA1 (Varchar(255)), Pad (Varchar(255)) Of both tables the column RowNumber is the primary key. Also, in both tables the column Pad is the same (the same content, not exactly in the same order) Now, I want to combine the two tables into one table, based on 'Pad' I suppose I have to use the Script function of MySQL query browser. Please explain to me what I have to do to combine the two tables into one table. This third table has this columns: Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255)), MD5 (Varchar(255)), SHA1 (Varchar(255))
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
Merge Daily Records
I am writing a POS program by Java with JDBC connecting to local MySQL Server. I need to merge daily sale records in each retail shops to the headquarter company. just need to know the general procedures how to implement the merging through telephone line. Is there any commands or tools in MySQL to ease merging records?
View Replies !
ِDaily Update
Many years ago I wrote a program that works with Microsoft Access. Last month I wrote that program with php and mysql again. I run this new program on server but I need to update my mysql database by Access database as source every day. Now I wana know is there any program to do this.(For example I find navicat but I don't know why it's append/update option is off)
View Replies !
Replication On Daily Basis?
I have an office computer that uses a MySQL database. What I need to set up is an online duplicate of that database so various data can be displayed via a new website. What is the best way to upload the database along with new records on a daily (or on demand) basis? I've been reading about replication but Im not sure this is what I'm after. Would a php script do it? It needs to be user friendly for my office worker she has limted computer skills.
View Replies !
Daily Update Of A Table
I've a MySQl db. I've a table "example", with 3 columns: code::int date:date status:bool (0 or 1) I need each day something checks my table and calculates difference between current date and date in the second column of my table. ....
View Replies !
Execute Script Daily?
I have a database set up as a membership directory. We would like to get an email if a member has not paid his/her dues in a year. Is it possible to set up something in mysql for it to check the database daily for people who's dues haven't been paid in a year and then execute a script that sends me an email with the names of those people? I know I could write a php script to do it, but it just needs to be executed automatically each day so i'm not sure how to make it happen.
View Replies !
Get Weekly Result From Daily
select id, sum(succ_calls)/sum totl_calls as succ_rate from the_database table where date >= 2008-11-01 and <= 2008-11-18 and id =2 and id =4 and id = 6 group by week ; id da_date succ_rte 2 2008-11-08 97.42 4 2008-11-15 97.80 6 2008-11-22 89.99 don't quote me on the succ rte accuracy it's an example ....
View Replies !
Daily Record Drop
would like to keep only 60 days worth of messages archived on the server at any time. This would be a rolling window. I have not found a way in OpenFire to tell it to enforce this retention policy. I am thinking of creating a MySQL stored procedure or function that drops all rows in the the ofconversation and ofmessagearchive tables which are older than this. I do not want to truncate them. The challenge comes in that the date format for those tables is bigint(20). I am not sure what MySQL uses for an epoch on Windows Server 2003. This would also have to be a nightly, recurring event.
View Replies !
Update Monthly Table With Daily Max / Min
I have 2 tables, they are daily and monthly tables. I want to update the monthly table with the max and min value of the daily table with the corresponding month. I figure out the following sql but it doesn't work because the group by function seems not supposed to be allowed. update MONTHLY as m SET m.M_HIGH=max(d.D_HIGH), m.M_LOW=min(d.D_LOW) WHERE m.MONTH=(select MONTH from DAILY as d GROUP BY d.MONTH); How can I make it in 1 sql statement ?
View Replies !
Stored Procedure For Creating Daily Report
how i can create a stored procedure that do the select into the OUTFILE for a day or two from the time of excution. the aim is to generate a report to the shared folder say /home/reports/ The created file should have the unixtime to identify itself
View Replies !
Using PhpMyADMIN To Write Daily Backup Stored Procedure
as stated in the title... i have never written a stored procedure before. I would like to write a stored procedure which I can execute in phpMyAdmin. The procedure would hopefully work as so. Everyday at midnight, backup the entire database and write it to a text(sql) file in a folder on the website directory. Hopefully only keep 2 weeks worth on server and delete everything previous to that. Is this hard to do? I can't find anything online which can do this.
View Replies !
Bug Work
I've got this bug http://bugs.mysql.com/bug.php?id=27861. I know they've made a fix and it's going to come out in the next version but is there any way I can fix this myself?
View Replies !
How Does Sql Work Exactly?
Well what exactly does sql do when you create a table for like a login (user) What does the tables and stuff do. Do they just create like a library to store that users information so they can log back in to site or w/e....
View Replies !
Subselect Does Not Work
i am trying to remove values from a list menu if the join table d= oesnt have keys when a key is selected for instance: locations locationID locations_join locationID shotlistID SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo= cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN= (select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B= Y l.location ASC. so when shotlistID is selected all the keys from the locations_join joined = to the shotlistID would be remove from the locations list please help, i'm = trying to do this in one query saving from getting all the keys into an arr= ay then checking if the values arent in the array when generating the list.
View Replies !
Date_add Does Not Work
I'm using mySQL 5.0, with a database of UNIX timestamps. Can someone explain this to me? This query works great: SELECT from_unixtime( 1132277574 ) But this one breaks: SELECT date_add(from_unixtime( 1132277574 ), interval 1 week) Telling me, oh-so-helpfully, that the error is near "'week)'". (It doesn't work when I use "1 weeks" either.)Does the from_unixtime simply not work with the date_add function, or is there some arcanity to this syntax that I'm missing?
View Replies !
Getting Log File To Work
I am putting log = "C:Program FilesMySQLMySQL Server 5.0sql.txt" into my .ini files and mysql wont start. I cant work out the syntax to tell mysql to ouput all the queries to a log file.
View Replies !
How Does Synchronizing Work?
I have a question; currently we want to run our central database from two different locations as opposed to one currently. I am trying to figure out how to synchronize the two locations. My question is: if two (or more) locations create new clients locally, each location will thus create a new client-numbers on their own active operational database. This opens the possibility that each locations will create identical client-numbers; of course being different clients altogether.
View Replies !
Mysql_connect Does Not Work
I have searched through the forum and tried the suggestions that have been made for this issue but I am about ready to give up! I have installed Apache 2.0.52, PHP 5.0.3, and MySQL 4.0.23 on Windows XP. I uncommented extension=php_mysql.dll in the php.ini file and copied libmysql.dll into both C:WindowsSystem32 and C:WindowsSystem. My phpinfo.php test works fine so I know PHP and Apache are working together. I do not see any MySQL references on that entire page. From previous posts I know I should. Needless to say, mysql_connect(...) does not work, it gives an undefined function error. I apologize if this should have been posted in the PHP forum instead. If it is a PHP issue, I will repost this there.
View Replies !
Work In MySql
This query works fine in SQL Server, will it work in MySql? SELECT a.total, b.browsertotal, b.browser, (b.browsertotal / a.total) * 100 AS browserpercent FROM (SELECT Count(id) As total FROM MyTable) a, (SELECT count(id) as browsertotal, browser FROM MyTable GROUP BY browser) b I dont have MySQL and I am trying to help someone out.
View Replies !
Getting PHP & MySQL To Work Together
I am trying to get PHP to write into MySQL database. Right now it does not seem that it is doing such, MySQL doesnt appear when I bring up the PHPInfo() page. I have removed semi-colons before the Mysql.dll extension in php.ini, and I have made sure that PHP is in the windows PATH. What else do I need to do to get this stuff running?
View Replies !
Sql Statement Doesn't Work
When i was using a previous version of mysql (3.something) this sql statement worked fine. strSQL = "SELECT COUNT(*) as COUNTER FROM prayerrequest where dateexpires <= '" & sDate & "'" sDate equals todays date. But after installing the new version (4.1) of mysql this statement no longer works. I went as far as stepping through all dates and comparing them and it wouldn't work. "1/11/2005" from the database did not equal sDate which was "1/11/2005".. The only way I could get it to work was to use the asp/vb function FormatDateTime on dateexpires and then compare it with sDate. Does mysql not return a different var type? Is there anyway I can convert dateexpires in the above sql statement so I can compare it with sDate?
View Replies !
Cannot Get 3 Where Statement To Work
I am having a problem with MySQL 5. I cannot get 3 where statements to work with my database. Any two of the three work fine and return records as they are supposed to but when I add a third clause (all clauses are AND) I get absolutely no records at all and no error message. I don't understand what could be causing this and can use any help offered. SELECT this, that, otherthing FROM myTable WHERE something = "this" AND somethingelse = "that" AND yetsomethingelse > 0 ORDER BY RAND() LIMIT 3;
View Replies !
Join Doesn't Seem Work
I have some issues with a simple query, but I just can't figure out why the query doesn't return expected results. Here is the situation: I have a table look like this: TRANSACTION(UNIQUE_ID(PK), CUSTOMER_ID, EMP_ID, TRANS_TYPE, TRANS_DATE, POSTED_DATE, AMOUNT_CHARGED, AMOUNT_PAID, TRANS_LOC, TRANS_ID_CHARGE, TRANS_ID_PAYMENT) Some rules for the data in the table: - The TRANS_TYPE indicates if it is a charge or payment(C charge, P for payment). For a payment, there is a unique TRANS_ID_PAYMENT, and for same transaction, the charge record holds same TRANS ID in the TRANS_ID_CHARGE field. - The TRANS_LOC is for location where the charge happened. But for a payment record, this field is null. which means, payment records and charge records can't be linked together through location. - TRANS_DATE is the date the transaction happened. For a payment record, this field is also null. - A charge record and a payment record for same transaction usually have different POSTED_DATE since they are posted most likely on different days. - AMOUNT_PAID could be less than AMOUNT_CHARGED for same transaction since customers can have an installment payment plan. Here is my query: SELECT DISTINCT A.CUSTOMER_ID, A.AMOUNT_PAID FROM TRANSACTION A WHERE A.TRANS_TYPE = 'C' AND A.TRANS_DATE BETWEEN '2007-01-01' AND '2007-09-30 AND A.LOC_ID = 1 UNION ALL SELECT DISTINCT A.CUSTOMER_ID, A.AMOUNT_PAID FROM TRANSACTION A, TRANSACTION B WHERE A.TRANS_TYPE = 'P' AND B.TRANS_TYPE = 'C' AND B.TRANS_DATE BETWEEN '2007-01-01' AND '2007-09-30 AND B.LOC_ID = 1 AND A.CUSTMER_ID = B.CUSTOMER_ID AND A.EMP_ID = B.EMP_ID AND A.TRANS_ID_PAYMENT = B.TRANS_ID_CHARGE The purpose of this query is to pair all the charge and payment records for the same transaction that happened between a certain period of time. But the results show that there are some dangling payment records that are not linked to any charge records.
View Replies !
Min Max Does Not Work For Me For Timestamp
I have such a table: id timestamp value person regardless or entering this SELECT id, MIN(timestamp),value FROM table GROUP BY person or this SELECT id, MAX(timestamp),value FROM table GROUP BY person The timestamp value may be properly pulled, yet, the value field is always the same. Meaning, not the value associated wuth the MIN or MAX timestamp value is shown, but the very first for that person. How is that possible?
View Replies !
Why Does This Grant All Does Not Work?
I've created a new user like this: create user myuser identified by 'xxxxx'; commit; Now I'd like to give him all privileges to a certain database: grant all on db210216204.* to 'myuser'@localhost; Now I get an error message: ERROR 1133 (42000): Can't find any matching row in the user table What am I doing wrong? The database exists: mysql> show databases -> ; +--------------------+ | Database | +--------------------+ | information_schema | | baka | | db210216204 | | mysql | | test | +--------------------+ 5 rows in set (0.13 sec)
View Replies !
LD_PRELOAD Doesn't Work
LD_PRELOAD doesn't seem to work with /usr/sbin/mysqld. It works for other programs, but when it comes to mysqld, I always get the same error saying that the object cannot be preloaded, so ignored. I checked that mysqld is not a setuid binary. Is mysqld doing something funny or did I miss anything?
View Replies !
Auto_Increment Doesn't Work Any More!
After upgrading from 5.0.11.beta to 5.0.15 on a Windows 32 system, I can't use the auto_increment feature any more; for example, phpMyAdmin gives the following error message: "#1366 - Incorrect integer value: '' for column 'ID' at row 1". Other php clients also (WordPress, MediaWiki...) don't work.
View Replies !
Logging Doesn't Work
I have WinXP, and MySQL 5.0.18 running as a service. I followed the instructions for setting up general query log file (mysqld --log), and the file was created (Nebo.log), but it does not display any logs. This is all I have after executing a few queries: Nebo.log mysqld, Version: 5.0.18-log. started with: Tcp port: 3306 Unix socket: (null) Time Id Command Argument Can anyone offer any help?
View Replies !
WHERE EXISTS Does Not Work.
SELECT id,name FROM tcesports_groups WHERE EXISTS (SELECT * FROM tcesports_teams WHERE tcesports_teams.gid=tcesports_groups.id); i get this: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT * FROM tcesports_teams WHERE tcesports_teams.gid Strucuture of the both tables: tcesports_teams ------------------- gid smallint(6) img varchar(100) mod mediumint(9) pos smallint(6) tcesports_groups ------------------- id smallint(5) name varchar(100) currently the entries for id or gid are just 2digits long (1-99) so im a bit confused what throws this error. im using mysql 4.0 branch
View Replies !
Getting MySQL To Work..
I downloaded version 4.1.7, opened the manual, followed the instructions, and installed it. Then, when I opened up the "MySQL Server Instance Config Wizard", I set it all up. I press the "Configure" button, and it runs up the point where it creates the Service. Then, it gives this message"Could not start the service MySQL. Error: 0". I've ran a Google search on the error message, but have found nothing. I'm running Windows XP. If there's any more information you need about my computer setup, go ahead and ask.
View Replies !
Difference Of Work
My provider has passed on version 4.1.7 and the inquiry of the following kind has ceased to work for me: SELECT goods.id, goods.name FROM goods LEFT JOIN category ON goods.id = category.goods AND category.razdel=2 WHERE category.goods IS NULL. Before all worked correctly, on the version 3.x, what could exchange? Where a problem?
View Replies !
Sentence Doesn't Work
I'm using mysql 4.0.21 and Toad to run this query. The message i'm receiving is that there is a Syntax error, where? select a.id_opcion, a.nombre, a.campo, a.visible, b.id_usuario, if(b.ancho is null, 50, b.ancho) as ancho, b.posicion from det_rmen as a left join (select * from rmen_usu where id_usuario = 'generico') as b on (a.id_opcion = b.id_opcion and a.campo = b.campo) where (b.id_usuario='generico') or (b.id_usuario is NULL) order by id_opcion, posicion;
View Replies !
|
TRACKER
