Help please with TSQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help please with TSQL

Author

Topic

paulwfromtheden
Starting Member

9 Posts

Posted - 2010-08-18 : 03:25:31

Hi,
I have a table below and I need to insert two additional rows.
Claims can be settled in any currency and if more than one currency is used I need to make sure that on each 'as_of_dt' there is a 'LR' row for every currency that has been used, and if on a given day one of the currencies is not used, then I need to insert a record 'bringing forward' the currency and currency amount from the previous 'as at dt'.
In the data below an additional row is required for 2002-06-02 because there is no USD LR row for that date, I need to insert a row dated '2006-06-02' with an amount of 4000 (this comes from the LR record for USD dated '2006-05-31').
Similarly a rowneeds to be inserted on 2008-10-12 for 0.00 USD, this is brought forward from the row for USD, type 'LR' from 2008-10-10.

Im sure it is simple, but I would be obliged if someone could point me in the right direction.
Regards
Paul

claim_id curr as_of_dt amount type
12345 USD 2002-07-05 00:00:00 1000.00 LR
12345 USD 2004-06-29 00:00:00 500.00 LP
12345 USD 2004-06-29 00:00:00 3000.00 LR
12345 GBP 2006-05-31 00:00:00 6000.00 LR
12345 USD 2006-05-31 00:00:00 2000.00 LP
12345 USD 2006-05-31 00:00:00 4000.00 LR
12345 GBP 2006-06-02 00:00:00 5000.00 LP
12345 GBP 2006-06-02 00:00:00 6000.00 LR
12345 USD 2008-10-10 00:00:00 4000.00 LP
12345 USD 2008-10-10 00:00:00 0.00 LR
12345 GBP 2008-10-10 00:00:00 6000.00 LP
12345 GBP 2008-10-10 00:00:00 0.00 LR
12345 GBP 2008-10-12 00:00:00 500.00 LP
12345 GBP 2008-10-12 00:00:00 20000.00 LR

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-18 : 04:00:13

So what is your exected result, based on the sample data provided above?

N 56°04'39.26"
E 12°55'05.63"

paulwfromtheden
Starting Member

9 Posts

Posted - 2010-08-18 : 04:10:05

claim_id curr as_of_dt amount type
12345 USD 2002-07-05 00:00:00 1000.00 LR
12345 USD 2004-06-29 00:00:00 500.00 LP
12345 USD 2004-06-29 00:00:00 3000.00 LR
12345 GBP 2006-05-31 00:00:00 6000.00 LR
12345 USD 2006-05-31 00:00:00 2000.00 LP
12345 USD 2006-05-31 00:00:00 4000.00 LR
12345 GBP 2006-06-02 00:00:00 5000.00 LP
12345 GBP 2006-06-02 00:00:00 6000.00 LR
12345 USD 2006-06-02 00:00:00 4000.00 LR
12345 USD 2008-10-10 00:00:00 4000.00 LP
12345 USD 2008-10-10 00:00:00 0.00 LR
12345 GBP 2008-10-10 00:00:00 6000.00 LP
12345 GBP 2008-10-10 00:00:00 0.00 LR
12345 GBP 2008-10-12 00:00:00 500.00 LP
12345 GBP 2008-10-12 00:00:00 20000.00 LR
12345 USD 2008-10-12 00:00:00 0.00 LR

The two new rows are in red and green. The one in red is the amount brought forward from the LR row for USD from 2006-05-31 (because there is no USD row for 2006-06-02 and likewise the one in green is brought forward from the LR row dated 2008-10-10 for the same reason.
Is that clear?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources