CODEWITHSUNDEEP
mysqlsql
Aasish Kumar
Aasish Kumar

Reputation: 21

To show the data in weekly basis

I have a date column in a table. Based on the selection of the date it should calculate the order count for last 6 weeks as Week1,Week2...Week6 (This is not the week number its the simple serial number). For example, If user selects the date as 12/10/2017(dd/mm/yyyy) then it should calculate the order count for the week dates as

below image:

Can someone please let me know whether this is possible in SQL ?

Upvotes: 2

Views: 112

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Here is another way to achieve the same result - 

select CONCAT(subdate(CURDATE(), INTERVAL (weekday(CURDATE())+1) DAY), ' - ', CURDATE())
      ,CONCAT(subdate(date_sub(CURDATE(), interval 7 day), INTERVAL (weekday(CURDATE())+1) DAY), ' - ', subdate(CURDATE(), INTERVAL (weekday(CURDATE())+2) DAY))
      ,CONCAT(subdate(date_sub(CURDATE(), interval 14 day), INTERVAL (weekday(CURDATE())+1) DAY), ' - ', subdate(date_sub(CURDATE(), interval 7 day), INTERVAL (weekday(CURDATE())+2) DAY))
      ,CONCAT(subdate(date_sub(CURDATE(), interval 21 day), INTERVAL (weekday(CURDATE())+1) DAY), ' - ', subdate(date_sub(CURDATE(), interval 14 day), INTERVAL (weekday(CURDATE())+2) DAY))
      ,CONCAT(subdate(date_sub(CURDATE(), interval 28 day), INTERVAL (weekday(CURDATE())+1) DAY), ' - ', subdate(date_sub(CURDATE(), interval 21 day), INTERVAL (weekday(CURDATE())+2) DAY))
      ,CONCAT(subdate(date_sub(CURDATE(), interval 35 day), INTERVAL (weekday(CURDATE())+1) DAY), ' - ', subdate(date_sub(CURDATE(), interval 28 day), INTERVAL (weekday(CURDATE())+2) DAY))

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

This is possible with SQL code.

Query

SELECT  
   CONCAT(week1.first_day, '-', week1.second_day) AS Week1
 , CONCAT(week2.first_day, '-', week2.second_day) AS Week2
 , CONCAT(week3.first_day, '-', week3.second_day) AS Week3
 , CONCAT(week4.first_day, '-', week4.second_day) AS Week4
 , CONCAT(week5.first_day, '-', week5.second_day) AS Week5
 , CONCAT(week6.first_day, '-', week6.second_day) AS Week6
FROM (
 SELECT 
     DATE_FORMAT(@first_date_of_week, '%d/%m/%Y') AS first_day
   , DATE_FORMAT(@date, '%d/%m/%Y') AS second_day 
)  
 week1 
CROSS JOIN (
  SELECT 
      DATE_FORMAT(@first_date_of_week - INTERVAL 1 WEEK, '%d/%m/%Y') AS first_day
    , DATE_FORMAT(@first_date_of_week - INTERVAL 1 WEEK + INTERVAL 1 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS second_day
) 
 week2
CROSS JOIN (
  SELECT 
      DATE_FORMAT(@first_date_of_week - INTERVAL 2 WEEK, '%d/%m/%Y') AS first_day
    , DATE_FORMAT(@first_date_of_week - INTERVAL 2 WEEK + INTERVAL 1 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS second_day
) 
 week3 
CROSS JOIN (
  SELECT 
      DATE_FORMAT(@first_date_of_week - INTERVAL 3 WEEK, '%d/%m/%Y') AS first_day
    , DATE_FORMAT(@first_date_of_week - INTERVAL 3 WEEK + INTERVAL 1 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS second_day
) 
 week4  
CROSS JOIN (
  SELECT 
      DATE_FORMAT(@first_date_of_week - INTERVAL 4 WEEK, '%d/%m/%Y') AS first_day
    , DATE_FORMAT(@first_date_of_week - INTERVAL 4 WEEK + INTERVAL 1 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS second_day
) 
 week5  
CROSS JOIN (
  SELECT 
      DATE_FORMAT(@first_date_of_week - INTERVAL 5 WEEK, '%d/%m/%Y') AS first_day
    , DATE_FORMAT(@first_date_of_week - INTERVAL 5 WEEK + INTERVAL 1 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS second_day
) 
 week6   

CROSS JOIN (
  SELECT
      @date := STR_TO_DATE('12/10/2017', '%d/%m/%Y') AS DATE
    , @first_date_of_week := @date - INTERVAL (DAYOFWEEK(@date) - 1) DAY AS first_date_of_the_week
) init_user_params

Result

Week1                  Week2                  Week3                  Week4                  Week5                  Week6                  
---------------------  ---------------------  ---------------------  ---------------------  ---------------------  -----------------------
08/10/2017-12/10/2017  01/10/2017-07/10/2017  24/09/2017-30/09/2017  17/09/2017-23/09/2017  10/09/2017-16/09/2017  03/09/2017-09/09/2017

Upvotes: 1

Related Questions