Select current date range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select current date range

Author

Topic

djavet
Starting Member

36 Posts

Posted - 2006-07-19 : 10:53:08

Hello

I wish to make a SELECT with the result between ReservationVon and ReservationBis for the current date (today).
In fact, give all the Equipement_Name for today range.

How could I make this?
A lot of thx for your help and time.

Regards,
Dominique

My Query:

SELECT 
  dbo.Reservation.ReservationVon,
  dbo.Reservation.ReservationBis,
  dbo.Workspace.Name AS Equipement_Name,
FROM
  dbo.Reservation
  INNER JOIN dbo.Person ON (dbo.Reservation.ReservatorOID = dbo.Person.OID) AND (dbo.Reservation.AdressatOID = dbo.Person.OID)
  INNER JOIN dbo.Workspace_Reservation ON (dbo.Reservation.OID = dbo.Workspace_Reservation.ReservationOID)
  INNER JOIN dbo.Workspace ON (dbo.Workspace_Reservation.WorkspaceOID = dbo.Workspace.OID)
WHERE
  (dbo.Workspace.Name LIKE 'Ber-Lau10_Mob_%')
ORDER BY
  dbo.Reservation.ReservationVon,
  Equipement_Name,
  dbo.Person.Name

Result:

ReservationVon		ReservationBis		Equipement_Name
02.03.2006 07:40:00	02.03.2006 18:00:00	Ber-Lau10_Mob_Beamer_A
02.03.2006 07:40:00	02.03.2006 18:00:00	Ber-Lau10_Mob_Laptop_5
02.03.2006 10:00:00	02.03.2006 12:00:00	Ber-Lau10_Mob_Beamer_A
06.03.2006 08:00:00	06.03.2006 18:00:00	Ber-Lau10_Mob_Laptop_3
06.03.2006 10:00:00	06.03.2006 11:00:00	Ber-Lau10_Mob_Laptop_1
08.03.2006 11:00:00	08.03.2006 12:00:00	Ber-Lau10_Mob_Beamer_B
08.03.2006 11:00:00	08.03.2006 12:00:00	Ber-Lau10_Mob_Beamer_B
08.03.2006 11:00:00	08.03.2006 12:00:00	Ber-Lau10_Mob_Beamer_B
08.03.2006 11:00:00	08.03.2006 12:00:00	Ber-Lau10_Mob_Beamer_B
08.03.2006 13:00:00	08.03.2006 16:00:00	Ber-Lau10_Mob_Laptop_10
08.03.2006 13:00:00	08.03.2006 16:00:00	Ber-Lau10_Mob_Laptop_4
08.03.2006 14:00:00	08.03.2006 15:00:00	Ber-Lau10_Mob_Beamer_B
etc...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-19 : 11:05:49

I'm not clear on what you mean, but maybe it's this...?

declare @StartOfToday datetime, @StartOfTomorrow datetime
select @StartOfToday = dateadd(day, datediff(day, 0, getdate()), 0),
       @StartOfTomorrow = dateadd(day, datediff(day, 0, getdate())+1, 0)

SELECT 
  r.ReservationVon,
  r.ReservationBis,
  w.Name AS Equipement_Name,
FROM
  dbo.Reservation r
  INNER JOIN dbo.Person p ON (r.ReservatorOID = p.OID) AND (r.AdressatOID = p.OID)
  INNER JOIN dbo.Workspace_Reservation wr ON (r.OID = wr.ReservationOID)
  INNER JOIN dbo.Workspace w ON (wr.WorkspaceOID = w.OID)
WHERE (    (r.ReservationVon >= @StartOfToday and r.ReservationVon < @StartOfTomorrow)
        or (r.ReservationBis >= @StartOfToday and r.ReservationBis < @StartOfTomorrow))
  and (w.Name LIKE 'Ber-Lau10_Mob_%')
ORDER BY
  r.ReservationVon,
  Equipement_Name,
  p.Name

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-19 : 11:08:27

Or maybe this...?

declare @StartOfToday datetime, @StartOfTomorrow datetime
select @StartOfToday = dateadd(day, datediff(day, 0, getdate()), 0),
       @StartOfTomorrow = dateadd(day, datediff(day, 0, getdate())+1, 0)

SELECT 
  r.ReservationVon,
  r.ReservationBis,
  w.Name AS Equipement_Name,
FROM
  dbo.Reservation r
  INNER JOIN dbo.Person p ON (r.ReservatorOID = p.OID) AND (r.AdressatOID = p.OID)
  INNER JOIN dbo.Workspace_Reservation wr ON (r.OID = wr.ReservationOID)
  INNER JOIN dbo.Workspace w ON (wr.WorkspaceOID = w.OID)
WHERE (r.ReservationVon < @StartOfTomorrow and r.ReservationBis >= @StartOfToday)
  and (w.Name LIKE 'Ber-Lau10_Mob_%')
ORDER BY
  r.ReservationVon,
  Equipement_Name,
  p.Name

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

djavet
Starting Member

36 Posts

Posted - 2006-07-24 : 02:41:39

Thx for your help.
But my MS SQL Server doesnt recognize "Declare" in SQL Analyser.
Is that normal?

Dom

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 03:30:50

quote:
Originally posted by djavet

Thx for your help.
But my MS SQL Server doesnt recognize "Declare" in SQL Analyser.
Is that normal?

Dom

Definately not normal.

Post the query that is giving the error message

djavet
Starting Member

36 Posts

Posted - 2006-07-24 : 03:44:48

Hummm I found a wrong comma into.
Into Analyser, it's working with

declare @StartOfToday datetime, @StartOfTomorrow datetime
select @StartOfToday = dateadd(day, datediff(day, 0, getdate()), 0),
       @StartOfTomorrow = dateadd(day, datediff(day, 0, getdate())+1, 0)

SELECT 
  r.ReservationVon,
  r.ReservationBis,
  w.Name AS Equipement_Name
FROM
  dbo.Reservation r
  INNER JOIN dbo.Person p ON (r.ReservatorOID = p.OID) AND (r.AdressatOID = p.OID)
  INNER JOIN dbo.Workspace_Reservation wr ON (r.OID = wr.ReservationOID)
  INNER JOIN dbo.Workspace w ON (wr.WorkspaceOID = w.OID)
WHERE (r.ReservationVon < @StartOfTomorrow and r.ReservationBis >= @StartOfToday)
  and (w.Name LIKE 'Ber-Lau10_Mob_%')
ORDER BY
  r.ReservationVon,
  Equipement_Name,
  p.Name

But now, how can I turn this into a View?
When I try to do that, I receive Querey Designer doesnt recognize "Declare" ...

Thx for your help.
Dominique

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 03:48:22

quote:

But now, how can I turn this into a View?

You can try Pl SQL in the View.

Alternate solution is that, either use Temp Tables or stored procedure.

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 04:05:45

"Pl SQL "
I am confuse. Is this for Oracle or MS SQL ?

djavet
Starting Member

36 Posts

Posted - 2006-07-24 : 04:07:18

Ooops... sorry it's MS SQL

Dom

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 04:15:56

opps mistake

You Can Not do Pl SQL in view

Chirag

djavet
Starting Member

36 Posts

Posted - 2006-07-24 : 04:37:27

Thx. But do you have a solution for a query with the today date as filter?

Regards, Dominique

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 05:05:22

Somthing like this ?????


Create View DataFilter 
As 
SELECT 
  r.ReservationVon,
  r.ReservationBis,
  w.Name AS Equipement_Name
FROM
  dbo.Reservation r
  INNER JOIN dbo.Person p ON (r.ReservatorOID = p.OID) AND (r.AdressatOID = p.OID)
  INNER JOIN dbo.Workspace_Reservation wr ON (r.OID = wr.ReservationOID)
  INNER JOIN dbo.Workspace w ON (wr.WorkspaceOID = w.OID)
WHERE (r.ReservationVon < dateadd(day, datediff(day, 0, getdate())+1, 0) and r.ReservationBis >= dateadd(day, datediff(day, 0, getdate()), 0))
  and (w.Name LIKE 'Ber-Lau10_Mob_%')
ORDER BY
  r.ReservationVon,
  Equipement_Name,
  p.Name

or if you want the datefilter based on the 2 different dates then,
you can create the stored procedure ... and in the procedure insert
the values in the dummy table and then just select from that table.

Chirag

djavet
Starting Member

36 Posts

Posted - 2006-07-24 : 05:11:57

Exactly!
Thx a lot.

Regards, Dom

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-24 : 11:30:22

http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources