logo
down
shadow

Select current ( week or any given week ) records excluding week ends and holidays


Select current ( week or any given week ) records excluding week ends and holidays

Content Index :

Select current ( week or any given week ) records excluding week ends and holidays
Tag : php , By : snapshooter
Date : November 29 2020, 04:01 AM

Hope that helps For weekdays only use the DAYOFWEEK function. The sum and more filters are left as an exercise for you, but the gist is:
SELECT * FROM to_do
WHERE DAYOFWEEK(`date`) IN (2,3,4,5,6)
AND `date` NOT IN (
  SELECT `date` FROM holidays
)

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Group by week and select records from current week


Tag : mysql , By : MK.
Date : March 29 2020, 07:55 AM
I wish this helpful for you I have been trying to display a table of results for weekly sales using MySQL, where the results of the week should be like:
 SELECT DATE_FORMAT(cc.datefield, '%d-%b') AS DATE
        , SUM(IFNULL(cds.quantity,0)) AS total_sales 
 FROM cart_calendar cc
 INNER JOIN cart_daily_sales cds
         ON (DATE(cds.order_date) = cc.datefield)
 WHERE WEEK(cc.datefield) = WEEK(now())
 GROUP BY WEEK(cc.datefield)
 SELECT DATE_FORMAT(cc.datefield, '%d-%b') AS DATE
        , SUM(IFNULL(cds.quantity,0)) AS total_sales 
 FROM cart_calendar cc
 INNER JOIN cart_daily_sales cds
         ON (DATE(cds.order_date) = cc.datefield)
 WHERE WEEK(cc.datefield) = WEEK(now())
 GROUP BY MOD(WEEKDAY(cc.datefield)+1,6) WITH ROLLUP

Issues with finding SQL records for current week and last week


Tag : php , By : moops
Date : March 29 2020, 07:55 AM
I wish this helpful for you I'll put this here as I can use the code tags better and have more space.
So what we want to do is do 2 queries off a db where the first query runs last monday to last sunday and the 2nd query does this monday to next sunday
$lastsunday = date('Y-m-d', strtotime('last sunday'));
$thismonday = date('Y-m-d', strtotime('this monday'));
//there's better ways of doing this probably but I'm just copy and pasting from your code above
$last_week_sql = "SELECT * FROM table WHERE DateColumn>=DATE_SUB('$lastsunday',INTERVAL 7 DAY) AND DateColumn<='$lastsunday'";

$this_week_sql = "SELECT * FROM table WHERE DateColumn>='$thismonday' AND DateColumn<=DATE_ADD('$thismonday',INTERVAL 7 DAY)";

MySQL-Select start and end of week along with average of a column (week-by-week)


Tag : mysql , By : Kilimanjaro
Date : March 29 2020, 07:55 AM
will help you You need an expression that truncates an arbitrary date to the first day of the week in which it occurs. That is, it returns 2017-05-21 (Sunday) if you give it 2017-05-24
This expression does that, assuming your weeks start on Sundays. Here's an explanation.
    FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -1, 7))
SELECT AVG(player_count) as average, 
  FROM_DAYS(TO_DAYS(updated_at) -MOD(TO_DAYS(updated_at) -1, 7)) week_beginning, 
  FROM_DAYS(TO_DAYS(updated_at) -MOD(TO_DAYS(updated_at) -1, 7)) + INTERVAL 6 DAY week_ending 
FROM `gtan_servers` 
WHERE server_short_name = 'FiveRP' 
  AND updated_at >= FROM_DAYS(TO_DAYS(NOW()) -MOD(TO_DAYS(NOW()) -1, 7)) - INTERVAL 6 WEEK
GROUP BY FROM_DAYS(TO_DAYS(updated_at) -MOD(TO_DAYS(updated_at) -1, 7)) 
ORDER BY 2 DESC 
LIMIT 6
DELIMITER $$
DROP FUNCTION IF EXISTS TRUNC_SUNDAY$$
CREATE
  FUNCTION TRUNC_SUNDAY(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'returns preceding Sunday'
  RETURN FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -1, 7))$$
DELIMITER ;
SELECT AVG(player_count) as average, 
  TRUNC_SUNDAY(updated_at) week_beginning, 
  TRUNC_SUNDAY(updated_at) + INTERVAL 6 DAY week_ending 
FROM `gtan_servers` 
WHERE server_short_name = 'FiveRP' 
  AND updated_at >= TRUNC_SUNDAY(NOW()) - INTERVAL 6 WEEK
GROUP BY TRUNC_SUNDAY(updated_at) 
ORDER BY TRUNC_SUNDAY(updated_at) DESC 
LIMIT 6

Week starting hour gives previous week date instead of current week


Tag : sql , By : Reiner
Date : March 29 2020, 07:55 AM
hop of those help? In our project we are maintaining a customers weekly spend. For that every week start we are resetting a customer limit. , I believe your issue lies in your date manipulation. You are doing:
TRUNC(TXNDT) BETWEEN (TRUNC (SYSTIMESTAMP, 'IW') - 1 ) AND TRUNC (SYSTIMESTAMP)
TRUNC(TXNDT) BETWEEN (TRUNC (SYSDATE + 1, 'IW') - 1 ) AND TRUNC (SYSDATE)
WITH dts AS (SELECT TRUNC(SYSDATE, 'mm') - 1 + LEVEL dt
             FROM   dual
             CONNECT BY LEVEL <= 13)
