Date Between Problem

[Babu R K]

Hi, I have a typical scenario. I want to get My query is SELECT StarRate, date FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05'; My Result is as follows StarRate date -------- ----- 1 2008-06-03 00:00:00.000 1 2008-06-04 00:00:00.000 1 2008-06-05 00:00:00.000 5 2008-06-04 00:00:00.000 5 2008-06-05 00:00:00.000 But I require the result as below StarRate date -------- ----- 1 2008-06-03 00:00:00.000 1 2008-06-04 00:00:00.000 1 2008-06-05 00:00:00.000 Because StarRate 1 has all 3 dates Thanks in advance

[Ashfield]

SELECT DISTINCT StarRate, date FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05';

Bob Ashfield Consultants Ltd

[Kjetil Svendsen]

Hi Do you want the output to be only the StarRate witch has all the dates in the date interval? Try

SELECT StarRate, date FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05'
AND StarRate in (
SELECT StarRate FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05'
GROUP BY StarRate
HAVING Count(*) = DATEDIFF("D", '2008-06-03', '2008-06-05') + 1)

This should work if it's uniqueness on StarRate and date Kjetil

[Ashfield]

Nice one. I think I misread the original post, didn't notive it was just the starrate 1's required.

Bob Ashfield Consultants Ltd

[Niraj_Silver]

hi...

SELECT StarRate, date FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05'
Order By Date

By.. Tc :-D