How to use the OUTPUT clause to solve a complicate

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)

How to use the OUTPUT clause to solve a complicate

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-07 : 16:06:59

Hello All,

I need help on determining the best approach on accomplishing this task. I’m thinking I will need to use the “Output clause” the problem is I have never used it before never less for a complicated scenario has my… I have three types of scenarios which are listed below:

Three Types of Scenarios:
1)I will compare the Process_Date, Item_ID, and Level fields to determine if it is a new entry to the table if so, you will update the following:

Col_C = Col_A
Col_D = Col_B

If Process_Date, Item_ID, and Level fields are not a new entry I will do the following:

Col_C = 0
Col_D = 0

2)I will compare the Process_Date, Item_ID, and Level fields from the previous date and the current date, if Level field is different I will do the following:

Previous.Col_A = Current.Col_A
Previous.Col_B = Current.Col_B

3) If there is a record that exists in the Previous Table but not in the Current Table do the following:
Insert the old record from the previous table into the Current Table and change the signs in the following fields:

- Col_A
- Col_B
- Col_C
- Col_D

Below are sample tables:

Previous Date

scenarios Process_Date Item_ID Level Col_A Col_B Col_C Col_D
1 8/31/2009 14521 1 550 10 550 10
2 8/31/2009 14521 1 200 200 0 0
3 8/31/2009 14522 1 100 250 100 250

Current Date

scenarios Process_Date Item_ID Level Col_A Col_B Col_C Col_D
1 9/30/2009 14521 1 550 10 0 0
2 9/30/2009 14521 1.5 800 200 600 0
3 9/30/2009 14522 1 -100 -250 -100 -250

Result Table

Process_Date Item_ID Level Col_A Col_B Col_C Col_D
8/31/2009 14521 1 550 10 550 10
8/31/2009 14521 1 200 200 0 0
8/31/2009 14522 1 100 250 100 250
9/30/2009 14521 1 550 10 550 10
9/30/2009 14521 1.5 8000 0 8000 0
9/30/2009 14522 1 -100 -250 -100 -250

Listed to the left of the sample tables are the scenarios, not part of the column in the table.

Please advice. Thanks.

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-08 : 05:01:42

Any suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources