Tough Date Manipulation Issue.

[LineVoltageHalogen]

Greetings All, I was hoping that someone might be able to help me with
the following issue:

table ddl:

create table exchange
(
exchangefrom varchar(6),
exchangeto varchar(6),
exchangecode varchar(6),
datemfrom datetime,
dateto datetime,
exchangerate decimal(28,10)
)

The data in this table under normal conditons will look like:

select *
from exchange

USD EURO GL 01/01/2004 01/31/2004 .7523453111
USD GBP GL 01/01/2004 01/31/2004 .5384966123
USD EURO GL 02/01/2004 02/29/2004 .7523492111
USD GBP GL 02/01/2004 02/29/2004 .6004972023
My task is to calculate the days delimeted by the start and end date of
the period which is simple enough:

select exchangefrom, exchangeto, exchangecode, datemfrom, dateto,
datediff(d, datemfrom, dateto)
from exchange
However due to circumstances beyond my control the dateto field may
contain a null instead of a valid end date!! YIKES:
select *
from exchange

USD EURO GL 01/01/2004 01/31/2004 .7523453111
USD GBP GL 01/01/2004 NULL .5384966123
USD EURO GL 02/01/2004 02/29/2004 .7523492111
USD GBP GL 02/01/2004 02/29/2004 .6004972023

My solution to correct the data is to populate the missing end date
with the (start date -1 day) of the next period. However, I am not
sure how to do this with SQL? E.g) from the example directly above the
row: USD GBP GL 01/01/2004 NULL needs to be updated to:
USD GBP GL 01/01/2004 01/31/2004 and this can be done by
looking for the next period (USD GBP GL 02/01/2004
02/29/2004) that follows and subtracting from its start date 1 day (
02/01/2004 - 1 day = 01/31/2004) and that will give me the appropriate
end date.

If anyone has any insight into solving this problem I would be very
thankful.

Regards, TFD.

[LineVoltageHalogen]

Greeting All Again, I wanted to put in a bit more information regarding
the nature of the data. The periods are not guarenteed to be monthly
so you cannot simply look for the next month. The periods could be
daily, weekly, bi-weekly, every 6 six days, etc.

The PK for the table defined below is:
(exchangefrom,exchangeto,exchangecode,datemfrom)

Thanks TFD.
LineVoltageHalogen wrote:

Greetings All, I was hoping that someone might be able to help me with the following issue:

table ddl:

create table exchange
(
exchangefrom varchar(6),
exchangeto varchar(6),
exchangecode varchar(6),
datemfrom datetime,
dateto datetime,
exchangerate decimal(28,10)
)

The data in this table under normal conditons will look like:

select *
from exchange

USD EURO GL 01/01/2004 01/31/2004 .7523453111
USD GBP GL 01/01/2004 01/31/2004 .5384966123
USD EURO GL 02/01/2004 02/29/2004 .7523492111
USD GBP GL 02/01/2004 02/29/2004 .6004972023
My task is to calculate the days delimeted by the start and end date of the period which is simple enough:

select exchangefrom, exchangeto, exchangecode, datemfrom, dateto,
datediff(d, datemfrom, dateto)
from exchange
However due to circumstances beyond my control the dateto field may
contain a null instead of a valid end date!! YIKES:
select *
from exchange

USD EURO GL 01/01/2004 01/31/2004 .7523453111
USD GBP GL 01/01/2004 NULL .5384966123
USD EURO GL 02/01/2004 02/29/2004 .7523492111
USD GBP GL 02/01/2004 02/29/2004 .6004972023

My solution to correct the data is to populate the missing end date
with the (start date -1 day) of the next period. However, I am not
sure how to do this with SQL? E.g) from the example directly above the row: USD GBP GL 01/01/2004 NULL needs to be updated to:
USD GBP GL 01/01/2004 01/31/2004 and this can be done by
looking for the next period (USD GBP GL 02/01/2004
02/29/2004) that follows and subtracting from its start date 1 day (
02/01/2004 - 1 day = 01/31/2004) and that will give me the appropriate end date.

If anyone has any insight into solving this problem I would be very
thankful.

Regards, TFD.

