Calculating Business Days
I need to calculate the number of business days between a pair of arbitrary dates.Some scrounging around online delivered me this query,which basically does the trick:
Quote:SELECT
d1,
d2,
@dow1 := DAYOFWEEK(d1) AS dow1,
@dow2 := DAYOFWEEK(d2) AS dow2,
@days := DATEDIFF(d2,d1) AS Days,
@wknddays := 2 * FLOOR( @days / 7 ) +
if( @dow1 = 1 AND @dow2 > 1, 1,
if( @dow1 = 7 AND @dow2 = 1, 1,
if( @dow1 > 1 AND @dow1 > @dow2, 2,
if( @dow1 < 7 AND @dow2 = 7, 1, 0 )
)
)
) AS WkndDays,
@days - @wkndDays AS BizDays
FROM dates
ORDER BY d1,d2;
View Complete Forum Thread with Replies
Related Forum Messages:
Calculating Days To Excluding Weekends
I have a table(t2) with the following fields t2 Fields Datatypes StartDate datetime EndDate datetime #_of_days_Taken decimal A user will select start date and end date , So I want the system to automatically update #_of_days_Taken but it needs to excludes weekends only. I currenntly have it like this --> #_of_days_Taken =(EndDate - StartDate) If anyone knows how to do it please show me how to go about it.
View Replies !
Jdbc Over SSL, SAP Business Connector 4.6
I have set up a connection between SAP Business Connector 4.6 and MYSQL. I'm currently using jdbc driver com.mysql.jdbc.Driver (mysql-connector-java-3.0.16-ga-bin.jar). Using DB URL jdbc:mysql://<ip>/<database> the connection is working fine. So far so good. Now my problem: I need to set up a SSL connection but in MySQL Connector/J Documentation is stated: 'For SSL Support to work, you must have the following: A JDK that includes JSSE (Java Secure Sockets Extension), like JDK-1.4.1 or newer. SSL does not currently work with a JDK that you can add JSSE to, like JDK-1.2.x or JDK-1.3.x due to the following JSSE bug: http://developer.java.sun.com/developer/bugParade/bugs/4273544.html' My SAP business connector is running under jdk 1.3.0 How do I get SSL running?
View Replies !
Selecting Results From This Business Week
I have a table which has the column 'date_added'. I need a query which returns all of the results which were added in the current week. By this I dont mean today minus 7 days, I mean monday 00.00am to sunday 11.59pm. Is there a way of doing this? I've looked into WEEK() and YEARWEEK() but cant think of a way to do this using PHP. Heres what I've got (probably awfully wrong.. PHP mysql_query("SELECT * FROM table WHERE date_added BETWEEN WEEK(now(), 0) AND WEEK(??)");
View Replies !
How To Reset Yahoo Small Business MySQL Database
I am a new user of "Yahoo Small Business" web hosting service and I changed a lot of settings in my "MySQL Database" using "phpMyAdmin" utility during my learning period. Now, I want to reset my Yahoo Small Business MySQL Database to default settings or to reinstall it (I want it to be the same as it was newly activated). Note: I tried to repair my database with Yahoo's "Database Setup" page and the message "Database scheduled for repair" appeared for a few days but nothing happened. And, I can not login to my database using "phpMyAdmin" utility. If you can help me to reset my "Yahoo Small Business - MySQL Database" I will be very pleased.
View Replies !
Calculating Sum
Need help to calculate the total sum of an item sold on different days using SQL and display the result as Report to user using VB form
View Replies !
Calculating New Vs Old
How can I construct this query? I have a bunch of values in a table. I want to output the % of values that only occur once to the % of values that occur more than once. In other words, if I have 1, 2, 2, 2, 3, 4, 4, I would like to output: New numbers: 50% Existing numbers: 50% ..new numbers because the 1 and 3 only occurred once and existing numbers of 2 and 4 because they occurred more than once. %s are %s of individual numbers.
View Replies !
Calculating Average Age
I got this players table and I do want to list its average age... SELECT AVG(YEAR(SUBDATE(CURDATE(), TO_DAYS(birth)))) FROM players This works fine for me BUT, it doesn't on my server though it isn't 4.1.XX as it is on my computer at home.
View Replies !
Calculating A Percentage
I'm working on an adp file and I'm trying to do is calculate what percentage one figure is of another figure in a view. In access I would simply do it like this: [FirstField]/[SecondField]*100 But this doesn't seem to work. My exact code is as follows: SELECT Product, Quarter, TotalPlanProductSpend, TotalPlanSpend, TotalPlanProductSpend / TotalPlanSpend * 100 AS PercentOfTotal FROM dbo.Qry_RptPlanPRoductSetup
View Replies !
Calculating Median
I have to find out median amount of loan amount. so can i do it in a query. I know its possible by writing function. but still i am asking.I am new in MYSQL.I have searched for inbuild functions.i didn't found any median function.
View Replies !
Errors Calculating Vat
Why do I get the wrong results with the code below. iAmount = 16.84 iVatAmt = ((iAmount * 17.5) 100) (Returns 2.00 instead of 2.947) iTotalAmt = iAmount + iVatAmt (Should = 19.79 but comes back with 18.84) I know that the "" means interger division but if I use "/" I keep getting errors reported from PayPal that the Amount being passed to it is formatted incorrectly. How do I calculate and add the vat element to the iAmount keeping the correct decimal format.
View Replies !
Calculating A New Field
Lets say I have the following price margins: price------percent margin between $1 and $5---- 60 - 80 between $20 and $40---- 50 - 60 The following works as expected but how do I accomplish the above? select `c`.`eachprice` AS `eachprice`,`c`.`caseprice` AS `caseprice`,(`c`.`caseprice` - `c`.`eachprice`) AS `caseminuseach` from `UNFIW` `c` where ((`c`.`eachprice` between 12 and 24) or (`c`.`eachprice` between 1 and 4)) Something like this? select `c`.`eachprice` AS `eachprice`,`c`.`sku` AS `sku`, if `c`.`eachprice` between 1 and 2.5 ((`c`.`eachprice`*.60)+`c`.`eachprice`) AS `custommargin` and if `c`.`eachprice` between 2.5 and 5 ((`c`.`eachprice`*.80)+`c`.`eachprice`) AS `custommargin` from `UNFIW` `c`
View Replies !
Calculating Time
I have a small application which sends out alerts at specified times during a day. I have a table with 3 fields :- id int(11) alertime datetime sendbefore datetime These alerts will get automatically sent out depending on how many hours the user specified in the sendbefore field. For example if the alertime is set to '04/02/2006 15:00:00' and sendbefore is set to '03/02/2006 15:00:00' then that alert needs to go out 24 hours before. I need an SQL query or stored procedure which will return the alerts to be sent out during each hour of the day. And i am not sure how to do this. Hope it makes sense? I dont mind making the sendbefore field into an int so that i only need to put in number of hours before the alerts should go out.
View Replies !
Calculating Moving Difference
I got 2 records by individual with some fileds, one associates with the min date and the other one assocaites with max date. So lay out looks as follows: Key_ID | Dates_Min_Max Avg_Weight 1234 1/2/2004 12 1234 1/2/2006 24 I need to get the difference change between the weights for individual ids that is group by Key_ID and find the percent change. Some thing like this: (MAx_Row - Previos Row)/Previous Row * 100. How to do this? How do I get the previos row between 2 records if I order by the date?
View Replies !
Calculating Totals From Two Tables
I have three tables: invoices, invoicedetails, invoicepayments The fields are: invoices -------- InvoiceNo InvoiceDate CompanyNo invoicedetails -------------- InvoiceNo ProductNo Quantity UnitPrice invoicepayments --------------- InvoiceNo PaymentDate PaymentAmount For each row in invoices there will be 0 or more rows in invoicedetails and invoicepayments, with the InvoiceNo field linking everything together (i.e. one to many relationship between invoices and invoicedetails and invoicepayments). I need a query that will give me a list of invoices that still have money outstanding on them. To manually do this I would loop through the invoices table and for each InvoiceNo I would gather all the matching rows in invoicedetails and invoicepayments. To get the invoice total I would multiply Quantity by UnitPrice for each invoicedetails row. To get the total paid I would add up the PaymentAmount. Then I'd compare the invoice total and the payment total and if the payment total was less than the invoice total I'd know that there was still some money outstanding on that invoice. Now, how do I write a single query to do this? I using MySQL 4.0.20 (unfortunately because I don't control the server I can't upgrade to a version of MySQL that support subqueries). I'm guessing I'll need to use the SUM() function to add things up, and GROUP BY to group the invoicedetails and invoicepayments so I only get one row per invoice. I can get a total for each invoice by using the following query: SELECT invoices.InvoiceNo, SUM(Quantity * UnitPrice) AS InvTotal FROM invoices, invoicedetails WHERE invoices.InvoiceNo = invoicedetails.InvoiceNo GROUP BY invoices.InvoiceNo However I'm at a loss as to how I would modify this query to also total up the invoicepayments to give me a PaymentsTotal and then calculate the difference between the InvTotal and the PaymentsTotal to figure out if there is still money outstanding.
View Replies !
Calculating The Sum Of Various Sections Of A Table
I have a table that has two columns. the first column has product names and the second has product quantities. The product names are similar to the example below. 01BlueChair 01BlueTable 01GreenChair 01GreenTable 01RedChair 01RedTable 02BlueChair 02BlueTable 02GreenChair 02GreenTable 02RedChair 02RedTable Each product name has a number, a colour and an item type. What I need to do, is calculate the sum of the quantities for each number and product type. Eg, how many 01Chairs do we have? But I need to do that for 01Tables, 02Chairs and 02Tables as well, and output them in the same query results. 01Chair 12 01Table 15 02Chair 11 02Table 21 Some like the above. I can do it for one item, but not for all, for example here is the code I am using that outputs one result. Code: select stock-code sum(stk-trans-qty) from stock-movements where and stock-code like "01%Chair"
View Replies !
Calculating Time Difference
I have a task where I need to calculate the hours between two dates. However, they only want to calcute the time during Mon-Friday from 8-5. So if someone enters a question at 4:59PM on Friday and someone responds to them on Monday at 8:30. The user wants to see 31 minutes as the response time. I see that I have a function called dayname to get the Monday-Friday. However, I have no idea how I might actually use this to calculate the time lapsed. I need it to somehow ignore Saturday/Sunday.
View Replies !
Calculating Dates From Other Tables
below is some code we have created to enter in payment details for a customer.... INSERT into Payments values(Payments_seq.nextval, initcap('&Payment_Method'), '&Amount_Payable', Date_Due = (select date_of_order from orders where order_no = (date_of_order+7)); I am having trouble with the last line, I want the date due to be calculated from the date the order was made in another table (orders) and I want a week to be added to this so that in the field it will display (date ordered plus 7 days)...
View Replies !
Calculating Date Difference With PHP
I know nothing about MySQL (I'm more of a low level php guy). I'm building a reminder script that will remind the site admin to send out a newsletter. I want to send my reminder email email only if 30 days have passed since the last newsletter sending. I've already setup a database that will be updated with the current date when a newsletter is sent out. I found this function, which seams useful. PHP mysql> SELECT DATEDIFF(��-12-31 23:59:59',��-12-30'); -> 1 My guess is I could do something vaguelylike this: PHP $x = SELECT DATEDIFF(' CURRENT_DATE()',' the date in the dabase'); -> the difference between the 2 of them From there I could use php to say if $x is greater than 29 days, send the email. I was wondering if anyone could help me with the syntax / methodology of achieving this. If MySQL is not the right method, please let me know. I was told that MySQL is the "best" way of doing this.
View Replies !
Calculating Innodb Space ?
I have just started with some innodb. So first I say there was a file called ibdata1 of size 10Mb. So as I add on data it became 18Mb now. So I want to find out is how to exactly know what is size of my innodb database.
View Replies !
Calculating Experience In Mysql
I had a workers table which contains their name and date of joining. Now i want to list the name, date of joining and calculate their experience in a separate field exp and the exp should be in an order.
View Replies !
Calculating Disk Space
I was wondering, is there a way to calculate the overhead in disk space for a table that contains column types that are only regular ints and floats. The table type is MyISAM, but I'd also like to know for other tables how to find this overhead. Basically I'm looking for a formula. If that's possible?
View Replies !
Calculating Duration Between Specific Row Entries
Table: CREATE TABLE `log` ( `id` int(11) NOT NULL auto_increment, `session_key` int(11), `date` datetime, `level` varchar(30), `action` varchar(100) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=MyISAM; - Every user has a unique 'session_key' - Actions can in principle come in any order - A user can have several 'game started' - 'game ended' pairs with the same session_key. A typical log set of log-entries would look like: 1,1000, 2008-11-01 10:00:00,level2, game started 2,1001, 2008-11-01 10:01:00,level2, event3 3,1001, 2008-11-01 10:02:00,level2, event2 4,1002, 2008-11-01 10:02:30,level2, event2 5,1001, 2008-11-01 10:03:00,level2, game started 6,1001, 2008-11-01 10:04:00,level2, game ended 7,1000, 2008-11-01 10:05:00,level2, event3 8,1000, 2008-11-01 10:06:00,level2, game ended 9,1000, 2008-11-01 10:07:00,level2, game started 10,1000, 2008-11-01 10:08:00,level2, game ended The query should give the following result session_key, level, duration (hh:mm:ss) -------------------------------------- 1000,level2,00:06:00 1001,level2,00:01:00 1000,level2,00:01:00
View Replies !
Calculating A % Based On Date Range
I have a table called TransResult and I have 2 data fields in it. I want to get the percentages for the 2 possiable values in TransResult and I want this done by date, so I can go backwards to compare what was done in the past months to what is done in current month. This is what i have come up with so far, however my _total is giving me ALL records in the table not this months records, so my percentages are off, aside from the _total the values it is calculating are correct. SELECT TransResult, COUNT(*) AS HowMany, (COUNT(*) / _total ) * 100 AS Percent FROM tbltranslog, (SELECT COUNT(*) AS _total FROM tbltranslog) AS myTotal WHERE MONTH(Date) = MONTH(NOW()) +0 GROUP BY transresult
View Replies !
Calculating And Displating Voting Results
The results pages of myverdict.net have been the most difficult so far. It took me a month of research to find out how to write the sql query. I eventually found the answer in an added comment in mysql documentation. Here was my problem. I had a table with a vote column, the entries for which could be For, Against or Undecided. Counting the total votes was easy enough and by grouping I could return a count for each. However, I wanted to display results for every question in a particular category, on one page, using a repeating table, the results reading across the page. A repeating region only displays one row at a time and my simple grouped query would not do as it returned multiple rows. Here then, for the sql buffs out there is the query that worked. SELECT questions.question, COUNT(votes.vote) AS total, COUNT(votes.vote = ‘For’ OR NULL) AS col1, COUNT(votes.vote = ‘Against’ OR NULL) AS col2, COUNT(votes.vote = ‘Undecided’ OR NULL) AS col3 FROM questions, votes WHERE questions.questionID = votes.questionID GROUP BY questions.question You apparently need the ‘OR NULL’ or else it doesn’t work, I don’t know why. Anyway it was a simple matter to display col1, col2, col3 and total votes in the repeating region of my page.
View Replies !
Calculating Unused Time, Per Day Given Date Ranges
I've found a few potential solutions to this, but mostly they use Oracle Analytics syntax or SQL server specific extensions. I'm struggling to come up with something that works in Mysql. I've got a table of date ranges, let's call it "bookings" Code: roomid | startdate | enddate -------+------------------+----------------- 1 | 2008-02-03 13:00 | 2008-02-03 17:00 1 | 2008-02-03 18:00 | 2008-02-03 19:00 I'm trying to come up with a query that will give me the number of unused minutes for a room for a given day, such as: Code: roomid | date | unused -------+------------+------- 1 | 2008-02-02 | 1440 1 | 2008-02-03 | 1140 1 | 2008-02-04 | 1440......
View Replies !
Using Only Weekdays And Excluding Weekends When Calculating Dates
I need to figure out how many days are between certain dates excluding weekends. Is it possible to do this. I have tried searching google, but I guess I am not using the right keywords because the results I am getting back aren't giving me much help. I was thinking about setting up a table and putting in all weekend dates and then pulling in that data, but thought there might be an easier way.
View Replies !
Calculating Distance With Latitude/longitude In MySQL
I was trawling around the web and discovered the following gode on a website called http://ben.milleare.com and Ben is an english guy who has produced the following MySQL snippet which calculates the distance between two sets of Long Lat points. SELECT id,name,(((acos(sin(($lat*pi()/180)) * sin((latitude*pi()/180)) + cos(($lat*pi()/180)) * cos((latitude*pi()/180)) * cos((($lng - longitude)*pi()/180))))*180/pi())*60*1.1515) as distance FROM companies HAVING distance <= $miles ORDER BY distance ASC LIMIT xx $lat and $lng for the starting point as well as $miles for the max distance to search This works excellently. However what i want to do is twist this slightly to take a table where the users (whose home postcodes have been converted to Long Lat points ~ possibly by google) will have a MaxDistance they are prepared to travel to a venue for an event. I have changed the above statement to: SELECT id,user,longitude,latitude, (((acos(sin((51.75733*pi()/180)) * sin((latitude*pi()/180)) + cos((51.75733*pi()/180)) * cos((latitude*pi()/180)) * cos(((-0.341325 - longitude)*pi()/180))))*180/pi())*60*1.1515) as distance FROM tblusers HAVING distance <= tblusers.Maxdistance ORDER BY distance ASC LIMIT 5 the values 51.75733, and -0.341325 are the Long and Lat co-ordinates for an event location and the tblusers.Maxdistance is the column in TABLE tblusers which holds the max distance that they are willing to travel. This doesnt work. I am a noob to MySQL and therefore not sure if what i am trying to do is possible or not or if it is something that just needs the aplications of another clause. I have tried WHERE instead of HAVING but this the errors with 'distance' not being a valid column....
View Replies !
30 Days
I was wondering is it possible to update a value in a feild by executing a query for date values? Basically, I need store the CURRENT_DATE with 30 days added to it into my db. How would I do that? My table for test purpose is only one feild called date where the date is stored in the following format YEAR-MM-DD
View Replies !
Add 45 Days
i have a simple problems in query, i want to add current date(2006-08-17 00:00:00) + 45 days. = (2006-09-14 00:00:00) but it will give result Equivalents = 2051 that
View Replies !
Last 7 Days
I have a table with a date column (yyyy-mm-dd). I need to find all rows which are listed dates from Sunday to Saturday of the current week. I tried DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= datecol but that doesn't work...
View Replies !
Last 7 Days Query
I'm in the middle of moving a MS Access DB to a MySql backend. I have figured out about 90% of the problems I have faced, execpt for this one. I have 3 Queries, which pull records depending on a date range. (Today,Last 7 Days,Next 7 Days) The one I'm having problems with is the "Last 7 Days" Query. Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date()) When the tables are in access things work Fine, but when the tables are in MySql the Query doesn't work. Changing things a little and playing around with the criteria, it looks like MySql/MyODBC or something doesn't like the "-7" part, as when I make the # a non negitive value the query works.
View Replies !
Counting Days
How can I make a query such that in the "WHERE" part of my clause, I want to put something to the effect that, say, X days have elapsed since a given date. For example, suppose in my table, I have a boolean field and a date field. I want to create a query asking for those rows where X days have elapsed from the date field in a row and the boolean, say, is false. Do I need to create a third field, called, say "X" for how many days have elapsed between the date field and today, and update every row in the table every day?
View Replies !
Adding Days
How can i add 7 days to a date type? Ive tryed simple now() + 7 to get seven days from now but when i checked the database it was still just today.
View Replies !
Date > Now() - 360 Days
Im trying to select all records where a date field is greater than now minus one year, have tried a variety of different approaches none have worked. Im sure it should be simple but im struggling...
View Replies !
Days Between Start And End
this is the SQL i have so far: SELECT c.ID AS 'Course ID', date_format(c.start_date,'%d/%m/%Y') AS 'Start Date', date_format(c.end_date,'%d/%m/%Y') AS 'End Date' , t.description AS 'Course Description', FROM media_courses AS c INNER JOIN media_courses_type AS t ON t.ID = c.media_courses_type_ID GROUP BY c.ID ORDER BY c.start_date ASC
View Replies !
Birthdays In The Next 7 Days
Has any one got any suggestions on how to do this. I have a column date_of_birth that is a date field and I want to select all users who have a birthday in the next 7 days.
View Replies !
Query For Last 7 Days
i have a mysql table where a random number of entries can be inserted on a given day. for example during a week monday could have 3 entries, tuesday 0, wednesday 5, thrusday 4, and so on. think of it as like storing blog entries. i want to have a query select all the entries from the last seven days of entries but not including days when nothing was entered. so during a week of 7 days, if nothing was entered on say tuesday and friday, i would like to keep going back until i have a total of seven days with results. to sum it up quick: i want to return all entries for the last 7 days of entries but days without any entries don't count.
View Replies !
Records From Last 7 Days
I am building a site that will show racing results and want to be able to show the results from the last 7 days. The table is called 'results' structure of the table is: Date Course Time Horse Odds Result How can I just return the restuls in the last 7 days?
View Replies !
Query On Last 7 Days
I have a query below and I was wondering how could I query only the last 7 days from the time I ran the query? If its easier not to put in my query can you just show how it would be done.....
View Replies !
Data For Next 7 Days
I want to select records from database with a field holding a date in a date type field where the date record is between today and a weeks time i.e SELECT * From 'dbase' WHERE 'date' "is between today's date and today plus 7 days" ORDER by 'date' ASC I've tried many variations and all I have achieved is to output todays date!!! Can anyone help me with a simple query
View Replies !
Data From 'x' Days
I am having some problems writing a query. What I want to do is to pull data from 2 tables (a user table, and an awards table) and only have it show awards that were issued from the last 'x' Number of days. Lets say 7. Here is what I have so far: SELECT A1.user_name, A2.awarded_date FROM e107_user A1, e107_ranks_awarded A2 WHERE A1.user_id = A2.awarded_user_id This pulls all the information I need, but it pulls ALL dates. I am going to use this in a PHP module. What I need help with is to make it so it only lists awards from the past 7 days.
View Replies !
|
TRACKER
