SQL server 2000 - any gurus?

[#1]

i have a query running on SQL server 2000

declare @selecteddate datetime
set @selecteddate = '13 March 2017'

select

diary_engineer_id, diary_start_date, diary_end_date
from diary with (nolock)

where diary_link_to_nonprod in ('HO','LD')
and (@selecteddate <= diary_end_date) and (@selecteddate >= diary_start_date)
and diary_engineer_id in ('C000021','GB','GC','JA','JMCF','KYLEM')

order by diary_engineer_id, diary_end_date

which produces the result

C000021	2017-03-13 00:00:00.000	2017-03-17 00:00:00.000
GB	2017-03-13 00:00:00.000	2017-03-13 00:00:00.000
GC	2017-03-13 00:00:00.000	2017-03-13 00:00:00.000
JA	2017-03-13 00:00:00.000	2017-03-17 00:00:00.000
JMCF	2017-03-13 00:00:00.000	2017-03-13 00:00:00.000
KYLEM	2017-03-13 00:00:00.000	2017-03-13 00:00:00.000

what i need it to do is produce a row for each of the dates within the daterange in each resulting row.
so essentially in this example i would want it to produce

C000021	2017-03-13 00:00	2017-03-13 00:00
C000021	2017-03-14 00:00	2017-03-14 00:00
C000021	2017-03-15 00:00	2017-03-15 00:00
C000021	2017-03-16 00:00	2017-03-16 00:00
C000021	2017-03-17 00:00	2017-03-17 00:00
GB	2017-03-13 00:00	2017-03-13 00:00
GC	2017-03-13 00:00	2017-03-13 00:00
JA	2017-03-13 00:00	2017-03-13 00:00
JA	2017-03-14 00:00	2017-03-14 00:00
JA	2017-03-15 00:00	2017-03-15 00:00
JA	2017-03-16 00:00	2017-03-16 00:00
JA	2017-03-17 00:00	2017-03-17 00:00
JMCF	2017-03-13 00:00	2017-03-13 00:00
KYLEM	2017-03-13 00:00	2017-03-13 00:00

is this possible?

[#2]

Very possible,

several solutions here:

http://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates

[#3]

actually that isn't quite the same thing.
as i am only passing 1 date into the query, not two.

i am not looking for the dates between two date variables.

[#4]

I would set up a calendar with all the possible dates and then cross join the results "where calendar.date between diary_start_date and diary_end_date"

[#5]

@chenks76 - What happens when you change the order of the predicate, from :

and (@selecteddate <= diary_end_date) and (@selecteddate >= diary_start_date)

to something like :

and (diary_end_date <= @selecteddate) and (diary_start_date >= @selecteddate)

[#6]

Incidently, why not instead of

and (@selecteddate <= diary_end_date) and (@selecteddate >= diary_start_date)

did you not use

and (@selecteddate between diary_start_date and diary_end_date)

which is much easier to read?

[#7]

@Anonymous That's still only going to return one row for each row in diary.

[#8]

---

@Anonymous wrote:

@chenks76 - What happens when you change the order of the predicate, from :

and (@selecteddate <= diary_end_date) and (@selecteddate >= diary_start_date)

to something like :

and (diary_end_date <= @selecteddate) and (diary_start_date >= @selecteddate)

---

that produces 2 less records, which would be a wrong outcome.
and doesn't give the desired multiple rows anyway.

[#9]

---

@jelv wrote:

Incidently, why not instead of

and (@selecteddate <= diary_end_date) and (@selecteddate >= diary_start_date)

did you not use

and (@selecteddate between diary_start_date and diary_end_date)

which is much easier to read?

---

that produces the same results for the test sample. but the ease of the readability is not my primary concern at the moment

[#10]

---

@jelv wrote:

I would set up a calendar with all the possible dates and then cross join the results "where calendar.date between diary_start_date and diary_end_date"

---

will that not add an extra overhead on the run time of the script?
the supplied query is just a small test sample, but in theory it could include many rows.

[#11]

True but you could get the diary start date and end date into variables as a first step and then use those dates to generate the data you want.

something like this;

select 
    @diary_start_date_var = diary_start_date,
    @diary_end_date_var = diary_end_date
from
    diary
where
    (@selecteddate <= diary_end_date) and (@selecteddate >= diary_start_date)

[#12]

but where are these two new variable getting the data from?
only the original varibiale called "selecteddate" is passed into the query (which comes from elsewhere).

only diary_start_Date will always be the same (from the original variable)
diary_end_date could be anything.

[#13]

Exactly @chenks76 so it proves that the ordering within the predicate is important. You can now work the rest out knowing that.

[#14]

The overheads of a calendar table would be minimal and you'll have the dates as the primary key so the join will be quick.

[#15]

---

@Anonymous wrote:

Exactly @chenks76 so it proves that the ordering within the predicate is important. You can now work the rest out knowing that.

---

i'm not sure i follow your thought process there.
my original ordering gives the correct outcome, your amended one gives the wrong outcome.