How to insert records based on the data given

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)

How to insert records based on the data given

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-10-20 : 01:14:49

Hello All,

I have a sample of the before table (show below) where I want to insert “Open” value records based on the record already given… This will have to be derived. I know how to write it mathematically but don’t have a clue on how to create it in a query if it is even possible… Below is what is needed mathematically. Please advice. Thanks.

- Everything beings from the start field.

Open -540 + 630 = 90
Break 630 + 15 = 645
Open -645 + 720 = 75
Lunch 720 + 60 = 780
Open -780 + 930 = 150
Break 930 + 15 = 945
Open -945 + 960 = 15
DCOM 960 + 15 = 975
Open -975 + 990 = 15
ADM 990 + 13 = 1003
Open -1003 + 1005 = 2
Blank 1005 + 1 = 1006
Open -1006 + 1006 = 0
ADM 1006 + 5 = 1055
Open -1011 + 1012 = 1
CMT 1012 + 38 = 1050

To determine if an Open record is needed, Start field + Amount filed (540 + 510 = 1050) has to be equal to the last value, if not an Open record is need.

Query that generated the before table:

SELECT TABLE1.CREATED_DATE, TABLE1.ID, TABLE2.LENGTH, TABLE3.EVENT, TABLE1.START, TABLE2.INTERVAL, TABLE1.AMOUNT
FROM
TABLE1 INNER JOIN TABLE2 ON TABLE1.SCHED_ID = TABLE2.SCHED_ID INNER JOIN TABLE3 ON TABLE2.EXC_CODE = TABLE3.EXC_ID
GROUP BY TABLE1.CREATED_DATE, TABLE1.ID, TABLE2.LENGTH, TABLE3.EVENT, TABLE1.START, TABLE2.START, TABLE1.AMOUNT
ORDER BY TABLE2.START

BEFORE
ID CREATED_DATE LENGTH EVENT START INTERVAL AMOUNT
7623 10/18/2007 15 Break 540 630 510
7623 10/18/2007 60 Lunch 540 720 510
7623 10/18/2007 15 Break 540 930 510
7623 10/18/2007 15 DCOM 540 960 510
7623 10/18/2007 13 ADM 540 990 510
7623 10/18/2007 1 Blank 540 1005 510
7623 10/18/2007 5 ADM 540 1006 510
7623 10/18/2007 1 Blank 540 1011 510
7623 10/18/2007 38 CMT 540 1012 510

AFTER
ID CREATED_DATE LENGTH EVENT START INTERVAL AMOUNT
7623 10/18/2007 90 Open 540 510
7623 10/18/2007 15 Break 540 630 510
7623 10/18/2007 75 Open 540 15 510
7623 10/18/2007 60 Lunch 540 720 510
7623 10/18/2007 150 Open 540 60 510
7623 10/18/2007 15 Break 540 930 510
7623 10/18/2007 15 Open 540 15 510
7623 10/18/2007 15 DCOM 540 960 510
7623 10/18/2007 15 Open 540 15 510
7623 10/18/2007 13 ADM 540 990 510
7623 10/18/2007 2 Open 540 13 510
7623 10/18/2007 1 Blank 540 1005 510
7623 10/18/2007 0 Open 540 1 510
7623 10/18/2007 5 ADM 540 1006 510
7623 10/18/2007 Open 540 5 510
7623 10/18/2007 1 Blank 540 1012 510
7623 10/18/2007 Open 540 38 510
7623 10/18/2007 38 CMT 540 1012 510

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-20 : 08:18:04

what is table1 and table2 ? can you post the table DDL and sample data from the tables ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources