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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Selecting Based On Time Range
I'm writing a series of queries on a transactions table. I'd like to find all transactions from: Today Yesterday Last Week Last Month This Year The table contains a datetime field called txn_timestamp. What is the appropriate code to filter the table for each time window? SELECT transaction_id FROM transactions WHERE <?>
View Replies !
View Related
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 !
View Related
Date Range :: Month, Year And Date In Separate Columns
I have a database where the date is stored in 3 different collums month, day, year. A am trying to retrieve data by a date range something like "1-1-2005" - "1-1-2007" or something but had quite some difficulty in doing it but I found a way and I am not sure that it is the best way but it appears to work well. Let me know what you think. "SELECT * FROM database " + "WHERE datey*365+DAYOFYEAR(CONCAT(datey, '-', datem, '-', dated)) " + "BETWEEN " + StartDate + " AND " + "EndDate + " " + "ORDER BY datey, datem, dated;" NOTE: StartDate and EndDate use the same formula of (year*365)+dayofyear
View Replies !
View Related
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 !
View Related
Date Range On Date Field
I have a table that has birthday dates stored as a DATE field. How would I get all the rows returned based on a min years old and a max years old...Let's say... My form has.... Display Rows by birthdate in years.... Show between (23 years old) AND (36 years old) from the current month/day/year
View Replies !
View Related
Sort By Date (date Range)
I just have a question thats probly simple for you SQL pros out there but im trying to sort my Table in vb6 by date i tried everything i could think of to put it in the WHERE of my SQL statment but i cant get it to work, Also I would like to be able to sort it in a date range like Between DAte1 and Date2.
View Replies !
View Related
Date Range Without Date Column
Suppose I have a table that says on which days I should eat certain foods: +----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+ | Id | Name | Qty | Mon | Tue | Wed | Thu | Fri | Sat | Sun | +----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+ | 1 | Carrots | 3 | T | T | T | T | T | T | T | | 2 | Cauliflower | 1 | T | T | T | T | T | T | T | | 3 | Broccoli | 2 | T | T | T | T | T | T | T | | 4 | Peas | 10 | F | F | F | F | F | T | T | | 5 | Potatoes | 1 | T | T | T | F | F | F | F | +----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+ And suppose this is the calendar: August 2006 MonTueWedThuFriSatSun 123456 78910111213 14151617181920 21222324252627 28293031 For the days August 8 through August 10 inclusive, how can I figure out how much food I should eat with an SQL statement? The answer should be: 9 carrots 3 cauliflowers 6 broccoli 2 potatoes
View Replies !
View Related
Date Range Within A Date Range
I have tryed sub queries and betweens, and cant get it right. I have a list of hotel rooms types than links to a hotel table I also have a costs table with cost periods - like low season and hi season. so I might have 01/05/2009 | 30/05/2009 Low 01/06/2009 | 30/06/2009 Hi if somebody supplies dates to check availability say of the 29/05 - 6/06 the requirment is supplied by two rows of the table.
View Replies !
View Related
SQL For Specifying Date Range Please
I have a table that has a date field in it, in the format of yyyy-mm-dd In my sql I want to select all records who have a date greater than (>) the current day (today). I tried this but to now avail sql = "select * from specials where exp > 2006-06-28 and this sql = "select * from specials where exp > '2006-06-28' I am using asp for this and here is my table structure: CREATE TABLE `specials` ( `id` int(10) NOT NULL auto_increment, `title` varchar(100) NOT NULL default '', `special` text character set ascii NOT NULL, `exp` date NOT NULL default '0000-00-00', `price` double NOT NULL default '0', `image` varchar(50) NOT NULL default '', `shop` int(4) NOT NULL default '0', PRIMARY KEY (`id`) )
View Replies !
View Related
Not Between Date Range
I'm working on a booking system that allows users to search for properties that are available on a certain date. For instance, if the user were to search for properties between 2007-02-15 and 2007-02-22... +----------------------+ | name | date | +----------------------+ | Property1 | 20070221 | | Property1 | 20070220 | | Property1 | 20070219 | | Property2 | 20070214 | | Property2 | 20070213 | | Property2 | 20070212 | | Property3 | 20070216 | | Property3 | 20070215 | | Property3 | 20070214 | +----------------------+ Only Property2 would be displayed because Property1 is booked from the 19-21st and property 3 is booked from the 14-16th. I had a look through the manual and found NOT BETWEEN which seems ideal on paper, but it's including Property3 because that's available on the 14th, despite also being booked on the 15th and 16th. A regular BETWEEN command returns the expected results though, showing Property1 and Property3 as booked. But what I need is the inverse of that (ONLY properties that aren't booked from the 15-22nd), and it's proving pretty tricky! Here's the command I've been using: SELECT DISTINCT name, date FROM properties, pdates WHERE PID=ID AND date NOT BETWEEN 20070215 AND 20070222; As you can see I've two different tables here: properties and pdates which are tied together using ID/PID. There's an entry for each date booked in pdates, so each date range comprises of three rows. Hope all this makes sense, any help would be much appreciated as I'm really stumped here! EDIT: I'm using MySQL 5.1 on an Apache server. The date column is a varchar type.
View Replies !
View Related
Date Range Sorting
how to return data by date ranges, where I return all data made: Today, (Every individual day of the week up to 1 week ago - eg. Everything for last Monday, last Tuesday etc) Two/Three weeks ago Anything over three weeks ago Also, is this something best done in PHP after a full query or during SQL?
View Replies !
View Related
Query Date Range
I have a Date/Time field that has a date and time as the data in the field, in a table that I want to run a query on returning records that are in a date range. What would the select statement be to return the records in the date range just by date so any record in that date range will be returned regaurdless of its time?
View Replies !
View Related
Qucertain Date Range
this is apart of a PHP thing im doing. the script will be given a string such as "Decemeber 2006" and I want to use it to search through a table and select rows where the DateCreated column(which datatype is "Date") is a date in the month of decemeber in 2006 of course you can't just put in December 2006 into the query (right?). so I guess I'll need to use PHP to manipulate the string into something usable in a query, thats not really an issue, it's just some simple string parsing and such. I just don't know what I should be parsing the string to. bassicaly i need to know whats the best way to do this query so i'll know how to reformat the string for mysql.
View Replies !
View Related
Date Range Request
Dates are stored in unix format GMT. I want to find all records either by "day" or by A Date Range "week" i have to deal with users seeing data in their time zone so people in GMT +9 would see records posted accordingly to someone in say GMT -8? Code:
View Replies !
View Related
Date Range Queries
I am trying to figure out how to query a table for a count of records within a given date/time range. my db records a row of data including a TIMESTAMP column (recorded as now() when created). I need to be able to query the table for a count of how many records were recorded within a specifiable time range, say between 7:00 and 13:30 of a specific date.
View Replies !
View Related
Select Date Range
I need a SELECT query that will select records from the past 12 weeks and one that will SELECT records from the past 12 months. the 'compdate' field is a DATETIME field. I have gotten it so far to select records from the same month, and the same year. All form current week: "SELECT * FROM table2 WHERE MONTH(compdate)=$month AND WEEK(compdate,1)=$week ORDER BY id DESC" All from current month: "SELECT * FROM table2 WHERE MONTH(compdate)=$month AND WEEK(compdate,1)=$week ORDER BY id DESC" $month is date("m") in php $week is date("W") in php
View Replies !
View Related
Date Range Change
I've taken over a DB from someone else. The DB is connected to a phpMyAdmin web front end. We need to add records to the DB but the date field only gives us up to the year 2007.
View Replies !
View Related
Date Range With Different Formats
the format for the date in our database is mm/dd/yyyy and when i get the current_date() to compare the two I have the problem of them being different current_date() is yyyy-mm-dd (right?) event_startDate is mm-dd-yyyy I tried using the get_format() function to get them to be the same but it doesnt seem to be working correctly. What I am trying to do is write a query that gets the information for the next upcoming meeting. Thus, the events_startDates needs to be greater than the current_date() and i merely grab the "closest" meeting or the first row as it were. This is what i have so far. How do I get the dates to be comparable to each other? $query = mysql_query("SELECT * FROM meeting_list, events WHERE event_startDate > current_date() AND eventID = ID AND event_type != 'Sales' ORDER BY event_dates ASC LIMIT 1");
View Replies !
View Related
SQL Date Range Query
I need some help with this one. I hate admitting defeat but I'm getting frustrated! I've got a table called 'Events' with these fields... eID eName eStartDate eEndDate An example entry would be... 1 'Course Open Day' 12-Nov-2006 14-Nov-2006 What I want to be able to do is search the table and select any records where a given date falls between the the startDate and endDate. So, if I searched with ཉ-Nov-2006' i'd get 'Course Open Day'.
View Replies !
View Related
Selecting A Date Range Using CURDATE()
When a user selects a page with the recordset I want it to list dates that fall into a certain range, related to CURDATE(). The range fields are start_date and end_date, which are both DATETIME column types. Using BETWEEN won't work because it won't list a date that starts BEFORE CURDATE() e.g. If CURDATE() is 2005-08-10 Then an event that has a start_date of 2005-08-06 and an end_date of 2005-08-26 won't show in the list, even though the date range does fall into the CURDATE() of 2005-08-10.
View Replies !
View Related
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 !
View Related
Select Query Within Specified Date Range..?
Im doing a select in which im specifying the date range.. "select key_id from result where started_at_date >= '2008-10-17 13:30:00' and ended_at_date <= '2008-10-17 13:30:20'" but in the above query im getting the key_id for which the date range is out of the date range specified here. That is im getting key_id = 5, for whcih started_at = '2008-10-18 13:30:00' and ended_at = '2008-10-18 13:30:20'
View Replies !
View Related
Compare Date Range In A Table
how to select data from a table that is based on the comparison of the date in the table to today. For example, I want to do the following: SELECT * FROM tablename WHERE 'date' >= 'now' AND coldate <= 'now + 6 days' How do I write that query? The date format in the table is yyyy-mm-dd.
View Replies !
View Related
Select Not Matching Date Range
I'm looking to write a query which returns a date range result that does not match any date range in the database. Example. id date_from date_to 1 2007-10-10 2007-10-11 2 2007-10-12 2007-10-13 Query returns 2007-10-11 or something along those lines. Is it possible at all?
View Replies !
View Related
Restore Backup By Date Range
I want to restore the backups made in MySQL within a specfied date range. For eg I had taken backup between the dates 01/01/08 to 01/02/08. Now i want to restore it by specifying the date range as 10/01/08 to 20/01/08.
View Replies !
View Related
Selecting A Entry Within A Date Range
I have a MySQL database with 5 columns called Name, Model, Colour, Date_From, Date_To. Basically I want the user to specify the name and a date and want it to return the corresponding entry whose matches the name and within Date_From and Date_To. I wanted to use the date format dd-mm-yy so ive used VARCHAR. This is what ive done of which the date range doesnt work. SELECT Name, Model, Colour, Date, Date_From, Date_To FROM Cars WHERE Name='$Name' AND '$Date BETWEEN Date_To AND Date_From'
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
MySQL Date Range Max Count Query
Code: ----------------------------------------------------- Idstartend ----------------------------------------------------- a2008-09-01 15:012008-09-01 15:04 b2008-09-01 15:022008-09-01 15:09 c2008-09-01 15:122008-09-01 15:15 d2008-09-01 16:112008-09-01 16:23 e2008-09-01 16:192008-09-01 16:25 f2008-09-01 17:522008-09-01 17:59 g2008-09-01 18:182008-09-01 18:22 It shows each time some one logs on, and logs off. The ID is not a userId, just a unique id for the record. I want to be able to query the data so i can create a graph showing concurrent visitors. Either at 10 min, Hourly, Daily or monthly summarys. For example: Code: 2008-09-01 16:10 - 16:202 2008-09-01 17:50 - 18:001 2008-09-01 18:10 - 18:201 The above shows me concurrent visitors within ten minute intervals. The query doesnt have to be that specific, it can just show changes. For example: Code: dateCount ------------------------------------------ 2008-09-01 15:011 2008-09-01 15:022 2008-09-01 15:041 2008-09-01 15:090 2008-09-01 15:121 2008-09-01 15:150 2008-09-01 16:111 2008-09-01 16:192 2008-09-01 16:231 2008-09-01 16:250 etc etc etc...
View Replies !
View Related
Generate Multi-results From Date Range
I am running MySQL 5.0.15. I am not an SQL expert. I have the following situation: I have a simple table that holds brief info on some actions that we expect to happen. actions ( id INT unsigned PRIMARY, name varchar(128) not null, start datetime not null, end datetime not null, ) A record could have time values that both happen on the same day or span one or more day boundaries. We are not creating a "calendar" but the display interface of a monthly calendar view is useful. If I have the data: Code:
View Replies !
View Related
Finding Column Total In Specified Date Range
I'm stumped on an issue that I'm hoping someone can shed some light on. I have a table that tracks financial transactions for multiple members with two key fields being 'available' and 'pending' balances. I am trying to write a select query that will allow me to view the total available and pending balances for all members in a specified date range. Essentially what I need to do is select the sum of each balance type from each unique member at the latest point in the given date range. I've been toying with queries like: SELECT id, MAX(available_balance) AS available, MAX(pending_balance) AS pending FROM members.trans_hist WHERE date BETWEEN '$fromDate' AND '$toDate' GROUP BY pt_id"
View Replies !
View Related
Counting Values That First Occur During A Date Range
I don't know why the query for this is eluding me.. I haven't had a problem coming up with a query for something in a while. I've got a table with two relevant columns, a name and a datetime created_at. The name can occur on many rows at various times. I am creating a list of all names whose first entry into the table was during a user-specified time frame. I have that working fine. It looks similar to this: SELECT name, MIN(created_at) as `min_time` FROM table GROUP BY name HAVING `min_time` BETWEEN ? AND ? In order to page this list, to display only 20 rows at a time, I need to also count how many names appeared during the time frame. For some reason I can't think of the COUNT() to do that...
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
|
TRACKER
