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
 Transact-SQL (2000)
 I Hate Dates (and Prunes, too!)

Author  Topic 

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-06-21 : 18:03:17
I have code that combines the data from 3 tables to create a new one called CURRENT. In this process, a new column called "Days" is to be calculated depending on the dates two input columns. Id the result is POSITIVE, then the result of the computation is accepted, else a ZERO is the value for "Days". Unfortunately, my function for these two dates aren't working...here's a simplified example:

.....the code............
SELECT a.track
, a.event_date
, c.max_dt
, fnMax(0, CAST(a.event_date AS int) - CAST(c.max_dt AS int)) AS Days
INTO #Y_TEST
from [PLF].[dbo].[Events] a
, [PLF].[dbo].[Other] b
, [PLF].[dbo].[Tix] c
where a.event_main_id = b.event_main_id
and b.track = c.track
and b.event_id = c.event_id
GO


and my function........
CREATE FUNCTION [dbo].[fnMax](@p1 INT,@p2 INT)
RETURNS IN
AS BEGIN
DECLARE @Result INT
SET @p2=COALESCE(@p2,@p1)
SELECT
@Result = (SELECT CASE WHEN @p1>@p2 THEN @p1 ELSE @p2 END)
RETURN @Result
END


Any help you can offer is much appreciated!



Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-21 : 18:28:08
Why are you casting the dates as integers? Why aren't you using the DateDiff() function?
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-06-21 : 21:31:31
Because datediff() gives an absolute value. I have to know if the event_date is less than the max_dt--if it is negative, then I need to put a zero in Days.


Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-21 : 22:18:04
[code]
SELECT a.track
, a.event_date
, c.max_dt
, fnMax(0, CAST(a.event_date AS int) - CAST(c.max_dt AS int)) AS Days
, case when a.event_date > c.max_dt then datediff(day, c.max_dt, a.event_date) else 0 end AS Days
INTO #Y_TEST
FROM [PLF].[dbo].[Events] a
JOIN [PLF].[dbo].[Other] b ON a.event_main_id = b.event_main_id
JOIN [PLF].[dbo].[Tix] c ON b.track = c.track
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-06-22 : 12:15:50
Thanks, khtan!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-23 : 03:33:04
quote:
Originally posted by Xerxes

Thanks, khtan!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!


Probably you will now love Dates (and Prunes, too)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -