Count And Group By Month In 1 Query For A Date Range?
I have a table that has employees work history on it. Basically the only thing I am worried about is the start and end date. My boss wants a report that charts out the quantity of workers for the past 12 months group by the month.
For example
Employee--Start Date--End Date
Charles-----2008-6-3---2009-2-1
Vicky-------2009-1-1---Present
So in this case there would be a quantity of 1 from June 08 to December 08. Then their would be a quantity of 2 from Jan 09 to Feb 09. And Vicky would also be counted for March since she is still working. I can do this by creating PHP functions and putting it all together, but I was just wondering if there was a way to do this all in 1 query.
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
Group By Date, Count Of Multiple Fields Within Date
take table a: userID INT, userLogin VARCHAR and table b: customerID INT, userID INT, customerStarted DATE, customerFinished DATE what I'm looking to produce is a report by date, showing number of starts and finishes for all customers marked with a specific userID, like User 4: Date | starts | finishes 2008-10-01 | 0 | 5 2008-10-02 | 3 | 3 2008-10-03 | 4 | 2 2008-10-04 | 0 | 0 2008-10-05 | 3 | 3 ... etc.
View Replies !
Count The Hits For Week And Month In One Query?
I have this query to count the amount of total clicks for a whole week, but i would also like to know the total amount of clicks for the whole month. Is it possible to do both of them with one query? Or do i really need to make 2 seperate querys for that? SELECT date FROM clicks WHERE date >= '$timestamps[0]' and date <= '$timestamps[6]' AND link_id = '{$row['ad_linkid']}'
View Replies !
Using Group By Date And Count
Is there a way to select count(*) by grouping by date, and having multiple date ranges? combining... select field,count(*) from table where datefield > 2004/1/1 and datefield < 2004/1/31 and select field,count(*) from table where datefield > 2004/2/1 and datefield < 2004/2/29 so that the output is field 34 field 40
View Replies !
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 !
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 !
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 !
Month By Month Count Of Records
I need to generate record counts for each month in a year, just one year at a time, but a full year at a glance. I am thinking 12 hits to the DB is perhaps not the most idea way to do it, is there a way to rip this out in one go?
View Replies !
How To Create Complex Query With Group By And Count
I have a table usertags: usertags ------------ - usertagid - usertagsiteid - usertagtext I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext. Not sure if that makes sense so here is an example: ......
View Replies !
Query - Count With Date Intervals
I have this: select count(*) from visitors where visitordate >= '2008-01-01' AND visitordate >= '2008-01-08' Now this gives me a count of how many visitors that week visited my site but I want something more automatic. I do not know which functions to use to make this logic below happen: Start with year base (ie:2008) increment by week number (ie:upto 52) and determine how many visitors each week visited the site. I wish this worked: select count(*) from visitors where visitordate = week(1), then week(2), etc.
View Replies !
GROUP BY Range
I'm trying to write a script that reports the number of users in each country. I have a users table that contains an ip address field and another table that contains the ip-ranges of each country. It has 3 significant columns, (1) name of the country (2) ip range start (3) ip range end. Is it possible to write one SQL statement that will have this result: 1. First column is the name of the country 2. Second column is the number of users within that country's ip-range 3. All countries with no user records will be disregarded I'm thinking about using GROUP BY but I'm not sure if it's possible to use it with ranges instead of just one column match.
View Replies !
GROUP BY IP Address Range
I store IP addresses using the INET_ATON function, e.g. INSERT INTO myTable(IP_Address) Values (INET_ATON(123.123.33.12)); I would like to create a daily report that groups IP addresses by a "dotted triad", e.g. (pseudo SQL) SELECT myTable.IP_Address FROM myTable GROUP BY 123.123.33.*;
View Replies !
Retrieving Months And News Count From That Month
I'm creating a news archive page. Here is a trimmed down version of my news table: news_id author_id title maintext date_added (datetime) What I want to do in one query is get a list of all the months in a given year and a count of how many news stories were added in each month. Off the top of my head I could just think of selecting distinct month from date_added and counting the news items in that month and given year. Perhaps there is a better way though as I realise the distinct function isn't very efficient.
View Replies !
Date Format With Month Name
if it is possible to insert into a mySQL db a date format formatted like so: 11-Oct-06. I would like to save myself any tedious string processing tasks (I know I could convert it with PHP...).
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
Comparing String Dates To Current Date For Month And Day
Trying to set the display column to a no if the ending date is before or at the current date. Comparing string dates to the current date for month and day only. Am using CONCAT because the ending date needs to be figured into a date as month_end is in a varchar type and day_end is a tinyint type. The query does run, but it affects everything where continuous != 'always' - seeming to ignore the AND part. UPDATE Listings SET display = 'no' WHERE continuous != 'always' AND STR_TO_DATE(CONCAT(month_end, '-', day_end), '%M-%e') <= DATE_FORMAT(CURDATE(), '%M-%e') What should be done differently here?
View Replies !
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 !
Date Range MySQL
show me the code where I can return all recoreds whose date field is greater than today but less than 3 months from today.
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
SELECT Statement For Date Range
I have a table that has an event StartDate and EndDate, based on the current Date "NOW()" I need to know which records are currently active. Can anyone help with a quick SELECT statement?
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 !
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 !
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 !
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 !
|
TRACKER
