May 7, 2008 at 8:30 am
I have interesting scenario and would like to get some help on how to approach this problem.
I have a table with a date field called AuthDate. Which needs to be updated from whatever is the greatest date value in the three other fields and then use first of that month to populate Authdate.
here is the logic:
update authdate in table based on following conditions
1) check bdate in table B
2) check cdate in table C
3) check ddate in table D
4) determine which date is max of the three dates from step 1,2, and 3 and used first of the month to populate the authdate in table A. so if bdate=02-28-2008 , cdate=03-15-2008 and cdate=04-15-2008 then max of the three dates is 04-15-2008. and authdate field in table A should be updated with the value of 01-04-2008.
any ideas how to tackle this in tsql?
May 7, 2008 at 8:56 am
Would you please provide a little more detail into what you are doing? Specifically, how are the four tables related (just need enough of the DDL to understand the relationships, not all the fields), when is the update to authdate in tableA supposed to occur, and anything else you feel will help with understanding your problem better.
😎
May 7, 2008 at 9:03 am
I'd put it in function and then pass the function the three date values
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufn_MaxDate] (@DateA DATETIME, @DateB DATETIME, @DateC DATETIME)
RETURNS DATETIME
BEGIN
DECLARE @ReturnDate DATETIME,
BEGIN
IF @DateA >= @DateB
BEGIN
SELECT @ReturnDate = @DateA
END
IF @DateB >= @DateA
BEGIN
SELECT @ReturnDate = @DateB
END
IF @DateB >= @ReturnDate
BEGIN
SELECT @ReturnDate = @DateB
END
END
RETURN @ReturnDate
END
SELECT
dbo.[ufn_MaxDate] ('3-Sep-07', '10-Feb-09', '12-Jan-11') AS 'MaxDate'
Ooops, didn't see the set it to the first of the month part...let me go back to the drawing board
Marvin Dillard
Senior Consultant
Claraview Inc
May 7, 2008 at 9:15 am
MD (5/7/2008)
I'd put it in function and then pass the function the three date valuesSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufn_MaxDate] (@DateA DATETIME, @DateB DATETIME, @DateC DATETIME)
RETURNS DATETIME
BEGIN
DECLARE @ReturnDate DATETIME,
BEGIN
IF @DateA >= @DateB
BEGIN
SELECT @ReturnDate = @DateA
END
IF @DateB >= @DateA
BEGIN
SELECT @ReturnDate = @DateB
END
IF @DateB >= @ReturnDate
BEGIN
SELECT @ReturnDate = @DateB
END
END
RETURN @ReturnDate
END
SELECT
dbo.[ufn_MaxDate] ('3-Sep-07', '10-Feb-09', '12-Jan-11') AS 'MaxDate'
Ooops, didn't see the set it to the first of the month part...let me go back to the drawing board
Simple change to your function, see code below, but I'd like more info before venturing a possible solution.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufn_MaxDate] (@DateA DATETIME, @DateB DATETIME, @DateC DATETIME)
RETURNS DATETIME
BEGIN
DECLARE @ReturnDate DATETIME,
BEGIN
IF @DateA >= @DateB
BEGIN
SELECT @ReturnDate = @DateA
END
IF @DateB >= @DateA
BEGIN
SELECT @ReturnDate = @DateB
END
IF @DateB >= @ReturnDate
BEGIN
SELECT @ReturnDate = @DateB
END
END
set @ReturnDate = dateadd(mm,datediff(mm,0,@ReturnDate),0) -- will set @ReturnDate to first of month
RETURN @ReturnDate
END
😎
May 7, 2008 at 9:19 am
Lynn
Thanks for the update, also change the last block from @DateB to @DateC. I either had fat fingers or brain freeze. It's only 11 so brain freeze should not be in play yet.
Thanks
Marvin Dillard
Senior Consultant
Claraview Inc
May 7, 2008 at 9:35 am
Marvin,
No problem. I didn't even notice the error in the code itself. I just thought I'd help fix it to give the first of the month.
😎
May 7, 2008 at 1:57 pm
I'd do the whole thing as a calculated column, instead of updating it.
alter table Table1
add column AuthDate as (
case
when ADate > BDate and ADate > CDate then dateadd(day, -1 * datepart(day, ADate), ADate) + 1
when BDate > ADate and BDate > CDate then dateadd(day, -1 * datepart(day, BDate), BDate) + 1
when CDate > BDate and CDate > BDate then dateadd(day, -1 * datepart(day, CDate), CDate) + 1
end)
That way, you don't have to worry about updating it. It's never out of synch, even if one of the other dates is modified. And it takes no disk space (since it's calculated), unless you want to index it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 2:51 pm
Actually, I'm still waiting for more information since the four date fields are in four seperate tables. BDate is in TableB, CDate is in TableC, and DDate is in TableD, and AuthDate (the target column) is in TableA.
Could we please have the additional info I requested earlier? It would help.
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply