Need some help with check contraint in SQL2005

Hi,

I have this table which contains 2 datetime fields called Startdate & enddate

Startdate cannot contain NULL
Enddate can because it can be a single day event or a period.

If enddate is not null it must be later than startdate
i created this checkconstraint

enddate > startdate

But i also fires when enddate = NULL

Someone knows howto do this?

Cheers Wimenddate > startdate
OR
enddate IS NULL|||Thanx for your reply.

But enddate could be NULL when NOTNULL it should be later then Startdate|||Your point being? :)|||Sorry my bad, calendar control gives me 1-1-1980 instead of NUll

Thanx for ur help!!
CHeers Wimsql

Need Max date?

Here is what I have so far:
SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
SPEEDLOCATIONS.[CROSS]
FROM SPEEDLOCATIONS INNER JOIN
SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
WHERE (SPEEDLOCATIONS.CITY = 'HOU')
ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
Beltway 8-Nort 70 3/9/2004 1:36:12 PM Ella
Beltway 8-Nort 50 3/9/2004 12:47:12 PM Ella
Beltway 8-Nort 52 3/9/2004 12:47:12 PM Ella
Beltway 8-Nort 60 3/9/2004 11:45:56 AM Ella
Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
Beltway 8-Nort 70 3/9/2004 1:36:12 PM Fairbanks at US-290
Beltway 8-Nort 39 3/9/2004 12:47:12 PM Fairbanks at US-290
Beltway 8-Nort 58 3/9/2004 12:47:12 PM Fairbanks at US-290
Beltway 8-Nort 45 3/9/2004 11:45:56 AM Fairbanks at US-290
Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
Hardy Tollroad 70 3/9/2004 1:42:08 PM Aldine-Bender
Hardy Tollroad 61 3/9/2004 12:47:12 PM Aldine-Bender
Hardy Tollroad 66 3/9/2004 12:47:12 PM Aldine-Bender
Hardy Tollroad 70 3/9/2004 12:46:17 PM Aldine-Bender
I just want to select the latest date for each SPEEDLOCATIONS/.[CROSS]
Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
DavidCould you provide DDL and sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"DaveF" <dfetrow@.geodecisions.com> wrote in message
news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
> Here is what I have so far:
> SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
> SPEEDLOCATIONS.[CROSS]
> FROM SPEEDLOCATIONS INNER JOIN
> SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
> WHERE (SPEEDLOCATIONS.CITY = 'HOU')
> ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
> --
> Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
> Beltway 8-Nort 70 3/9/2004 1:36:12 PM Ella
> Beltway 8-Nort 50 3/9/2004 12:47:12 PM Ella
> Beltway 8-Nort 52 3/9/2004 12:47:12 PM Ella
> Beltway 8-Nort 60 3/9/2004 11:45:56 AM Ella
> Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
> Beltway 8-Nort 70 3/9/2004 1:36:12 PM Fairbanks at US-290
> Beltway 8-Nort 39 3/9/2004 12:47:12 PM Fairbanks at US-290
> Beltway 8-Nort 58 3/9/2004 12:47:12 PM Fairbanks at US-290
> Beltway 8-Nort 45 3/9/2004 11:45:56 AM Fairbanks at US-290
> Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
> Hardy Tollroad 70 3/9/2004 1:42:08 PM Aldine-Bender
> Hardy Tollroad 61 3/9/2004 12:47:12 PM Aldine-Bender
> Hardy Tollroad 66 3/9/2004 12:47:12 PM Aldine-Bender
> Hardy Tollroad 70 3/9/2004 12:46:17 PM Aldine-Bender
>
> I just want to select the latest date for each SPEEDLOCATIONS/.[CROSS]
> Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
> Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
> Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
>
>
> David
>|||CREATE TABLE [SPEED] (
[CITY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID] [numeric](7, 0) NOT NULL ,
[SPEEDMPH] [numeric](5, 0) NOT NULL ,
[DATETIME] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SPEEDLOCATIONS] (
[OBJECTID] [int] NULL ,
[CITY] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] NULL ,
[HWY] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HWYDIR] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CROSS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LANEID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col009] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col010] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col011] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col012] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col013] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col014] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eRk56nbHFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Could you provide DDL and sample data?
> http://www.aspfaq.com/5006
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message
> news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
>|||Here's one possible way (untested):
SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
SPEEDLOCATIONS.[CROSS]
FROM SPEEDLOCATIONS INNER JOIN
SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
WHERE (SPEEDLOCATIONS.CITY = 'HOU')
AND SPEED.DATETIME =
(SELECT MAX(S1.DATETIME)
FROM SPEED S1
JOIN SPEEDLOCATIONS SL1 ON S1.ID = S.ID
WHERE SL1.HWY = SPEEDLOCATIONS.HWY
AND SL1.[CROSS] = SPEEDLOCATIONS.[CROSS])
ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DaveF" <dfetrow@.geodecisions.com> wrote in message
news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
> Here is what I have so far:
> SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
> SPEEDLOCATIONS.[CROSS]
> FROM SPEEDLOCATIONS INNER JOIN
> SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
> WHERE (SPEEDLOCATIONS.CITY = 'HOU')
> ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
> --
> Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
> Beltway 8-Nort 70 3/9/2004 1:36:12 PM Ella
> Beltway 8-Nort 50 3/9/2004 12:47:12 PM Ella
> Beltway 8-Nort 52 3/9/2004 12:47:12 PM Ella
> Beltway 8-Nort 60 3/9/2004 11:45:56 AM Ella
> Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
> Beltway 8-Nort 70 3/9/2004 1:36:12 PM Fairbanks at US-290
> Beltway 8-Nort 39 3/9/2004 12:47:12 PM Fairbanks at US-290
> Beltway 8-Nort 58 3/9/2004 12:47:12 PM Fairbanks at US-290
> Beltway 8-Nort 45 3/9/2004 11:45:56 AM Fairbanks at US-290
> Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
> Hardy Tollroad 70 3/9/2004 1:42:08 PM Aldine-Bender
> Hardy Tollroad 61 3/9/2004 12:47:12 PM Aldine-Bender
> Hardy Tollroad 66 3/9/2004 12:47:12 PM Aldine-Bender
> Hardy Tollroad 70 3/9/2004 12:46:17 PM Aldine-Bender
>
> I just want to select the latest date for each SPEEDLOCATIONS/.[CROSS]
> Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
> Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
> Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
>
>
> David
>|||You can try using max(SPEED.DATETIME) and group by SPEEDLOCATIONS.HWY
"Adam Machanic" wrote:

