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.
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 DaysINTO #Y_TESTfrom [PLF].[dbo].[Events] a, [PLF].[dbo].[Other] b, [PLF].[dbo].[Tix] cwhere a.event_main_id = b.event_main_idand b.track = c.trackand b.event_id = c.event_idGOand my function........CREATE FUNCTION [dbo].[fnMax](@p1 INT,@p2 INT)RETURNS INAS BEGIN DECLARE @Result INT SET @p2=COALESCE(@p2,@p1) SELECT @Result = (SELECT CASE WHEN @p1>@p2 THEN @p1 ELSE @p2 END) RETURN @ResultENDAny 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? |
|
|
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! |
|
|
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 DaysINTO #Y_TESTFROM [PLF].[dbo].[Events] aJOIN [PLF].[dbo].[Other] b ON a.event_main_id = b.event_main_idJOIN [PLF].[dbo].[Tix] c ON b.track = c.track[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
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! |
|
|
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) MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|