Compare 2 table dates to retrieve value

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)

Compare 2 table dates to retrieve value

Author

Topic

MikeSQL
Starting Member

7 Posts

Posted - 2011-05-09 : 06:33:17

Hi, no doubt there is an easy answer but being new to SQL it's beyond my current skills

I have two tables. Table1 carries multiple columns one of which is a captured date, Table2 carries a list of charge codes each with an effective date for each value. There are multiple updates to Table2, each entry has a new effective date. Each charge code is valid for everyday from the effective date until a new entry is created in Table2.

When a new record is created in Table1 it should refer to Table2 using the charge code and look for a value based on the effective date. My problem is that when I ask for a particular charge code, all I get is 4 results for everytime that charge code has been used in Table2. This is ok but I then need it to compare the effective date on Table2 to the captured date on Table1 for that exact value.

Any help would appreciated. Thanks Mike.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 06:37:14

would you like to share, table structures, sample data and expected results. It would be quite helpful for us to understand your problem.

--------------------------
http://connectsql.blogspot.com/

MikeSQL
Starting Member

7 Posts

Posted - 2011-05-09 : 07:32:21

Hi, Table1 looks as follows:

Agreement	Captured_Date  Charge_Type  Charge_Code  Charge-Value
AEPY010918	20110325       519.00000    1            ?????

Table2 looks as follows:


Charge_Code Effective_Date Charge_Value
1	    19900710	   17.50000
2	    19610101	   17.50000
2	    20081101	   15.00000
1	    20081201	   15.00000
1	    20100101	   17.50000
2	    20100101	   17.50000
1	    20110104	   20.00000
2	    20110104	   17.50000

I need Table1 to look at Table2 using Charge_Code and Captured-Date to return the Charge_Value. The exected result should be 20.0000 or in the case of older records say 31st December it should be 17.5000
Hope this helps.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 07:57:35

DECLARE @Table1 TABLE (Agreement VARCHAR(50),Captured_Date DATETIME,Charge_Type FLOAT,Charge_Code INT)
INSERT INTO @Table1
SELECT 'AEPY010918','20110325',519.00000,1

DECLARE @Table2 TABLE (Charge_Code INT,Effective_Date DATETIME,Charge_Value FLOAT)

INSERT INTO @Table2
SELECT 1,'19900710',17.50000 UNION ALL
SELECT 2 ,'19610101' ,17.50000 UNION ALL
SELECT 2 ,'20081101' ,15.00000 UNION ALL
SELECT 1 ,'20081201' ,15.00000 UNION ALL
SELECT 1 ,'20100101' ,17.50000 UNION ALL
SELECT 2 ,'20100101' ,17.50000 UNION ALL
SELECT 1 ,'20110104' ,20.00000 UNION ALL
SELECT 2 ,'20110104' ,17.50000

SELECT TOP (1) Charge_Value
FROM @Table2 t2
INNER JOIN @Table1 t1 ON t1.Charge_Code = t2.Charge_Code
WHERE DATEDIFF(D,t2.Effective_Date, t1.Captured_Date) >0
ORDER BY DATEDIFF(D,t2.Effective_Date, t1.Captured_Date)

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources