856

MySql how to compare this years month to last years month?

Question:

How do I find the department numbers who have produced more than 20% more sales in them this February (as in this year so would use YEAR(CURDATE()) rather than saying "2012") compared to last February as in the year before. The result table should have only one column which contains the department numbers that have produced 20% more sales.

If the febraury last year has no sales for "department 2" but sales for this febraury, then 0 * 1.2= 0 so the results would not show "department 2". If the february last year for "department 3" sold one item, and the february this year sold two items, this would be a 1.5 increase which is more than 1.2 so the results should show "department 3". If there were only 3 departments, at most, there should be only 3 rows.

Department 2 does not appear because there was 3 sales last february and one sale this february. Department 4 does not appear because there was no sale last february so in terms of maths 0 * 1.2 = 0 so Department 4 would not appear despite there being sales this febraury.

Thank you in advance, and hope this is detailed enough.

Answer1:

Try using having as below:

select departement_no from department group by departement_no having sum(case date_format(date, '%m%Y') when CONCAT('02',YEAR(CURDATE())) then 1 else 0 end) > 1.2 * sum(case date_form(date, '%m%Y') when CONCAT('02',YEAR(DATE_SUB(CURDATE(),INTERVAL 1 Year))) then 1 else 0 end) AND sum(case date_form(date, '%m%Y') when CONCAT('02',YEAR(DATE_SUB(CURDATE(),INTERVAL 1 Year))) then 1 else 0 end) > 0

Answer2:

This uses <strong><a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_makedate" rel="nofollow">MAKEDATE()</a></strong> function to craete the first day of a year and then adds the appropriate month intervals to calculate the start of the (February) month and the start date of the next month.

Replace ? with 1 for January, 2 for February, etc.:

SELECT this_year.departement_no FROM ( SELECT departement_no, COUNT(*) AS num_sales FROM department WHERE date >= MAKEDATE( YEAR(NOW()), 1) + INTERVAL (?-1) MONTH AND date < MAKEDATE( YEAR(NOW()), 1) + INTERVAL (?) MONTH GROUP BY departement_no ) this_year LEFT JOIN ( SELECT departement_no, COUNT(*) AS num_sales FROM department WHERE date >= MAKEDATE( YEAR(NOW())-1, 1) + INTERVAL (?-1) MONTH AND date < MAKEDATE( YEAR(NOW())-1, 1) + INTERVAL (?) MONTH GROUP BY departement_no ) last_year ON last_year.departement_no = this_year.departement_no WHERE this_year.num_sales > 1.2 * COALESCE(last_year.num_sales, 0) ;

If you want (as your rather strange requirements) to not show departments that have sales this February and had no sales last year, remove the COALESCE() function. You can also change the LEFT join to INNER join:

WHERE this_year.num_sales > 1.2 * last_year.num_sales ;

Recommend

  • Merging linked Data in Array in Javascript
  • SQL statement, subquery count?
  • How to create and populate a nested ViewModel well
  • how to keep many to many relationships in sync with nhibernate?
  • How do you submit a rails 3 form without refreshing the page?
  • Using nativescript converters
  • Creating an array of products in JSON-LD
  • fetch all links under/in a specific class-selenium webdriver (java)
  • Using Select and where statement in Criteria
  • web shop (shopping cart) on google app engine
  • What's an elegant way of accessing parent controller's member from child controller?
  • Getting data from database using HQL
  • What does a hyphen at end of a term mean
  • How to merge two Request in Laravel
  • RabbitMQ java client stops consuming messages
  • Can XOR be expressed using SKI combinators?
  • Where these are stored?
  • Write output of for loop to multiple files
  • Plotting densities in R
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • ListItem.Attributes.Add not working
  • How to write order and limit within cakephp joins array
  • Magento Fatal error: Maximum execution error solution, on WAMP
  • JSON response opens as a file, but I can't access it with JavaScript
  • Cannot connect to cassandra from Spark
  • Read text file and split every line in MSBuild
  • Can Jackson SerializationFeature be overridden per field or class?
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • vba code to select only visible cells in specific column except heading
  • Do I've to free mysql result after storing it?
  • php design question - will a Helper help here?
  • Transpose CSV data with awk (pivot transformation)
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • NSLayoutConstraint that would pin a view to the bottom edge of a superview
  • embed rChart in Markdown
  • Sorting a 2D array using the second column C++
  • How to push additional view controllers onto NavigationController but keep the TabBar?
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How do I use LINQ to get all the Items that have a particular SubItem?