473,698 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Tough Date Manipulation Issue.

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.

Jul 23 '05 #1
7 1746
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,e xchangeto,excha ngecode,datemfr om)

Thanks TFD.
LineVoltageHalo gen 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.


Jul 23 '05 #2
On 13 Jan 2005 16:49:57 -0800, LineVoltageHalo gen 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.exchan gefrom
AND a.exchangeto = exchange.exchan geto
AND a.exchangecode = exchange.exchan gecode
AND a.datefrom > exchange.datefr om)
WHERE dateto IS NULL

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3
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

Jul 23 '05 #4
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

Jul 23 '05 #5
On 14 Jan 2005 06:50:05 -0800, LineVoltageHalo gen 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.exchan gefrom
AND a.exchangeto = exchange.exchan geto
AND a.exchangecode = exchange.exchan gecode
AND a.datemfrom > exchange.datemf rom)
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)
Jul 23 '05 #6
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

Jul 23 '05 #7
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.e xch_dt))
WHERE exch_end_dt IS NULL

Again much thanks.

Cheers, TFD

Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2129
by: Google Mike | last post by:
I guess I'm confused by the whole timezone thing and I don't want to write a web app that gets this stuff wrong. I don't know how timezones work exactly -- is it just hours +/- on GMT or are there going to be minor differences on minutes and seconds too? What I want to do is have a web app that one logs into (great place for submitting the browser's view on time via Javascript) and then the user sees the records from the database with...
16
2203
by: jason | last post by:
I'm tearing out my hair here: ACCESS 2000: When I attempt to overwrite a date in my date field with a new user selected valid date eg: 05/23/99 my date field changes to the TIME I updated the field and does not display my desired date: 05/23/99 Instead it reads: 12:12:03 AM I am going nuts - even considering converting the field to text. The field
4
1801
by: Rich Hurley | last post by:
We've just installed SQL Server 2000 on one of our servers and have noticed a strange behavior. When clicking the column headings in the job display in Enterprise Manager, the list is sorted (first click ascending, then descending) on the column clicked except for "Next Run Date." When we click on "Next Run Date," the list is reordered, but randomly. Each time we click it we get a different order but never ascending or descending. This is...
7
7196
by: What-a-Tool | last post by:
How does the expire date work setting it server side with asp. I know with javascript setting it client side it will be set to the clients local time, and therefore expire when the clients local time reaches the set expire-time. But if it is an expire time set on my server in California, and the cookie is put on a computer that is running on London Time, and the expire time is set at the server as 20 minutes from now, the London computer...
5
1631
by: John Kiernan | last post by:
Hey JavaScript gurus! Here's a weird one: If I check (with an alert) this line: vStrDate2 = vDate.toLocaleString(); I get: Saturday, August 14, 2004 7:57:30 PM (as I should) but this operation...
26
4235
by: sgershon | last post by:
Hi. I know this is should be a simple question. I know server-side web-programming, and never needed to use client-side scripting... until now :) I have done so far a little number of scripts that work well. But there are two that I am having special difficulties with: 1)
5
3376
by: WindAndWaves | last post by:
Hi Gurus This is a cry for help to all of you. I am looking for some sort of plug-in/active X/code/link to outlook/what have you, which I can add to my MS Access database for my users to manipulate dates. For example, as part of my project, I have a roster. I want my users to change the start and end times of the rostered people using a drop and drag scenario rather than entering data.
1
1507
by: awebguynow | last post by:
I'm working in a Lamp environment, (actually WAMP), am using one of the many Calendar widgets (that has configurable formats), and have a heavy data in/data out (forms/reports) webapp. This is probably more of a design issue, than PHP issue, but we all deal with this. My 1st priority has been for simplicity, so I have used std. MySQL format for the text input that feeds into the table.Column. There are problems with that and I'd like...
3
1685
by: GorseFox | last post by:
Forgive me. I am new to PHP and am a little confused by date manipulation and would appreciate some guidance. I have a string containing a date which has been parsed from and RSS feed. This looks something like: "Tue, 25 Mar 2008 04:28:18 -0500" and is stored in $rssdate I would like to manipulate this date to correct the timezone adjustment. (I happen to know that the item I am referencing was actually timestamped at 09:28:18). I...
0
8668
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8598
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8855
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7708
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6515
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5857
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4358
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1995
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.