sql select from table with date range condition

Question

I have this table

CREATE TABLE `tarif` (                  
  `tarif_id` int(11) NOT NULL AUTO_INCREMENT,
  `start_tarif` date NOT NULL,
  `end_tarif` date NOT NULL,
  `day_tarif` varchar(50) NOT NULL,
  PRIMARY KEY (`tarif_id`)
);

INSERT INTO `tarif` VALUES (1, '2019-02-01', '2019-02-10', '10'),
                           (2, '2019-02-11', '2019-02-20', '20'),
                           (3, '2019-02-21', '2019-02-28', '10'),
                           (4, '2019-03-01', '2019-02-10', '15');

How can I get day_tarif between 2019-02-05 and 2019-02-15 and calculate the sum = day_tarif * number of date betwwen 2019-02-05 and 2019-02-15

It's not a free coding site. Kindly share your efforts first then ask for specific help from others. — Muhammad Waheed
– Muhammad Waheed, Commented Feb 6, 2019 at 13:59
@a_horse_with_no_name considering the backticks, this is probably MySQL or MariaDB — Cid
– Cid, Commented Feb 6, 2019 at 14:01
What have you already tried? Please share any code you already have written. — Mathyn
– Mathyn, Commented Feb 6, 2019 at 14:07
i work with mysql database,i try this SELECT * FROM tarif WHERE start_tarif<='2019-02-15' && end_tarif>='2019-02-05',it work, but i can't get the product of number of day between 2019-02-05 and 2019-02-15 and the value of day_tarif — user3623368
– user3623368, Commented Feb 6, 2019 at 14:44

Sebastian Brosch · Accepted Answer · 2019-02-06 15:40:44Z

1

You can use the following solution using DATEDIFF:

SELECT SUM((DATEDIFF(LEAST(`end_tarif`, '2019-02-15'), GREATEST(`start_tarif`, '2019-02-05')) + 1) * `day_tarif`) AS sumCustom
FROM `tarif` 
WHERE `end_tarif` >= '2019-02-05' AND `start_tarif` <= '2019-02-15'

demo on dbfiddle.uk

~~You can use the following solution using SUM and COUNT:~~

~~SELECT SUM(`day_tarif`) * COUNT(`tarif_id`) FROM `tarif` WHERE `end_tarif` >= '2019-02-05' AND `start_tarif` <= '2019-02-15'~~

edited Feb 6, 2019 at 15:40

answered Feb 6, 2019 at 14:01

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3623368 Over a year ago

here you multiply number of day with the sum of tarif_day in the line 1 an line2,that means 11*(10+20)=330. Or we need (6*10)+(5*20)=160

Sebastian Brosch Over a year ago

@user3623368 - You're welcome! Next time try to describe your issue more detailed and with the expected value or result. Many people want to help but only can help (useful) if they unterstand the issue and the expected logic. - helpful for future questions

user3623368 Over a year ago

now i wont to get number of monday in this rangedate , i run this code but it give me result 0; select count(*) from tarif where weekday(end_tarif<= '2019-02-21'AND start_tarif>='2019-02-05') = 0;

Sebastian Brosch Over a year ago

@user3623368 you want to exclude mondays from days count (days count - count mondays)?

user3623368 Over a year ago

if i have new column contain monday_tarif different from other day_tarif, to calculate global tarif i must specify number of monday and multiply it with mondy_tarif,and the rest of day will be multiply with day_tarif

hmiedema9 · Accepted Answer · 2019-02-06 14:02:30Z

1

select SUM(day_tarif) * COUNT(tarif_id) 
from tarif
where end_date >= '2019-02-05' AND start_date <= '2019-02-15'

This question has been asked several times, or something like it. Be sure to search StackOverflow before asking a duplicate: SQL query to select dates between two dates

edited Feb 6, 2019 at 14:02

answered Feb 6, 2019 at 13:58

hmiedema9

1,0185 silver badges17 bronze badges

2 Comments

Muhammad Waheed Over a year ago

now you are missing the day_tarif itself in the select list.

user3623368 Over a year ago

thanks for your answer, but you have not understand what i want,day tarif is not a date, it's varchar, and i want get it between 2019-02-05 and 2019-02-15, the start date 2019-02-05 contained betwwen start_tarif and end_tarif in the first line,and the end date 2019-02-15 contained betwwen start_tarif and end_tarif in the second line .that is the problem that i won't solve.

