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, Feb 06 '19 at 13:59
@a_horse_with_no_name considering the backticks, this is probably MySQL or MariaDB — Cid, Feb 06 '19 at 14:01
What have you already tried? Please share any code you already have written. — Mathyn, Feb 06 '19 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, Feb 06 '19 at 14:44
@user3623368 the second line contains only 5 days until `2019-02-15` (not `2019-02-20`)? — Sebastian Brosch, Feb 06 '19 at 15:20
betwwen 2019-02-05 and 2019-02-15 ihave 16day , the first six day are include in the daterange from the first line where day_tarif=10,so the product of six first day mulitiplicate with day_tarif give me 60,the rest of days are includes in the date range from the second line where day_tarif=20,so the product is 20*5=100, the total result must be 160. that is my expected result.and thankyou all for your help — user3623368, Feb 06 '19 at 15:24

hmiedema9 · Answer 1 · 2019-02-06T14:02:30.093

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 06 '19 at 14:02

answered Feb 06 '19 at 13:58

hmiedema9

948
4
17

now you are missing the `day_tarif` itself in the select list. – Muhammad Waheed Feb 06 '19 at 14:08
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. – user3623368 Feb 06 '19 at 14:10

Sebastian Brosch · Accepted Answer · 2019-02-06T15:40:44.537

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 06 '19 at 15:40

answered Feb 06 '19 at 14:01

Sebastian Brosch

42,106
15
72
87

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 – user3623368 Feb 06 '19 at 15:39
@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](https://stackoverflow.com/help/how-to-ask) – Sebastian Brosch Feb 06 '19 at 15:45
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; – user3623368 Feb 07 '19 at 09:18
@user3623368 you want to exclude mondays from days count (days count - count mondays)? – Sebastian Brosch Feb 07 '19 at 09:22
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 – user3623368 Feb 07 '19 at 09:29

Muhammad Waheed · Answer 3 · 2019-02-07T05:06:56.670

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 07 '19 at 05:06

answered Feb 06 '19 at 14:02

Muhammad Waheed

1,048
1
13
30

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" – user3623368 Feb 06 '19 at 15:26
Can you please translate the error message in English language ? – Muhammad Waheed Feb 07 '19 at 05:05
syntaxe error near '*)) as sum where start_tarif between '2019/02/05' and '2019/02/15' and end_tari' at line 1 – user3623368 Feb 07 '19 at 08:09

Aura · Answer 4 · 2019-02-06T14:09:54.157

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 06 '19 at 14:09

answered Feb 06 '19 at 14:04

Aura

1,283
2
16
30

sample data shows that `start_tarif` should be associated with lowest_Date value and `end_tarif` with highest_date value. What do you say? – Muhammad Waheed Feb 06 '19 at 14:07
Hi @MuhammadWaheed, thanks for bringing it to my attention! Updated my answer. – Aura Feb 06 '19 at 14:10

score 1 · Answer 5 · answered Feb 06 '19 at 15:02

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

sql select from table with date range condition

5 Answers5