Plsql query to split data based on startdate and end date

I want to split data based on start ,end date ,date range configured (yearly/monthly/weekly/quarterly)

For example if the

Startdate is 2015/10/02 and the

Enddate is 2015/12/22

and my date range is Monthly (M)

then my required output is:

Output ------------- Newstartdate newEnddate 2015/10/02.   2015/10/31 2015/11/01.   2015/11/30 2015/12/01.   2015/12/30 

So I was looking for a generic PL/SQL query to split data what ever date range can be(Y/Q/M/W/D) based on the start and end dates and the range specification.

How to write a query to get wine pairing for a pizza based on pairing weight

I am working on a query to pair drinks to pizzas. Currently I am able to show the pizza_name, beverage and the pairing_weight being the amount of toppings on a pizza that the beverage pairs with. From here, I am looking to just output a mapping of distinct pizza name to beverage. I am unsure how to format the query to just return the top result per pizza based on pairing_weight.

mysql> select pizza_name, beverage, COUNT(beverage) as pairing_weight     -> from pizza_production     ->     JOIN pizza_restrictions pr on     ->         pizza_production.topping_name = pr.topping_name     ->     JOIN beverage_pairing_notes on     ->         restriction = pairing     ->     GROUP BY pizza_name, beverage     ->     ORDER BY pizza_name, pairing_weight DESC; +--------------+--------------------+----------------+ | pizza_name   | beverage           | pairing_weight | +--------------+--------------------+----------------+ | Grand Padano | Prosecco           |              2 | | Grand Padano | Champagne          |              2 | | Grand Padano | Riesling           |              2 | | Grand Padano | Cava               |              2 | | Grand Padano | Pinot Noir         |              1 | | Grand Padano | Carlsberg beer     |              1 | | Grand Padano | Zinfandel Rosé     |              1 | | Grand Padano | Chardonnay         |              1 | | Grand Padano | Chenin Blanc       |              1 | | Grand Padano | Gewürztraminer     |              1 | | Grand Padano | Guinness beer      |              1 | | Grand Padano | Pinot Grigio       |              1 | | Grand Padano | Heineken beer      |              1 | | Grand Padano | Irn Bru            |              1 | | Grand Padano | Malbec             |              1 | | Grand Padano | Muscat Blanc       |              1 | | new york     | Riesling           |              2 | | new york     | Champagne          |              2 | | new york     | Prosecco           |              2 | | new york     | Pinot Noir         |              2 | | new york     | Malbec             |              2 | | new york     | Syrah              |              1 | | new york     | Cava               |              1 | | new york     | Cabernet Sauvignon |              1 | | new york     | Chardonnay         |              1 | | new york     | Carlsberg beer     |              1 | | new york     | Chenin Blanc       |              1 | | new york     | Gewürztraminer     |              1 | | new york     | Guinness beer      |              1 | | new york     | Muscat Blanc       |              1 | | new york     | Heineken beer      |              1 | | pepperoni    | Pinot Noir         |              3 | | pepperoni    | Malbec             |              3 | | pepperoni    | Muscat Blanc       |              2 | | pepperoni    | Heineken beer      |              2 | | pepperoni    | Guinness beer      |              2 | | pepperoni    | Gewürztraminer     |              2 | | pepperoni    | Chenin Blanc       |              2 | | pepperoni    | Riesling           |              2 | | pepperoni    | Carlsberg beer     |              2 | | pepperoni    | Cabernet Sauvignon |              2 | | pepperoni    | Chardonnay         |              1 | | pepperoni    | Champagne          |              1 | | pepperoni    | Prosecco           |              1 | | pepperoni    | Cava               |              1 | | pepperoni    | Rioja              |              1 | | pepperoni    | Syrah              |              1 | | pepperoni    | Zinfandel Rosé     |              1 | | vegetarian   | Diet Coke          |              2 | | vegetarian   | Prosecco           |              1 | | vegetarian   | Champagne          |              1 | | vegetarian   | Riesling           |              1 | +--------------+--------------------+----------------+ 52 rows in set (0.01 sec) 