[Hugo Kornelis]

On 13 Jan 2005 16:49:57 -0800, LineVoltageHalogen wrote:

(snip)

My solution to correct the data is to populate the missing end date
with the (start date -1 day) of the next period. However, I am not
sure how to do this with SQL?

Hi TFD,

Try if this works. It's untested, so test it first, then run it in a
transaction, inspect results, and rollback or commit as appropriate.

UPDATE exchange
SET dateto = (SELECT DATEADD (day, -1, MIN(a.datefrom))
FROM exchange AS a
WHERE a.exchangefrom = exchange.exchangefrom
AND a.exchangeto = exchange.exchangeto
AND a.exchangecode = exchange.exchangecode
AND a.datefrom > exchange.datefrom)
WHERE dateto IS NULL

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[LineVoltageHalogen]

Hugo, I appreciate your taking the time to respond. I tested this and
unfortunetly it does not work. It does not update the null ros in the
table, do you have any other ideas?

Regards, TFD

[LineVoltageHalogen]

Hugo, I appreciate your taking the time to respond. I tested this and
unfortunetly it does not work. It does not update the null ros in the
table, do you have any other ideas?
Regards, TFD

[Hugo Kornelis]

On 14 Jan 2005 06:50:05 -0800, LineVoltageHalogen wrote:

Hugo, I appreciate your taking the time to respond. I tested this and
unfortunetly it does not work. It does not update the null ros in the
table, do you have any other ideas?

Hi TFD,

It does work when I test it (using your test data):

create table exchange
(
exchangefrom varchar(6),
exchangeto varchar(6),
exchangecode varchar(6),
datemfrom datetime,
dateto datetime,
exchangerate decimal(28,10)
)
go
insert exchange
select 'USD', 'EURO', 'GL', '01/01/2004', '01/31/2004', 0.7523453111
union all
select 'USD', 'GBP', 'GL', '01/01/2004', null, 0.5384966123
union all
select 'USD', 'EURO', 'GL', '02/01/2004', '02/29/2004', 0.7523492111
union all
select 'USD', 'GBP', 'GL', '02/01/2004', '02/29/2004', 0.6004972023
go
select * from exchange
go
UPDATE exchange
SET dateto = (SELECT DATEADD (day, -1, MIN(a.datemfrom))
FROM exchange AS a
WHERE a.exchangefrom = exchange.exchangefrom
AND a.exchangeto = exchange.exchangeto
AND a.exchangecode = exchange.exchangecode
AND a.datemfrom > exchange.datemfrom)
WHERE dateto IS NULL
go
select * from exchange
go
drop table exchange
go

Can you post a script (like the above) that I can copy and past into Query
Analyzer and run to see a situation where the dateto column is not
updated?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[LineVoltageHalogen]

Hugo, I just typed in your script and yes you are CORRECT it does work.
I need to see what I did wrong last time around. I will get back to
you. Thank you for your work so far.

TFD

[LineVoltageHalogen]

Hugo, you were spot on! Much thanks to your and your prodigious SQL
skills. Here is what I needed to do:

-- This takes care of the case which I explained to you.
UPDATE #exchange
SET exch_end_dt = (SELECT DATEADD (day, -1, MIN(a.exch_dt))
FROM #exchange AS a
WHERE a.exch_base_cd = #exchange.exch_base_cd
AND a.exch_frn_cd = #exchange.exch_frn_cd
AND a.exch_cd = #exchange.exch_cd
AND a.exch_dt > #exchange.exch_dt)
WHERE exch_end_dt IS NULL
-- This takes care of the case were there is no next period defined.
In this scenario
-- the business requirement says that I have to make the period 2
years.
UPDATE #exchange
SET exch_end_dt = (SELECT DATEADD (d, 730, getdate())
FROM #exchange AS a
WHERE a.exch_base_cd = #exchange.exch_base_cd
AND a.exch_frn_cd = #exchange.exch_frn_cd
AND a.exch_cd = #exchange.exch_cd
AND a.exch_dt = max(#exchange.exch_dt))
WHERE exch_end_dt IS NULL

Again much thanks.

Cheers, TFD