Aura · Accepted Answer · 2019-02-06 14:09:54Z

1

You can try following code:

 WITH cte AS(
 SELECT *
 FROM tablename
 WHERE end_tarif >= '2019-02-05' AND start_tarif <= '2019-02-15'
 )
 SELECT day_tarif, day_tarif * COUNT(tarif_id) AS 'SUM'
 FROM cte
 group by day_tarif;

edited Feb 6, 2019 at 14:09

answered Feb 6, 2019 at 14:04

Aura

1,3032 gold badges18 silver badges31 bronze badges

2 Comments

Muhammad Waheed Over a year ago

sample data shows that start_tarif should be associated with lowest_Date value and end_tarif with highest_date value. What do you say?

Aura Over a year ago

Hi @MuhammadWaheed, thanks for bringing it to my attention! Updated my answer.

iainc · Accepted Answer · 2019-02-06 15:02:21Z

I think this might be what you are after...

Declare @tarif as table (                  
  tarif_id int NOT NULL ,
  start_tarif date NOT NULL,
  end_tarif date NOT NULL,
  day_tarif varchar(50) NOT NULL
);

INSERT INTO @tarif VALUES (1, '2019-02-01', '2019-02-10', '10'),
                           (2, '2019-02-11', '2019-02-20', '20'),
                           (3, '2019-02-21', '2019-02-28', '10'),
                           (4, '2019-03-01', '2019-02-10', '15');

-- Declare parameters
Declare @paramstart date, @paramend date
Set @paramstart='2019-02-05'
Set @paramend='2019-02-15'

-- Set up loop
Declare @mincount int, @maxcount int, @myval int, @curstart date, @curend date,@curtarif int, @mytarif int

Set @mincount=(Select MIN(tarif_id) from @tarif where  end_tarif >= '2019-02-05' AND start_tarif <= '2019-02-15')
Set @maxcount=(Select Max(tarif_id) from @tarif where  end_tarif >= '2019-02-05' AND start_tarif <= '2019-02-15')
Set @mytarif=0


-- Do loop
WHile @mincount<=@maxcount
BEGIN

Set @curstart=(Select start_tarif from @tarif where  tarif_id=@mincount)
Set @curend=(Select end_tarif from @tarif where  tarif_id=@mincount)
Set @curtarif=(Select cast(day_tarif as int) from @tarif where  tarif_id=@mincount)

IF @paramstart between @curstart and @curend 
    BEGIN
    Set @mytarif=@mytarif+((DATEDIFF(day,@paramstart,@curend)+1) * @curtarif)
    END

IF @paramend between @curstart and @curend 
    BEGIN
    Set @mytarif=@mytarif+((DATEDIFF(day,@curstart,@paramend)+1) * @curtarif)
    END

IF @paramstart not between @curstart and @curend  and @paramend not between @curstart and @curend
    BEGIN
    Set @mytarif=@mytarif+((DATEDIFF(day,@curstart,@curend)+1) * @curtarif)
    END

Set @mincount=@mincount+1
END

Select @mytarif as tarif

Muhammad Waheed · Accepted Answer · 2019-02-07 05:06:56Z

1

Try this!

select day_tarif , sum(day_tarif * count (*)) as sum
where start_tarif between '2019/02/05' and '2019/02/15' and end_tarif between '2019/02/05' and '2019/02/15'
group by day_tarif;

edited Feb 7, 2019 at 5:06

answered Feb 6, 2019 at 14:02

Muhammad Waheed

1,0882 gold badges16 silver badges32 bronze badges

3 Comments

user3623368 Over a year ago

thanks, but it dont work,syntaxe error" Erreur de syntaxe prÃ¨s de 'distinct date )) as sum where start_tarif between '2019/02/05' and '2019/02/15'' Ã la ligne 1"

Muhammad Waheed Over a year ago

Can you please translate the error message in English language ?

user3623368 Over a year ago

syntaxe error near '*)) as sum where start_tarif between '2019/02/05' and '2019/02/15' and end_tari' at line 1

Collectives™ on Stack Overflow

sql select from table with date range condition

5 Answers 5

5 Comments

2 Comments

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

2 Comments

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related