cancel
Showing results for 
Search instead for 
Did you mean: 

SQL server 2000 - any gurus?

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

SQL server 2000 - any gurus?

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?

Tags (1)
26 REPLIES
mike4262
Hooked
Posts: 8
Registered: 30-03-2017

Re: SQL server 2000 - any gurus?

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: SQL server 2000 - any gurus?

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.
Community Veteran
Posts: 26,453
Thanks: 697
Fixes: 8
Registered: 10-04-2007

Re: SQL server 2000 - any gurus?

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"

jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£13/month)
Mobile: iD mobile (£4/month)
Community Veteran
Posts: 4,988
Thanks: 1,154
Fixes: 29
Registered: 16-10-2014

Re: SQL server 2000 - any gurus?

@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)

 

Community Veteran
Posts: 26,453
Thanks: 697
Fixes: 8
Registered: 10-04-2007

Re: SQL server 2000 - any gurus?

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?

jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£13/month)
Mobile: iD mobile (£4/month)
Community Veteran
Posts: 26,453
Thanks: 697
Fixes: 8
Registered: 10-04-2007

Re: SQL server 2000 - any gurus?

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

jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£13/month)
Mobile: iD mobile (£4/month)
Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: SQL server 2000 - any gurus?


Mook 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.

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: SQL server 2000 - any gurus?


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 Cheesy

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: SQL server 2000 - any gurus?


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.

mike4262
Hooked
Posts: 8
Registered: 30-03-2017

Re: SQL server 2000 - any gurus?

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)
Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: SQL server 2000 - any gurus?

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.
Community Veteran
Posts: 4,988
Thanks: 1,154
Fixes: 29
Registered: 16-10-2014

Re: SQL server 2000 - any gurus?

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

Community Veteran
Posts: 26,453
Thanks: 697
Fixes: 8
Registered: 10-04-2007

Re: SQL server 2000 - any gurus?

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.

jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£13/month)
Mobile: iD mobile (£4/month)
Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: SQL server 2000 - any gurus?


Mook 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.