How to exclude Weekend and compare current dates in MYSQL-CodePudding

I'm comparing data with current date - 1 and -2 dates in my table. Sample Data :

Name  cnt  Dates
Mills 20    2022-07-15
Mills 15    2022-07-14
Mills 10    2022-07-13

I have written query where it compares current date - 1 and Current date - 2

SELECT Name,Date,`T-2`,`T-1`,Diff
    FROM (
SELECT  T.Name  ,T.Date,T.`T-1`,TT.`T-2`,TT.`T-2` - T.`T-1`  AS Diff FROM 
(select  Name, Date, cnt AS 'T-1' from Acct where date = curdate() - 1 )T
INNER JOIN 
(select  Name, Date, cnt AS 'T-2' from Acct where date = curdate() - 2)TT
ON T.Name = TT.Name )T

Now when I execute this query Current date (2022-07-18) it is taking -1 as 2022-07-17 and -2 as 2022-07-16. I want to exclude weekends and compare those with Friday and Thursday .

Out put :

Name   T-1   T-2  DIFF 
Mills   20   15    5

CodePudding user response：

Replace curdate() - 1 with conditional expression curdate() - case weekday(curdate()) when 0 then 3 when 6 then 2 else 1 end. Similar for curdate() - 2

SELECT Name,Date,`T-2`,`T-1`,Diff
    FROM (
SELECT  T.Name  ,T.Date,T.`T-1`,TT.`T-2`,TT.`T-2` - T.`T-1`  AS Diff FROM 
(select  Name, Date, cnt AS 'T-1' 
 from Acct 
 where date = curdate() - case weekday(curdate()) when 0 then 3 when 6 then 2 else 1 end) T
INNER JOIN 
(select  Name, Date, cnt AS 'T-2' 
 from Acct 
 where date = curdate() - case weekday(curdate()) when 0 then 4 when 1 then 4 when 6 then 3 else 2 end) TT
ON T.Name = TT.Name ) T

weekday()

CodePudding user response：

SET @zi = curdate();
SET @x =   DAYOFWEEK(@zi);
SELECT @x;

case when  @x = 2 then  date = curdate() - 3 else  date = curdate() - 1 end