mysql - Apply value specified for date range (from, to) to value in table containing single row per date -


First of all, sorry for the title, but my english is too short to explain the meaning of my question. Suppose that we have two tables during the date table, the first table in tbl_percents contains the percentage value history if the date field is equal to to So, 0000-00-00 means that this is an incomplete range. 

  Table: From date to date tbl_percents Int example content: 2001-01-01 | 2015-01-21 | 10% 2015-01-21 | 0000-00-00. 20%

The second table is tbl_revenue , which has a revenue value for a specific date. 

  Table: tbl_revenue date date revenue bigint example content: 2014-01-10 | 10 2015-01-22 | 10

We now want to apply the percentages specified in the table in the table tbl_percents as a result, we want to get something like this: 

  2014-01-10 | 1 #Bikes 2001-01-01 to 2015-01-21 percent = 10% 2015-01-22 | 2 #because 2015-01-22 to date = 20%

Is it possible to get results in a single SQL query?

Yes you want to join one using a condition bet. I have to be careful that these types of questions are very expensive, very fast, so you do not want to do it on a huge dataset. It is being said that, you can join your tables with something similar to the following: 

  SELECT b.revenue, a.percent to join an INNER as tbl_percents tbl_revenue AS b Between b.date between a.from_date and case when a.to_date = DATE ("0000-00-00") THEN DATE ("2100-01-01") ELSE a.to_date END

Actually what I'm doing is setting something very big and in the future (such as January 1, 2100) to_date. If to_date is 0000-00-00, then I apply bigger in the future date. Otherwise, I just use to_date, by using it, I am in my table per second from the date of the revenue where the date of the revenue is between the start date of the date and the end date of my revised percentage.

Again, this is not a computationally good idea on a large dataset ... but for general purposes, it should work properly. If you are having trouble with speed / performance, then I think that using the scripting language such as r or python suggests to apply the same logic.

Good luck!


Comments

Post a Comment

Popular posts from this blog

python - Overriding the save method in Django ModelForm -

I'm having trouble overriding a ModelForm save method. I get this error I'm: Exception type: TypeErier Exception Value: Save () found an unexpected keyword argument 'committed' My intentions include a form 3 fields Submit several values ​​for, then create an object for each combination of those areas, and to save each object file models.py class collarsult type (models.Model): id = model. AutoField (db_column = 'icontact_result_code_type_id', primary_key = true) callResult = models.ForeignKey ( 'CallResult', db_column = 'icontact_result_code_id') campaign = models.ForeignKey ( 'campaign', db_column = 'icampaign_id') CALLTYPE = models.ForeignKey ( 'CALLTYPE', db_column = 'icall_type_id') agent = models.BooleanField (db_column = 'bagent', default = true) teamLeader = models.BooleanField (db_column = 'bTeamLeader', default = true) active = models.BooleanField (db_column = Django.form...
Read more

html - CSS autoheight, but fit content to height of div -

I have a div in which there are three children- div in the left and middle div: float: left while true: float: true because it is my layout Actually will spoil, I've used ClearFix Hack: .cf {zoom: 1; }. Cf: First,. Cf: {content: ""; Display: Table; }. Cf: After {clear: both; } It still works, but I want to be the right div an indicator. So it should fill 100% of the parent unit of height. How can I complete this? PS is the full code: & Lt; / Div & gt; & Lt; Div class = "mobile_content" & gt; & Lt; H5 & gt; {{Data.titel}} & lt; / H5> & Lt; Table & gt; & Lt; TR & gt; & Lt; Th & gt; Komponist: & lt; / Th & gt; & Lt; TD & gt; {{Data.komponist}} & lt; / TD & gt; & Lt; / TR & gt; & Lt; TR & gt; & Lt; Th & gt; Instrument: & lt; / Th & gt; & Lt; TD & gt; {{Data.instrumente}} & lt; / TD & gt; & Lt; / TR & gt;...
Read more

qt - How to prevent QAudioInput from automatically boosting the master volume to 100%? -

I have been trying to use Qt5 multimedia to record audio with QAudioInput, however, when I see If QAudioInput is started, it increases the master volume of my sound device to 100%. How can I prevent QAudioInput from changing the master volume? My current development platform is Linux with PalsAdio (with flat audio disabled). How can I use QAudioInput : QAudioDeviceInfo device_info = QAudioDeviceInfo :: defaultInputDevice (); QAudio format format; Format.setSampleRate (44100); Format.setChannelCount (1); Format.setSampleSize (16); Format.setCodec ("Audio / PCM"); Format.setSampleType (QAudioFormat :: SignedInt); Format.setByteOrder (QAudioFormat :: LittleEndian); Std :: cout & lt; & Lt; Device_info.deviceName (). ToUtf8 (). ConstData () & lt; & Lt; Std :: endl; QAudioInput * default_device = new QAudioInput (device_info, format); QIODevice * default_io_device = default_device-> Start (); a QAudioInput.setVolume () method is not you I ...
Read more