Rolling Averages Each Date But Based On Previous X Days
I have been working on rolling averages and managed to get this working:
Code:....
View Complete Forum Thread with Replies
Related Forum Messages:
How To Insert New Records Based On Previous Record Values?
I have TABLE: year value It has for example, year, code, vlaue 2007, 33, 4883 2006, 33, 4883 2007, 32, 4883 2006, 32, 4883 2008, 31, 4883 2007, 31, 4883 2006, 31, 4883 I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%. My several attempts have led to this but which does not work. UPDATE TABLE a SET a.value = b.value * 1.05 WHERE b.year = 2007 AND code NOT IN (SELECT code FROM TABLE b WHERE year =2008 AND value !=0)
View Replies !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query? Example result for ProductX:
View Replies !
Date Query (how Do I Get Everything With A Date In 7 Days?)
the goal is a reminder email to people who have a workshop coming up in 7 days. So, workshop date is 2006-05-25. The reminder email should go out on 2006-05-18 I guess mysql query returns all records where (Date column - 7 days) = current date. Right? But what does teh QUERY look like? And how does it accomodate if today is 2006-05-28 and the upcoming workshop is 2006-06-04 (so month changes over the 7 day period).
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 !
Get How Many Days From Date?
I am trying to order some products by popularity, I have a product_date_added column and a product_stat column (how many hits each product has). I know I need to divide the hits by the days to get the hits per day to see how popular the product is. like this: SELECT (hits / days) AS hitrate, cart_product.* FROM cart_product ORDER BY hitrate DESC what I need to figure out is how to determine how many days it has been on there? how can I subtrat the product_date_added from the current date to get the amount of days it has been alive?
View Replies !
Date To Days Query
I need to update one of our mysql tables, which has about 60,000 entires and correct the amount of days remain on each data record. An example of one of the data entires is: memid days regdate expdate -------------------------------------- 625290 | 5 | 2003-07-15 | 2003-08-16 now the days should be 14 and not 5 days. I have been trying to update the table with a single SQL query but haven't come up with a way to do this.. I'm sure it's something simple but I can't seem to come up with it. What is the best way to accomplish this in a single query to update the entire table so all the "days" are accurate according to the "expdate", and change the "days" so they are correct?
View Replies !
Date Functions For Days
This time i looked on the mysql site first, found the answer but it doesnt work I am trying to get the days between two dates with the following: SELECT DATEDIFF(now(), date_joined) AS 'joined', DATEDIFF(now(), trial_end_date) AS 'trial' FROM JSPCustomers WHERE username like 'qwerty' And i get the folllowing error: Syntax error or access violation: You have an error in your SQL syntax near '(now(), date_joined) AS 'joined', DATEDIFF(now(), trial_end_date) AS 'trial' FRO' at line 1
View Replies !
Erron In Date + Days
i want to retrive data from mysql database. it will be simply revrive date date from database. Eg :- 2006-06-27 which that code below of date retrive code. <? echo $rsProdDetail["product_added_date"];?> but i am also retrive data date + display day. which i will that code. eg:- 2006-06-27 + 3( 3 is prod_dispdays) but it will give me result that 2009 only which i will this code. <? echo ($rsProdDetail["product_added_date"]+$rsProdDetail["prod_dispdays"]);?></font> <? } else { } ?>
View Replies !
Date :: Records From 7 Days
Im having a Problem querying a date field in Mysql. I need to return all recs that are between the Current Date and the date field , if the value date field is between 1 and seven days.
View Replies !
Date Range CURDATE() + 7 Days
I need to select data between two dates a table - this bit I can achieve no problem. However, my query is not that simple - the date rangeI need to select is always going to be between the current date and 7 days forward, i.e I want to select all data between and including the current date and 7 additional days. I presume I use the CURDATE() function to generate the current date, but how do I work out CURDATE + 7 days and then get the query to select all the data between and including those two dates?
View Replies !
Break A Date Range Into Days?
I have a table with a start_date and an end_date. Is there a way to get back a row per day? ie: if today is 2008-10-11, it would find a row with start_date 2008-10-10 end_date 2008-10-12 And would return 3 rows with a column that specifies the date? like this row is 2008-10-10, this row is 2008-10-11, this row is 2008-10-12?
View Replies !
Date Filter, Last 7 Days (seven) Range
I need to biuld a date filter , today, yesterday, last 7 days, current month, prev month. all is easy except selecting range for last 7 days, when I was using timestamp it's was a simple task, but now the I have 3 fields , day, month, year $month, $day, $year - today $last7days =Â Â date("Y/m/d", mktime(0,0,0,$month, $day-7, $year) ); $last7days = explode('/',$last7days); $list_reports['last7days']['products'] = "WHERE product_hits.year>={$last7days[0]} Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AND product_hits.year<={$year} Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AND product_hits.month>={$last7days[1]} Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AND product_hits.month<={$month} Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AND product_hits.day>={$last7days[2]} Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AND product_hits.day<={$day}"; Is there a better way to select rows for date range? i thought maybe I could use WHERE CONCAT()>start_date AND CONCAT()<end_date
View Replies !
DateDiff :: Difference In Days And Current Date
I need to work out the difference in days between values in the database and the current date. "No problem," thought I , "I'll just use the SQL DATEDIFF command." Heh! Well, the user interface I'm using didn't even recognise DATEDIFF as being a function, so I decided to visit the mySQL website. Their description of DATEDIFF is as follows: ------------------------ DATEDIFF(ARGUMENTS) TIMEDIFF(ARGUMENTS) [Rest of description to be added here] NEED EXAMPLE DATEDIFF() and TIMEDIFF() were added in MySQL 4.1.1. ------------------------
View Replies !
To Find Days Per Month In A Date Range
How to find days per month in a date range using MySQL query? For example I have a start date - 04/28/2007 (mm/dd/Y) and end date 05/04/2007 , I have to find the days in each month that comes in the above date range In the above example the days in April are 3 and in may the days are 5 (I am using MySQL database,Please find a query that outputs the above result)
View Replies !
Date Format :: Display Week Days
I am trying to output a list of dates from mysql database into a table. I have created a variable $date which outputs in the format yyyy-mm-dd as per the default mysql format. How can I change this to display the list of dates in the format day (eg monday), date, month, year.
View Replies !
Date Based Calculations
I'm looking for a way to create a query that generates a record for every day of the last year and using that date information for each day generates a unique database query (sub query?)for each day of the year. I could probably do this in php but I'd like to do it for performance reasons from mysql (or at least most of it). Does any one know of any good online tutorial/s that may help me build this sort of project? Or have some basic scripts that may be a good starting point?
View Replies !
SQL Selection Based On Date
I'm using this function / string to pull data based on yesterday: $tomorrow = date('Y-m-d',strtotime('tomorrow')); How can I pull data similarily based on "last month"?
View Replies !
Rolling Backups
I currently have windows task scheduler set up to run a batch file that runs mysqldump, to backup all of my local databases, it looks a bit like this: mysqldump --user=root --password=password --all-databases > "d:ackupsdump.sql" I want to modify so that the file name of the backup is based on todays date, and then get it to automatically delete the oldest backup file, so that I always have the last 5 days of backups.
View Replies !
Getting Counts Based On Non-specified Date Ranges
I have a table which has, among other fields, a date field. I want to get a count of records where certain criteria are met for, say, three days in a row. For example: NumWidgetsDate 11/1/2000 101/2/2000 201/3/2000 101/4/2000 151/5/2000 51/6/2000 I would like to know how many times 3 consecutive days have at least 10 widgets. In this case the answer is 1 because from 1/1/2000 to 1/3/2000 one day did not have at least 10 widgets, from 1/2/2000 to 1/4/2000 each day had at least 10 widgets, from 1/3/2000 to 1/5/2000, though all three days have at least 10 widgets, 1/3/2000 was already counted before so it should not count again, and from 1/4/2000 to 1/6/2000 one day did not have a least 10 widgets. Since 1/3/2000 was not counted before it would otherwise qualify in the next set.
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 !
Date Based Left Join
I have two tables that I want to join to find groups that do NOT have transactions for a particular date range. The left hand table holds the groups. The right hand table holds transactions which have an associated date. The link between the tables requires 2 fields, group_id and storecode, and there is a one-to-many releationship between groups and transactions. I essentially want the left join between these two queries: select g.name, g.group_id, g.storecode from groups g select t.group_id, t.storecode, count(t.trans_id) from transactions t where t.proc_date >= '2005-10-01' group by t.group_id, t.storecode Unfortunately, when I join them together the date seems to be interfering with the join. Naturally, you can't check the date of a transaction that doesn't exist, and there are transactions for the groups somewhere in the transactions table. Joined like this, they return everything I want except the groups with 0 transactions for the date range: select g.gname, g.group_id, g.storecode, count(t.trans_id) from groups g left join transactions t using (group_id, storecode) where t.proc_date >= '2005-10-01' or (t.group_id is null or t.storecode is null) group by t.group_id, t.storecode I could easily add a HAVING clause at the end to get the rows with count = 0, but it is only returning one zero row, when I know there should be about 100. Anyone have any suggestions, or know what I'm missing?
View Replies !
Showing Results Based On A Date Range
I have a calendar application, which stores dates as characters in its own column, ( was designed externally and using a now defunct openforge project) So y = year m = month and d = day. So the result would be ....
View Replies !
SQL To Get Lastest Record Based On Date Of Entry
Here's my table: Activity - Id (PK) - ProfileId (FK) - Date Each record represents a profile's activity in the CMS we're building (e.g. user updated profile, user uploaded photo, etc.) I'm only interested Id, ProfileId, and Date of the latest activity. How do I get that?
View Replies !
Deleting Entries Based On Date String
I'm trying to figure out how I can delete entries from a table based on a column that contains the date in string format i.e. 9/22/2008 month/day/year I want to delete all entries that are older then 60 days from today using the above column. I thought it might be something like this: delete from sequencer.parts where (SELECT DATE_SUB(curdate(), INTERVAL 60 DAY)>(SELECT STR_TO_DATE(packdate, '%m/%d/%Y') from sequencer.parts))
View Replies !
Select Record Based On 'change' Date
I currently have a database containing a table with the following fields: signid, sign_typ, changedate and time. The 'changedate' field is the date on which the star sign changes and becomes 'active' until the next 'changedate' in the table. I am trying to search this field by a date the user enters and display the 'sign_typ'.
View Replies !
Selecting Events Based On Various Date Criterias
I'm a little stuck at how to tackle this one efficiently. I'll try to explain concisely... I have a form to search for events. Form fields include Postcode Event name Venue name When (radios) -> today -> tomorrow -> this weekend OR instead of 'When' they can choose a specific date. In the events table there are a various columns including startdate (of type datetime). There is also a column for each day of the week that the event is repeated. For example, if the event is repeated on a monday then the column 'repeat_mo will' have a value of 1. My difficulty lies in creating a query to retrieve all the relevant results. For example, a search could include the following variables from the form: postcode -> YO14 7LX when -> tomorrow So I'd need to go to the events table and retrieve all rows that have a similar postcode, a start date that is tomorrow OR has been flagged as being repeated every wednesday. The postcode bit is fine and getting tomorrow date (with php) is fine. But how do I put it all together to also take into account the repeat day flags?
View Replies !
Updated Table Fields Rolling Back After Update
I'm having to correct some scripts written by a collegue who is presently not around. I had to update 2 fields with over 13000 rows. The update do work well but the problem is that somehow, the initial values in the fields do roll back in again - kind of auto updating to the former values itself. Any ideas what could be wrong? Any way round it?
View Replies !
Averages
Query first, question after. SELECT hour(call_start) AS hour, ( count(*) / (To_days(max(call_start))-TO_DAYS(min(call_start))) ) as num_calls FROM call_log AS c JOIN properties AS p ON (c.property_id = p.id) JOIN customers AS cust ON (p.customer_id = cust.id) WHERE 1=1 {$variable} AND call_start BETWEEN '{$start}' AND '{$end}' GROUP BY hour(call_start) I'm currently trying to find the average number of calls each hour for a certain time period that gets entered by the user through PHP. The {$variable} is a where clause based on what customer or property they picked. It works. So does the call start between statement. If the date range is 3 days (3/16/09-3/19/09), how will this find the average? If there are 5 calls between 8AM and 8:59AM but only on one day, will this divide the 5 calls by 1 or by 3?
View Replies !
Weighted Averages
Here's a pickle that has my department stumped. We have a table with the following fileds: 1. True APR 2. Annualized Unexpired Loan Value (A.U.L) Now what I need to do is to multiply the True APR by the A.U.L to get the weight. Then I need to total the weights and total the A.U.L. to come up with a weighted average. Now the first part is easy, but it's the totalling that I'm having difficulty with. Please tell me how to finish this statement: SELECT true_apr, aul, (true_apr * aul) as weight, (need total statements here) FROM table WHERE this = 'this'
View Replies !
Best Method For Showing Averages?
Hi all, I have a quick question about averages in Mysql. Most of the reports I have to produce, the client wants % of total next to the amount. So if you have a list of employees and the amount of hours they worked. They want the number of hours and then the % of those hours against the total. Currently I run 1 query to get the list of employees and their hours. Then I run another query to get the total of the hours. Then when I output the info using a table I divide the $row['Hours'] by $total_hours.
View Replies !
Displaying Sorted Averages
Given a table with columns LotID, ItemName, CostPerUnit and UnitsSold, how would I produce a sorted list of the average of ( CostPerUnit * UnitsSold ) for all items with the same ItemName. Example LotID ItemName CostPerUnit UnitsSold 0 Ford 100 15 1 Chevy 75 10 2 Toyota 70 20 3 Ford 95 25 4 Ford 110 10 5 Chevy 80 20 6 Toyota 65 15 I'm looking for an output of Ford 1658 Toyota 1187 Chevy 1175 Which is created by Ford -> (100*15+95*25+110*10)/3 Toyota -> (70*20+65*15)/2 Chevy -> (75*10+80*20)/2
View Replies !
COUNT WHERE (COUNT > X OR ABOVE) And WHERE DATE -3 Days Before
Example of Date of request 1. SELECT Count(tbl_eventbooked.idEvent), Sum(tbl_eventmain.StartDateEvent) FROM tbl_eventmain Left Join tbl_eventbooked ON tbl_eventbooked.idEvent = tbl_eventmain.id_Event WHERE tbl_eventmain.StartDateEvent <= '-2' GROUP BY tbl_eventbooked.UserEmail, tbl_eventbooked.FirstName Example Count of request 2. SELECT Count(tbl_eventbooked.idEvent) FROM tbl_eventmain Left Join tbl_eventbooked ON tbl_eventbooked.idEvent = tbl_eventmain.id_Event WHERE Count(tbl_eventbooked.idEvent) >= '15' (but how do you tell it 50% of row X)
View Replies !
Rolling Back A DB From Version 5 To Version 4
I've got a local MySQL DB with my MediaWiki data that was created in version 5.0.17, but I recently discovered that my webhost is running version 4.0.27, and when I tried to import the database as-is, I received an error. I'm looking for suggestions as to how I could roll my database back to the older version of MySQL, in case my webhost decides not to let me upgrade. I've considered using an intermediary, such as converting to MDB or CSV, but so far I haven't had much luck with finding a tool to do so without spending a pile o' cash.
View Replies !
Subtract Previous Value.
I'm trying to get one column of numbers to subtract from another column and to keep a running total. Column A is a dollar amount from an invoice. Column B starts out with an opening amount ($10,000) and I want to have Column A to subtract from that. Code: COLUMN A--------COLUMN B $0.00 $10,000 $1000 $9,000 $500 $8,500 $2000 $6,500
View Replies !
ID Of Previous Insert
Im thinking i need a transaction here. I want to insert a record into a table, which has an autoincrement field as the primary id. Then use this id to insert into another table. In the time between the first insert and then getting the id, couldnt there possibly be another user that causes an insert and hence the id the first user gets back isnt correct ?transactions to the rescue ?
View Replies !
Previous Record
I need to get a previous record from database - i need to retrieve records which differs from the previous one about 5s example: select eventDate,previousEventDate,eventDate-previousEventDate as delta from events having delta<5 order by eventDate; eventDate is normal column from table previousEventDate is an "alias" for the previous date ... how can I write it in SQL? Please help. I can use SELECT as subquery, but it would be probably really slow, the table will have 100000s maybe milions of records soon.
View Replies !
Previous Row Comparison
I have a table in this format: Column A | Column B 1 | 2 1 | 3 1 | 4 2 | 5 ..... ..... What I want to do is something like this: If in Column A there is a change in the number i.e. from the above table there were many 1s which transitioned into a 2. I want to record this change. I want to extract these two rows into another table. How can I make this possible?
View Replies !
Previous To Last With A Certain WHERE Condition
How do i fetch the previous row record with the same WHERE when the last record has only a lately date. for example field CConsorcio CRubro ImporteA FechaA 1 105 1000 2006-05-10 2 110 30 2006-05-12 5 105 300 2006-05-13 4 104 600 2006-05-14 2 103 800 2006-05-15 the WHERE CConsorcio=2 the last record: 2,103,800,2006-05-15 and i want to select the row: 2,110,30,2006-05-12
View Replies !
Get Next And Previous Records
Our website has a portfolio database which uses a column entitled 'list_order' to set the order of how we want the projects to display in a list. Each project has a link to click into it to see the full details of the project. Inside this details page we would like to add a View Previous Project and View Next Project link which would allow the user to navigate through our projects. The problem is that we are using an order set by 'list_order' and not an auto incremented column. How would I setup the Query to grab the previous and next rows no matter which project the user clicked on? For example, our database has these records: id - list_order 102 - 100 89 - 200 7 - 250 30 - 300 97 - 400 20 - 500 62 - 600 88 - 650 103 - 700 85 - 800 112 - 900 95 - 1000 So, if someone clicked into id 97, we'd need a query that would grab id numbers 30 and 20 for the previous and next id to create the links.
View Replies !
Select Previous And Next Id After Id=# ?
For a gallery I'm trying to query for a picture by its id and also get some images before and after that id (according to current sort) are there references like DOM previousNode or stuff like that to use in the WHERE statement? Or how could I do this? Currently I have: ($picId is a php variable) '(SELECT id, picUri, picUriSmall FROM pictures WHERE id < '.$picId.' ORDER BY id DESC LIMIT 2) UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id = '.$picId.') UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id > '.$picId.' LIMIT 2)' This works but doesn't seem to be the best way. I want to be able to have it working for other sorts than by id. And the two first images gotten by this query will be in reversed order (can be fixed in php). Is there a way to ask for my picIDs position in a query and then use another query to make the LIMIT 10,14 if my image was in position 12?
View Replies !
Paging With Only Next/previous
I'm running PHP + MySQL (innodb) and in some pages, the paging is slowing down the system a lot. Now I've seen on some sites that have paging implemented in an interesting way. They only let you go forwards and backwards one page at a time, and some don't even tell you how many results there are. For instance if you look at google search results, they only says "about XXX results". Plus they never actually let you jump to the last page. I'm thinking of implementing my system this way in certain pages as well, to cut down on the load times. However, I'm exactly sure how that cuts down the load. Can someone shed some light on this?
View Replies !
Determine Previous Month
basically I need to display a list of "unpaid" statements for the previous Month, no matter what day the search/request is made on. I know MySQL has the DATE_SUB function where I can subtract 1 Month from the current date, but will that subtract the date exactly 1 month to the day ? Or will it simply give me the Month as I need ? What happens if the search takes place on the 31st of a month (for example: July), yet the previous month only has 30 days (June). Will it roll back to the previous month correctly or think that the date is 31st June or try to roll back further until it finds another month which has 31 days ?
View Replies !
Previous, Current, And Next In One Query?
This situation has come up a couple times and I've wanted to know if there's a better way to do it. Given something unique (an ID number, a specific date/time, etc) I want to find the thing that comes before it and the one after it when sorted by something else. Oh, and the "something unique" isn't sequential - random intervals between each. Code: ....
View Replies !
|
TRACKER