An example of desired output would be

+--------------+--------------------+ | pizza_name   | beverage           | +--------------+--------------------+ | pepperoni    | Pinot Noir         | | new york     | Riesling           | | Grand Padano | Prosecco           | | vegetarian   | Diet Coke          | +--------------+--------------------+ 

Is there a way to pull this data from what I already have in the output of the query above? Thank you.

Assign wordpress featured images via external URL based on numeric value in custom fields

I have several posts (150) with a custom field ‘floorplan-image-id’ which has a unique value (number) that feeds from XML. The featured image for each post should be automatically assigned via external URL based on the above custom field value.

Example: if floorplan-image-id = 12345 then the Featured image will be : //someurl.com/images/floorplan-1.png

else if floorplan-image-id = 34567 then the Featured image will be : //someurl.com/images/floorplan-2.png

====================

function custom_floorplan-image($ html, $ post_id, $ meta_field) {

$ meta_field = get_post_meta($ post->ID, ‘floorplan-image-id’, true);

if ($  meta_field === '12345') {     $  html = "<img src='"//mysite.com/images/Floorplan_A1.png"'>"; }else if ($  meta_field === '34567') {     $  html = "<img src='"//mysite.com/images/Floorplan_A2.png"'>"; }  return $  html; 

}

add_filter(‘post_thumbnail_html’, ‘custom_floorplan-image’, 10, 3);

how to exclude images from scrset based on file size (kb)

when I upload optimized jpg images to WordPress, I encounter the following problem: The original image file size is smaller than the auto-generated images (for the 1st and sometimes even the 2nd "smaller" auto-generated size).

Example: Original Image File: example.jpg (77kb, 816 * 612px)

Auto-generated Files: example-768×576.jpg (112kB), example-600×450.jpg (76kB)

anything I can do about this? The resulting problem is scrset, it doesn´t make any sense to deliver a scaled down image to the user if the file size is bigger or equal to the original file! Is there a functionality to "teach" scrset to pay attention to the file size as well?

-thanks in advance!

Coloured integers: Select outcome unexpected (part of a graph based problem using coloured vertex labels)

I use the following code output and need to retrieve the position of the part containing the blue-coloured integer 4

Part 1:

List[Placed[List[       Style[3, Rule[LineColor, RGBColor[1, 0, 0]],         Rule[FrontFaceColor, RGBColor[1, 0, 0]],         Rule[BackFaceColor, RGBColor[1, 0, 0]],         Rule[GraphicsColor, RGBColor[1, 0, 0]],         Rule[FontColor, RGBColor[1, 0, 0]]],        Style[4, Rule[LineColor, RGBColor[0, 0, 1]],         Rule[FrontFaceColor, RGBColor[0, 0, 1]],         Rule[BackFaceColor, RGBColor[0, 0, 1]],         Rule[GraphicsColor, RGBColor[0, 0, 1]],         Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]], Placed[      List[Style[4, Rule[LineColor, RGBColor[1, 0, 0]],         Rule[FrontFaceColor, RGBColor[1, 0, 0]],         Rule[BackFaceColor, RGBColor[1, 0, 0]],         Rule[GraphicsColor, RGBColor[1, 0, 0]],         Rule[FontColor, RGBColor[1, 0, 0]]],        Style[3, Rule[LineColor, RGBColor[0, 0, 1]],         Rule[FrontFaceColor, RGBColor[0, 0, 1]],         Rule[BackFaceColor, RGBColor[0, 0, 1]],         Rule[GraphicsColor, RGBColor[0, 0, 1]],         Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]]] 

I tried:

Part 2:

        Select[List[Placed[List[               Style[3, Rule[LineColor, RGBColor[1, 0, 0]],                 Rule[FrontFaceColor, RGBColor[1, 0, 0]],                 Rule[BackFaceColor, RGBColor[1, 0, 0]],                 Rule[GraphicsColor, RGBColor[1, 0, 0]],                 Rule[FontColor, RGBColor[1, 0, 0]]],                Style[4, Rule[LineColor, RGBColor[0, 0, 1]],                 Rule[FrontFaceColor, RGBColor[0, 0, 1]],                 Rule[BackFaceColor, RGBColor[0, 0, 1]],                 Rule[GraphicsColor, RGBColor[0, 0, 1]],                 Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]], Placed[              List[Style[4, Rule[LineColor, RGBColor[1, 0, 0]],                 Rule[FrontFaceColor, RGBColor[1, 0, 0]],                 Rule[BackFaceColor, RGBColor[1, 0, 0]],                 Rule[GraphicsColor, RGBColor[1, 0, 0]],                 Rule[FontColor, RGBColor[1, 0, 0]]],                Style[3, Rule[LineColor, RGBColor[0, 0, 1]],                 Rule[FrontFaceColor, RGBColor[0, 0, 1]],                 Rule[BackFaceColor, RGBColor[0, 0, 1]],                 Rule[GraphicsColor, RGBColor[0, 0, 1]],                 Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]]], Select[#[[1]][[2]], IntegerQ] &  == 4] 

Which produces { }

However

Part 3

 a = Placed[     List[Style[3, Rule[LineColor, RGBColor[1, 0, 0]],        Rule[FrontFaceColor, RGBColor[1, 0, 0]],        Rule[BackFaceColor, RGBColor[1, 0, 0]],        Rule[GraphicsColor, RGBColor[1, 0, 0]],        Rule[FontColor, RGBColor[1, 0, 0]]],       Style[4, Rule[LineColor, RGBColor[0, 0, 1]],        Rule[FrontFaceColor, RGBColor[0, 0, 1]],        Rule[BackFaceColor, RGBColor[0, 0, 1]],        Rule[GraphicsColor, RGBColor[0, 0, 1]],        Rule[FontColor, RGBColor[0, 0, 1]]]],      List[Before, After]][[1]][[2]]  Select[a,IntegerQ] 

produces 4 as expected.

Hence I would have expected Part 2 to produce the expression containing the blue label 4, i.e. the following result

Placed[List[           Style[3, Rule[LineColor, RGBColor[1, 0, 0]],             Rule[FrontFaceColor, RGBColor[1, 0, 0]],             Rule[BackFaceColor, RGBColor[1, 0, 0]],             Rule[GraphicsColor, RGBColor[1, 0, 0]],             Rule[FontColor, RGBColor[1, 0, 0]]],            Style[4, Rule[LineColor, RGBColor[0, 0, 1]],             Rule[FrontFaceColor, RGBColor[0, 0, 1]],             Rule[BackFaceColor, RGBColor[0, 0, 1]],             Rule[GraphicsColor, RGBColor[0, 0, 1]],             Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]] 

On closer inspection I tried the following:

FullForm[Select[a, IntegerQ]] 

which results in

Style[4] 

However, Style[4] should not be the result of a selection for integers.

What is going on?

Monthly summary of active users based on a start/end date roster

I have a table that dictates who was on a project, from a startdate to an enddate. I’d like some help writing a query that will return the number of "active" users at the end of every month, for the past 12 months.

CREATE TABLE `roster` (   `id` int(11) NOT NULL,   `user_id` int(11) DEFAULT NULL,   `project_id` int(11) DEFAULT NULL,   `start_date` datetime NOT NULL,   `end_date` datetime NOT NULL,   `closed_date` datetime DEFAULT NULL,   PRIMARY KEY (`id`) ); 
id  user_id project_id  start_date  end_date    closed_date 1   1   1   2019-05-27 00:00:00 2021-02-01 00:00:00 NULL 2   2   2   2020-05-27 00:00:00 2021-02-01 00:00:00 2020-02-05 00:00:00 3   3   3   2020-05-27 00:00:00 2024-02-01 00:00:00 2020-02-05 00:00:00 

And the result would be something that shows how many distinct users had an active project within each month (only since the start of this year).

So for the dataset above, we can see that all 3 projects were "active" in the month January 2021, because the end date is in the future.

2020-01-31 | 3 

The last bit of complexity is that sometimes a project can be closed before the EndDate and I’d like to exclude any users who’s endDate is in the future but the project has actually closed.

For example in the dataset above, the third project has a end date of Feb 2024, however the project closed in Feb 2021. So technically the person was active in 2021 Jan and Feb, but not March.

p.s Hoping to get an answer in Postgres (I’m using Redshift)

Replace or Update a Table Row – based on the presence of a matching row item

Unfortunately I know next to nothing about php coding that contains some sql code. The examples I have seen online do not seem to match the same syntax that I inherited with this code, which is making this task confusing for me.

I have a database table called "effort" that contains two columns:

Column 1 = ad  Column 2 = theEffort 


 This line of code below works, and it will populate the table with the proper values for both the "ad" and "theEffort" 

$  sql = "INSERT INTO effort SET ad='$  ad', theEffort='$  theEffort'"; 


 But I do not want the table to grow over time and contain redundant rows pertaining to the same ad. Each "ad" in this table should be unique. But each "ad" could have 5 different effort values, e.g., "theEffort" can be "easy", "medium", "difficult", "impossible".

The "ad" is 13 digit number, i.e. SFAD_000001234

Does anybody know how to modify my code above using similar syntax so that it would first check to see if the "ad" exists in the database table called "effort". And if so, instead of entering a new row it would update "theEffort" to the new value. And if the "ad" did not exist in any table row within the "effort" database then it would execute the INSERT INTO function, i.e., insert both the "ad" and "theEffort" as a new table row.

Thank you in advance, -Jeff

How Microsoft Junk Mail filter really works based in body content links?

I’m not here to discuss about DKIM, DMARC, SPF, or other of the hundreds of variables in the difficult path to get your email into the Inbox instead of Junk folder.

I’m here just about the links in the body content of the email, and I’ll tell you why.

I have been sending dozens of emails from a Google Apps mail account, to a hotmail.com account.

It was just TEXT, this text:

Hi there !!! Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aenean sagittis imperdiet eros sit amet placerat. Aenean blandit efficitur eros non mattis. Integer vehicula nunc eget est vulputate, sit amet consequat ipsum feugiat. Aliquam massa odio, dignissim vel luctus in, aliquam sit amet est. Aliquam erat volutpat.   Phasellus egestas, dolor in tempor ullamcorper, tortor lectus auctor neque, vel suscipit diam tellus tempus tortor. Nam vulputate euismod ligula, ac tempor arcu tempus at. Donec ultricies velit sed justo mollis accumsan at nec nisi. Vestibulum a diam ac magna varius consectetur. Donec in justo vitae ex lacinia imperdiet. Nullam gravida urna leo. Etiam congue, dui a commodo feugiat, risus tellus tempor neque, eget vestibulum erat ex non augue.  If you want you can visit: https://xxxxxxxxx.xxxx  Donec pulvinar rutrum commodo. Cras porta facilisis bibendum. Pellentesque pretium est sem, ut varius diam suscipit quis. Aenean et eros auctor, rhoncus mi in, dignissim odio. Suspendisse suscipit enim lorem, quis interdum massa consectetur in. Integer suscipit eros sodales hendrerit tempor. Etiam finibus semper tellus, ac suscipit orci mollis eu. Pellentesque vehicula auctor ultrices. In hendrerit dolor quis lorem laoreet, vel convallis magna laoreet. Sed pulvinar non magna maximus dapibus.  Nullam semper lacus eget hendrerit dapibus. Morbi eget urna eleifend, consectetur elit ac, fermentum orci. Mauris et urna est. Mauris ac lacinia lectus. Vestibulum scelerisque tempor nibh ac eleifend. Nulla faucibus tellus blandit quam suscipit ultricies. Suspendisse finibus risus id lectus lobortis mattis.  Regards 

The only change was the ONLY link in all email content. Everything was exact the same. The content, the subject, the sender … I just changed that link.

What I saw is with some links, the email goes to Inbox folder With other links, it goes to the Junk folder.

Just that ! Just because of the domain name of the only link in the body content.

Why is that?

I try to figure out things like:

  • domain age
  • domain – ip and reverse
  • domain ip in ip’s blacklist
  • domain in domain’s blacklist
  • domain has https activated
  • and some other variables

but I can’t get any "rule" that satisfies why sometimes "that link" is "good" or not to Microsoft Junk Email Filter.

Does anyone know any clue in order to check the list of "good" domains and "bad" domains I have so we can all learn about this? 😉

Response time optimization – Getting record count based on Input Parameters

I’m trying to optimize the process of calculating count of records, based on variable input parameters. The whole proces spans several queries, functions and stored procedures.

1/ Basically, front-end sends a request to the DB (it calls a Stored procedure) with an input parameter (DataTable). This DataTable (input parameter collection) contains 1 to X records. Each record corresponds to one specific rule.

2/ SP receives the collection of rules (as a custom typed table) and iterates through them one by one. Each rule apart from other meta-data contains a name of a specific function that should be used in evaluating the said rule.

For every rule, the SP prepares a dynamic query wherein it calls the mentioned function with 3 input parameters.

a/ Custom type Memory Optimized Table (Hashed index) b/ collection of lookup values (usually INTs) that the SELECT query uses to filtr data. Ie. "Get me all records, that have fkKey in (x1, x2, x3)" c/ BIT determining if this is the first rule in the whole process.

Each function has an IF statement, that determines based on the c/ parameter if it should return "all" records that fullfill the input criteria (b/). Or if it should fullfill the criteria on top of joining the result of previous rule that is contained in the custom table (a/)

3/ Once the function is run, it’s result is INSERTed into a table variable called @tmpResult. @result is then compared to tmpResult and records that are not in the tmpResult are DELETEd from result.

  • @result is a table variable (custom memory optimized table type), that holds intermediate result during the whole SP execution. It is fully filled up on the first rule, every consequent rule only removes records from it.

4/ The cycle repeats for every rule until all of the rules are done. At the end, count is called on the records in @result and returned as a result of SP.

Few things to take into account:

  • There are dozens of different types of rules. And the list of rules only grows bigger over time. That’s why dynamic query is used.
  • The most effective way to temporarily store records between individual rule execution so far proved to be custom Memory-Optimized table type. We tried a lot of things, but this one seems to be the fastest.
  • The number of records that are usually returned for 1 single rule is roughly somewhere between 100 000 and 3 000 000. That’s why a bucket_size of 5 000 000 for the HASHed temporary tables is used. And even though we tried nonclustered index, it was slower than that HASH.
  • The input collection of rules can vary strongly. There can be anything from 1 rule up to dozens of rules used at once.
  • Most every rules can be defined with at minimum 2 lookup values .. at most with dozens or in a few cases even hundred values. For a better understanding of rules, here are some examples:

Rule1Color, {1, 5, 7, 12} Rule2Size, {100, 200, 300} Rule3Material, {22, 23, 24}

Basically every rule is specified by it’s Designation, which corresponds to a specific Function. And by it’s collection of Lookup values. The possible lookup values differ based on the designation.

What we have done to optimize the process so far:

  • Where big number of records need to be temporarily stored, we use Memory-Optimized variable tables (also tried with temp ones, but it was basically same when using Memory-Optimized variants).
  • We strongly reduced and optimized the source tables the SELECT statements are run against.

Currently, the overal load is somewhat balanced 50/50 between I/O costs pertaining to SELECT statements and manipulation with records between temporary tables. Which is frankly not so good .. ideally the only bottleneck should be the I/O operations, but so far we were not able to come up with a better solution since the whole process has a lot of variability.

I will be happy for any idea you can throw my way. Of course feel free to ask questions if I failed to explain some part of the process adequately.

Thank you