May 28, 2008 at 10:09 am
I have a table with 5 date fields
CREATE TABLE [dbo].[ADate](
[MINDATE] [datetime] NULL,
[FPeriod] [datetime] NULL,
[Start_Date] [datetime] NULL,
[End_Date] [datetime] NULL,
[ADate] [datetime] NULL
)
GO
Insert Into ADate Select MINDATE='2006-06-15 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2006-05-30 00:00:00.000', FPeriod='2020-07-01 00:00:00.000', Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2006-05-30 00:00:00.000', FPeriod=null, Start_Date='2006-04-01 00:00:00.000', End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2006-05-15 00:00:00.000', FPeriod='2020-07-03 00:00:00.000', Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2004-08-15 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2004-09-15 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2005-03-15 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2004-08-15 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2004-10-15 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2005-10-15 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2007-09-06 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2007-09-15 00:00:00.000', FPeriod='2020-08-03 00:00:00.000', Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2007-09-06 00:00:00.000', FPeriod=null, Start_Date='2007-07-16 00:00:00.000', End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2007-10-15 00:00:00.000', FPeriod=null, Start_Date='2007-07-16 00:00:00.000', End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2007-02-27 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2006-10-15 00:00:00.000', FPeriod='2020-08-07 00:00:00.000', Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2006-10-14 00:00:00.000', FPeriod='2020-07-06 00:00:00.000', Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2007-08-31 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2004-08-15 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into ADate Select MINDATE='2004-05-31 00:00:00.000', FPeriod=null, Start_Date=null, End_Date=null, ADate=null
Insert Into #ADate Select MINDATE='2007-02-28 00:00:00.000', FPeriod='2020-08-11 00:00:00.000', Start_Date='2007-02-21 00:00:00.000', End_Date='2009-02-20 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2007-11-20 00:00:00.000', FPeriod='2020-08-11 00:00:00.000', Start_Date='2007-06-20 00:00:00.000', End_Date='2008-10-22 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2007-07-30 00:00:00.000', FPeriod=null, Start_Date='2007-06-20 00:00:00.000', End_Date='2008-10-22 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2007-02-28 00:00:00.000', FPeriod='2020-08-11 00:00:00.000', Start_Date='2007-02-12 00:00:00.000', End_Date='2008-02-08 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2008-03-31 00:00:00.000', FPeriod=null, Start_Date='2007-06-22 00:00:00.000', End_Date=null, ADate=null
Insert Into #ADate Select MINDATE='2008-02-15 00:00:00.000', FPeriod='2020-08-11 00:00:00.000', Start_Date='2008-02-07 00:00:00.000', End_Date='2009-01-19 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2008-04-15 00:00:00.000', FPeriod='2020-08-11 00:00:00.000', Start_Date='2008-03-31 00:00:00.000', End_Date='2009-03-10 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2004-01-31 00:00:00.000', FPeriod='2020-07-01 00:00:00.000', Start_Date=null, End_Date=null, ADate=null
Insert Into #ADate Select MINDATE='2007-10-15 00:00:00.000', FPeriod=null, Start_Date='2007-10-01 00:00:00.000', End_Date='2008-09-30 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2007-10-15 00:00:00.000', FPeriod=null, Start_Date='2007-10-01 00:00:00.000', End_Date='2008-09-30 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2007-10-15 00:00:00.000', FPeriod=null, Start_Date='2007-10-01 00:00:00.000', End_Date='2008-09-30 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2008-01-15 00:00:00.000', FPeriod=null, Start_Date='2008-01-01 00:00:00.000', End_Date='2008-12-31 00:00:00.000', ADate=null
Insert Into #ADate Select MINDATE='2008-05-15 00:00:00.000', FPeriod=null, Start_Date='2008-01-01 00:00:00.000', End_Date='2008-12-31 00:00:00.000', ADate=null
and here is what I want to do:
1) I would like to update Adate field with either MinDate field or FPeriod when
either one of them is between the range of Start_Date and End_Date fields. if both Start_Date and End_Date are null then dont update.
2) if both MinDate field or FPeriod are between the range of Start_Date and End_Date fields then update ADate with the earliest of MinDate or Fperiod. if both Start_Date End_Date are null then dont update.
thanks
May 28, 2008 at 10:36 am
Is this what you require?
UPDATE dbo.ADate
SET ADate = ( CASE
WHEN MinDate >= Start_Date AND MinDate <= End_Date
AND FPeriod >= Start_Date AND FPeriod <= End_Date AND MinDate < FPeriod THEN MinDate
WHEN MinDate >= Start_Date AND MinDate = FPeriod THEN FPeriod
WHEN MinDate >= Start_Date AND MinDate <= End_Date THEN MinDate
WHEN FPeriod >= Start_Date AND FPeriod <= End_Date THEN FPeriod
ELSE ADate
END )
FROM dbo.ADate
WHERE Start_Date IS NOT NULL AND End_Date IS NOT NULL
--Ramesh
May 28, 2008 at 12:29 pm
awesome.. thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply