CODEWITHSUNDEEP
mysqlsql
user3623368
user3623368

Reputation: 63

sql select from table with date range condition

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

Upvotes: 2

Views: 1042

Answers (5)

Muhammad Waheed
Muhammad Waheed

Reputation: 1088

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;

Upvotes: 1

Sebastian Brosch
Sebastian Brosch

Reputation: 43564

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'

Upvotes: 1

iainc
iainc

Reputation: 868

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

Upvotes: 1

Aura
Aura

Reputation: 1307

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;

Upvotes: 1

hmiedema9
hmiedema9

Reputation: 988

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

Upvotes: 1

Related Questions