SELECT dt,
       to_char(dt, 'Dy') day_of_week,
       TRUNC(dt, 'iw') monday_start_week,
       TRUNC(dt, 'iw') - 1 your_sunday_start_week,
       TRUNC(dt + 1, 'iw') - 1 actual_sunday_start_week
FROM   dts;

DT          DAY_OF_WEEK MONDAY_START_WEEK YOUR_SUNDAY_START_WEEK ACTUAL_SUNDAY_START_WEEK
----------- ----------- ----------------- ---------------------- ------------------------
01/01/2020  Wed         30/12/2019        29/12/2019             29/12/2019
02/01/2020  Thu         30/12/2019        29/12/2019             29/12/2019
03/01/2020  Fri         30/12/2019        29/12/2019             29/12/2019
04/01/2020  Sat         30/12/2019        29/12/2019             29/12/2019
05/01/2020  Sun         30/12/2019        29/12/2019             05/01/2020  <-----
06/01/2020  Mon         06/01/2020        05/01/2020             05/01/2020
07/01/2020  Tue         06/01/2020        05/01/2020             05/01/2020
08/01/2020  Wed         06/01/2020        05/01/2020             05/01/2020
09/01/2020  Thu         06/01/2020        05/01/2020             05/01/2020
10/01/2020  Fri         06/01/2020        05/01/2020             05/01/2020
11/01/2020  Sat         06/01/2020        05/01/2020             05/01/2020
12/01/2020  Sun         06/01/2020        05/01/2020             12/01/2020  <-----
13/01/2020  Mon         13/01/2020        12/01/2020             12/01/2020

MySql: Select from a table when week is actual week, and the week before


Tag : mysql , By : MK.
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • How to iterate array inside array data in laravel ,output are given below
  • To get output of movie with its genre
  • Extracting some data from a JS object literal string in PHP using Regex
  • axios and vuex are deleting wrong index in array
  • Setting empty CURLOPT_POSTFIELDS
  • Laravel 5.8 use subdomain as API endpoint beside domain.com/api
  • Error while add data in database SQL Server 2016
  • Does PhpStorm allow to skip PHPDoc tags when type hints is declared?
  • html classes are changed to Attributes
  • Using json to import multidimensional data
  • passing data from the view to the controller, where the data comes from another controller
  • How to extract integer and decimal numbers in PHP string correctly?
  • Parsing Sub-Object from an XML Feed with objects
  • Getting error "Unknown column 'text' in 'field list'"
  • How to properly get onedrive access token via php and curl
  • How to add spell check to a php function
  • Is it posible to create css rules with php?
  • Pretty URL with $_GET method
  • Wordpress always load same page - index.php
  • Find value in array and return with new from different array
  • PHP multiple file upload with different keys
  • Can't modify times one a time without weird errors
  • Get size of scraped image with domcrawler (Goutte)
  • Laravel - Ordering a collection of models
  • PHP Fatal error: Uncaught Error: Call to a member function setFetchMode() on boolean
  • Strict Standards: Only variables should be passed by reference in /main_dir/sub_dir/backup.php
  • Uncaught Google_Exception: (update) unknown parameter: 'copyRequiresWriterPermission'
  • Laravel 5 form does not redirect with old input
  • How to get values greater than or equal to in Mysql database sql when its a value like 1:00 PM?
  • PHP datediff overday issue
  • Masking an auto incrementing primary key
  • How to grab a String BEFORE a certain word
  • ZF2 Routing Multiple Controllers based on route constraints
  • How to deregister/dequeue jquery.sticky.js in Elementor (Pro)?
  • How to fix errors counting words in text plain with PHP?
  • How to get View data in Laravel in custom helper or directive
  • Creating a new data entry into a database with eloquent and trying to access the variable in the url
  • How to test authentication using Laravel Dusk?
  • PHP parse_str not getting all $_GET variables
  • Variable issues in php class
  • Get orders between dates in Codeigniter
  • Eloquent Special Chars Issue
  • Keeping leading zeros column Mysql
  • Current level navigation in Wordpress
  • Add a custom text to WooCommerce cart items if the product has a specific shipping class
  • Error connecting to CommissionJunction Token : [60] SSL certificate problem: unable to get local issuer certificate
  • How do I add a new key value pair to an object in PHP?
  • Http post request to php with dart
  • How to add a route in with blade into parameters of a component
  • How can i get the source of a 777 - CHMOD php file?
  • Convert 2010-04-16 16:30:00 to "Tomorrow Afternoon"
  • How can I hide a $_GET variable to be more secured in PHP?
  • Login to Gmail Inbox using Curl?
  • Upload Excel or CSV file to MySQL with PHP
  • Outlook Marking Email as Junk Email
  • Upload an image using class.upload.php via AJAX, display image in form using jQuery
  • mysql_connect() causes page to not display (WAMP)
  • How to limit a users uploaded image size using PHP
  • What's the difference whether or not to run PHP in safe mode?
  • Logging in to a website cURL
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com