> Here's one possible way (untested):
>
> SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
> SPEEDLOCATIONS.[CROSS]
> FROM SPEEDLOCATIONS INNER JOIN
> SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
> WHERE (SPEEDLOCATIONS.CITY = 'HOU')
> AND SPEED.DATETIME =
> (SELECT MAX(S1.DATETIME)
> FROM SPEED S1
> JOIN SPEEDLOCATIONS SL1 ON S1.ID = S.ID
> WHERE SL1.HWY = SPEEDLOCATIONS.HWY
> AND SL1.[CROSS] = SPEEDLOCATIONS.[CROSS])
> ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message
> news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
>
>|||I get no data back?
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OTzFcwbHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> Here's one possible way (untested):
>
> SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
> SPEEDLOCATIONS.[CROSS]
> FROM SPEEDLOCATIONS INNER JOIN
> SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
> WHERE (SPEEDLOCATIONS.CITY = 'HOU')
> AND SPEED.DATETIME =
> (SELECT MAX(S1.DATETIME)
> FROM SPEED S1
> JOIN SPEEDLOCATIONS SL1 ON S1.ID = S.ID
> WHERE SL1.HWY = SPEEDLOCATIONS.HWY
> AND SL1.[CROSS] = SPEEDLOCATIONS.[CROSS])
> ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message
> news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
>|||"DaveF" <dfetrow@.geodecisions.com> wrote in message
news:OgZfhTcHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> I get no data back?
?
You posted no sample data, so I was unable to test.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

need help-how to call a function by date in SQL server

I am writing a project in .NET.
I want to check every minute two fields in the data base:
time and start date
time end end date
if in the check the date and time arrives I want to call a function automaticly.
what is the best way to do it in windows service?
maybe directly from the database?
I would like to get sugestions to do it
thanksWell, you can fire a trigger in the db when a new record is added to the table. In that trigger you could shell out to run a .NET app that fires an event that the Windows service is watching for. Or you could poll the db from the Windows service every few seconds to see if there is new data.

Any of these work for you?

Don|||Well, you could also just add a job to the database that starts a stored procedure every x minutes.

In this particular case, the sp_add_job system procedure is agood place to start.