Retrive a Value based on previous date

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 2000 Forums

SQL Server Development (2000)

Retrive a Value based on previous date

Author

Topic

solent
Starting Member

33 Posts

Posted - 2005-12-07 : 04:05:07

Ok i dont even know if this is possible with only 1 select query, but here is my question.

I have a query that returns some values. One of them is the previous date based on the @CurrentDate variable. For example if current date is 24/11/2005 (UK time) then i can get the previous date of that record with the DATEADD(day, - 1, @CurrentDate) command. But in the mean time i need to get the OA_VALUE based on my previous date. Below is my sproc and some sample data.

[CODE]
DECLARE @CurrentDate nvarchar(40),
@LIMITS_CURR_DT [datetime]

SET @CurrentDate = '24/11/2005'
SET @CurrentDate = (CONVERT(DATETIME, @CurrentDate, 103))
SELECT @LIMITS_CURR_DT = MAX(CURR_DT) FROM TABLE_2

SELECT TABLE_1.RUN_ID, TABLE_1.CUT_NUM, TABLE_1.CURR_DT, TABLE_1.OA_VALUE, DATEADD(day, - 1, @CurrentDate) AS PreviousDate,
TABLE_1.DEFLT_ADJ, TABLE_1.RF_GROUP, TABLE_3.CUT_ID, TABLE_3.CUT_DESC, TABLE_2.LIMIT_AMOUNT,
TABLE_2.LIMIT_AMOUNT - TABLE_1.OA_VALUE AS LimitAlert, TABLE_1.OA_VALUE / TABLE_2.LIMIT_AMOUNT AS Utilazation
FROM TABLE_4 AS TABLE_4 INNER JOIN
TABLE_1 AS TABLE_1 ON TABLE_4.CUT_NUM = TABLE_1.CUT_NUM INNER JOIN
TABLE_3 AS TABLE_3 ON TABLE_4.CUT_ID = TABLE_3.CUT_ID AND TABLE_4.CUT_NUM = TABLE_3.CUT_NUM AND
TABLE_1.CUT_NUM = TABLE_3.CUT_NUM INNER JOIN
TABLE_2 ON TABLE_1.CUT_NUM = TABLE_2.CUT_NUM AND TABLE_3.CUT_ID = TABLE_2.CUT_ID AND
TABLE_1.RUN_ID = TABLE_2.RUN_ID AND TABLE_1.RF_GROUP = TABLE_2.RF_GROUP AND
TABLE_3.CUT_NUM = TABLE_2.CUT_NUM
GROUP BY TABLE_1.CUT_NUM, TABLE_1.CURR_DT, TABLE_1.OA_VALUE, TABLE_1.DEFLT_ADJ, TABLE_1.RF_GROUP, TABLE_3.CUT_ID,
TABLE_3.CUT_DESC, TABLE_1.RUN_ID, TABLE_2.LIMIT_AMOUNT, TABLE_2.LIMIT_AMOUNT - TABLE_1.OA_VALUE,
TABLE_1.OA_VALUE / TABLE_2.LIMIT_AMOUNT, TABLE_2.CURR_DT
HAVING (TABLE_2.CURR_DT = @LIMITS_CURR_DT) AND (TABLE_1.RUN_ID = 'CY') AND (TABLE_3.CUT_ID = '09') AND (TABLE_1.CUT_NUM = 1) AND
(TABLE_1.OA_VALUE <> 0) AND (TABLE_1.CURR_DT = @CurrentDate) AND (TABLE_1.RF_GROUP = 'ALL') OR
(TABLE_2.CURR_DT = @LIMITS_CURR_DT) AND (TABLE_1.RUN_ID = 'CY') AND (TABLE_3.CUT_ID = '09') AND (TABLE_1.CUT_NUM = 19) AND
(TABLE_1.OA_VALUE <> 0) AND (TABLE_1.CURR_DT = @CurrentDate) AND (TABLE_1.RF_GROUP = 'ALL')
[/CODE]

[CODE]
RUN_ID CUT_NUM CURR_DT OA_VALUE PreviousDate RF_GROUP CUT_ID LIMIT_AMOUNT
------ --------- ----------------------- -------- ------------------------ -------- ------ ------------
TEST 1 2005-11-24 00:00:00.000 100 2005-11-23 00:00:00.000 ALL 09 101
TEST 19 2005-11-24 00:00:00.000 99 2005-11-23 00:00:00.000 ALL 09 100

(2 row(s) affected)
[/CODE]

Basically i need to have another Expression there next to PreviousDate that will display the OA_VALUE of the Previous Date.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 04:18:56

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources