May 9, 2008 at 7:12 am
I have following table with 5 date fields. What I want to do is update ADate column using following criteria:
Read the dates for each record and
Calculte the Greatest Date value from columns
MINAUTHDATE, ph_FY_PD_CD,ph_zero_FY_PD_CD and proj_Start_Date
if the greatest value is in columns ph_FY_PD_CD then update ADate with date of first day of current month.
if the greatest value is in columns ph_zero_FY_PD_CD then update ADate with date of first day of month of value e.g. if ph_zero_FY_PD_CD = 04-04-2008 then update Adate to =04-01-2008 for that record.
if ts_dt is greatest value then use ts_dt to update ADate.
if proj_Start_Date has the greatest date value then use that date and chop off timestamp.
CREATE TABLE [dbo].[AlDate](
[EMPL_ID] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PROJ_ID] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MINAUTHDATE] [datetime] NULL,
[ph_FY_PD_CD] [datetime] NULL,
[ph_zero_FY_PD_CD] [datetime] NULL,
[proj_Start_Date] [datetime] NULL,
[ADate] [datetime] NULL
)
Here are insert statement:
Insert Into #authorizeddate Select EMPL_ID='A', PROJ_ID=null, MINAUTHDATE='2006-06-15 00:00:00.000', ph_FY_PD_CD=null, ph_zero_FY_PD_CD=null, proj_Start_Date=null, ADate=null
Insert Into #authorizeddate Select EMPL_ID='B', PROJ_ID='INDIR.0001', MINAUTHDATE='2006-05-30 00:00:00.000', ph_FY_PD_CD='2020-07-01 00:00:00.000', ph_zero_FY_PD_CD=null, proj_Start_Date=null, ADate=null
Insert Into #authorizeddate Select EMPL_ID='B', PROJ_ID='07181.0009.0001', MINAUTHDATE='2006-05-30 00:00:00.000', ph_FY_PD_CD=null, ph_zero_FY_PD_CD=null, proj_Start_Date='2006-04-01 00:00:00.000', ADate=null
Insert Into #authorizeddate Select EMPL_ID='C', PROJ_ID='07195.0001.0001', MINAUTHDATE='2006-05-15 00:00:00.000', ph_FY_PD_CD='2020-07-04 00:00:00.000', ph_zero_FY_PD_CD='2020-07-04 00:00:00.000', proj_Start_Date=null, ADate=null
Insert Into #authorizeddate Select EMPL_ID='B, PROJ_ID=null, MINAUTHDATE='2004-08-15 00:00:00.000', ph_FY_PD_CD=null, ph_zero_FY_PD_CD=null, proj_Start_Date=null, ADate=null
Whats the best way to do this?
thanks
May 9, 2008 at 7:50 am
What's ts_dt?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 9, 2008 at 7:59 am
Well, here's one idea of the type of thing you could do...
-- Structure and Data
CREATE TABLE [dbo].[AlDate](
[EMPL_ID] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PROJ_ID] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MINAUTHDATE] [datetime] NULL,
[ph_FY_PD_CD] [datetime] NULL,
[ph_zero_FY_PD_CD] [datetime] NULL,
[proj_Start_Date] [datetime] NULL,
[ADate] [datetime] NULL
)
Insert Into AlDate Select EMPL_ID='A', PROJ_ID=null, MINAUTHDATE='2006-06-15 00:00:00.000', ph_FY_PD_CD=null, ph_zero_FY_PD_CD=null, proj_Start_Date=null, ADate=null
Insert Into AlDate Select EMPL_ID='B', PROJ_ID='INDIR.0001', MINAUTHDATE='2006-05-30 00:00:00.000', ph_FY_PD_CD='2020-07-01 00:00:00.000', ph_zero_FY_PD_CD=null, proj_Start_Date=null, ADate=null
Insert Into AlDate Select EMPL_ID='B', PROJ_ID='07181.0009.0001', MINAUTHDATE='2006-05-30 00:00:00.000', ph_FY_PD_CD=null, ph_zero_FY_PD_CD=null, proj_Start_Date='2006-04-01 00:00:00.000', ADate=null
Insert Into AlDate Select EMPL_ID='C', PROJ_ID='07195.0001.0001', MINAUTHDATE='2006-05-15 00:00:00.000', ph_FY_PD_CD='2020-07-04 00:00:00.000', ph_zero_FY_PD_CD='2020-07-04 00:00:00.000', proj_Start_Date=null, ADate=null
Insert Into AlDate Select EMPL_ID='B', PROJ_ID=null, MINAUTHDATE='2004-08-15 00:00:00.000', ph_FY_PD_CD=null, ph_zero_FY_PD_CD=null, proj_Start_Date=null, ADate=null
-- Calculation
; with
a as ( select EMPL_ID, PROJ_ID, 'MINAUTHDATE' as Name, MINAUTHDATE as Date from dbo.AlDate
union all select EMPL_ID, PROJ_ID, 'ph_FY_PD_CD', ph_FY_PD_CD from dbo.AlDate
union all select EMPL_ID, PROJ_ID, 'ph_zero_FY_PD_CD', ph_zero_FY_PD_CD from dbo.AlDate
union all select EMPL_ID, PROJ_ID, 'proj_Start_Date', proj_Start_Date from dbo.AlDate
union all select EMPL_ID, PROJ_ID, 'ADate', ADate from dbo.AlDate),
b as (select *, row_number() over (partition by EMPL_ID, PROJ_ID order by Date desc) as RowNumber from a),
c as (
select *, case Name
when 'ph_FY_PD_CD' then dateadd(m, datediff(m, 0, getdate()), 0)
when 'ph_zero_FY_PD_CD' then dateadd(m, datediff(m, 0, Date), 0)
when 'ts_dt' then Date
when 'proj_Start_Date' then dateadd(d, datediff(d, 0, Date), 0)
else Date end as NewADate
from b where RowNumber = 1)
update a set ADate = NewADate from dbo.AlDate a inner join c on a.EMPL_ID = c.EMPL_ID and isnull(a.PROJ_ID, 'NULL') = isnull(c.PROJ_ID, 'NULL')
select * from dbo.AlDate
/* Results
EMPL_ID PROJ_ID MINAUTHDATE ph_FY_PD_CD ph_zero_FY_PD_CD proj_Start_Date ADate
------------ ------------------------------ ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
A NULL 2006-06-15 00:00:00.000 NULL NULL NULL 2006-06-15 00:00:00.000
B INDIR.0001 2006-05-30 00:00:00.000 2020-07-01 00:00:00.000 NULL NULL 2008-05-01 00:00:00.000
B 07181.0009.0001 2006-05-30 00:00:00.000 NULL NULL 2006-04-01 00:00:00.000 2006-05-30 00:00:00.000
C 07195.0001.0001 2006-05-15 00:00:00.000 2020-07-04 00:00:00.000 2020-07-04 00:00:00.000 NULL 2020-07-01 00:00:00.000
B NULL 2004-08-15 00:00:00.000 NULL NULL NULL 2004-08-15 00:00:00.000
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 9, 2008 at 8:05 am
ts_date is just a date field. for tangible sold.
May 9, 2008 at 8:39 am
shahab (5/9/2008)
ts_date is just a date field. for tangible sold.
The point was that you've referred to it in your rules, but it's not in your structure (at least not in what you posted).
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply