Re: simple SQL query question

Dale E. Reed Jr. ( (no email) )
Mon, 24 Mar 1997 13:21:03 -0800

Josh Hillman wrote:
>
> When executing a SQL query that has many mentions of the same dates/times
> throughout it, can that date be replaced by "variable"? In other words
> something like this:
>
> startdate = "Mar 17 1997 12:00AM"
> enddate = "Mar 24 1997 12:00AM"
>
> select somecolumns
> from sometable
> where date > startdate
> and date < enddate
>
> select someothercolumns
> from someothertable
> where date > startdate
> and date < enddate
>
> This is no big deal if the dates are only mentioned once, but if the same
> ones are all over the place and the dates need to be adjusted from week to
> week, redoing all the dates can drive me crazy...

Yes. Here is one I use alot (i'll leave it as an exercise of the user
to figure out what it does). :)

DECLARE @timeon smalldatetime, @timeon smalldatetime, @userip char(16)

Select @timeon = "11/9/96 16:00"
Select @timeoff = "11/9/96 22:00"
Select @userip = "204.227.164.2"

Select UserName, Start=DateAdd(Second,
(0-AcctSessionTime-AcctDelayTime), CallDate), Mins=(AcctSessionTime/60),
Stop=DateAdd(Second, (0-AcctDelayTime), CallDate),
Server=SubString(Server, 1, 15), Port=Str(NASPort,4), IP=FramedAddress

from calls, servers
where DateAdd(Second, (0-AcctSessionTime-AcctDelayTime), CallDate) >=
@timeon
and DateAdd(Second, (0-AcctDelayTime), CallDate) <= @timeoff
and AcctStatusType = 2
and calls.NASIdentifier=servers.ipaddress
and FramedAddress = @userip
Order By Start

GO

-- Dale E. Reed Jr.  (daler@iea.com)_________________________________________________________________       IEA Software, Inc.      |  RadiusNT, Emerald, and NT FAQs Internet Solutions for Today  |    http://www.emerald.iea.com