July 24, 2008 02:58pm pdt

1. angelIII 265,644
2. mark_wills 185,371
3. chapmandew 182,323
4. ee_rlee 137,045
5. matthews... 120,021
6. capricorn1 115,606
7. aneeshatti... 73,852
8. jinesh_ka... 67,954
9. jimpen 67,877
10. BillBach 67,320
11. DatabaseMX 65,619
12. sdstuber 65,367
13. dtodd 57,800
14. Thomasian 56,764
15. LSMCons... 50,588

1. angelIII 1,731,394
2. Arthur_... 694,467
3. jrb1 500,640
4. aneeshatti... 481,669
5. peter57r 452,417
6. matthews... 437,844
7. acperkins 347,899
8. Lowfatsp... 316,410
9. capricorn1 287,701
10. jimhorn 287,367
11. SjoerdVer... 245,577
12. jimpen 230,935
13. Kevin3NF 229,651
14. rockiroads 216,397
15. dqmq 191,591

09.14.2007 at 05:37AM PDT, ID: 22828650

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.2

Help needed with complex INSERT SQL on SQL Server 2000

Zones: Databases Miscellaneous, MS SQL Server, SQL Query Syntax

Tags: insert, complex, sql, statement

Fellow Experts,

I need your help with a SQL batch (SQL Server 2000) to run first a delete and then an insert.

The relevant tables/columns:

volume
------------------------------------------------------
volume_id (uniqueidentifier, PK)
activity_code (char(20))
work_center_code (varchar(50))

volume_detail
------------------------------------------------------
volume_id (uniqueidentifier, PK)
sample_date (datetime, PK)
sample_value (real)

volume_detail has transaction volumes for half-hour time increments for a given
work center and activity code. I need to do the following:

1) Delete all records from volume_detail where the related activity code is 'TlrTot'
and the sample date is >= 1 Jan 2007. This SQL should do the trick:

DELETE volume_detail
FROM volume_detail INNER JOIN
volume ON volume.volume_id = volume_detail.volume_id
WHERE volume.activity_code = 'TlrTot'

2) Insert new records into volume_detail such that for every record returned by the query below,
we insert one record into volume_detail with the appropriate volume_id.

SELECT v.work_center_code, 'TlrTot' AS activity_code, vd.sample_date, SUM(vd.sample_value) AS sample_value
FROM volume_detail INNER JOIN
volume ON volume.volume_id = volume_detail.volume_id
WHERE v.activity_code LIKE 'T-V01%'
GROUP BY v.work_center_code, 'TlrTot', vd.sample_date
HAVING vd.sample_date >= '1 Jan 2007'

So, Experts, please have a look at my DELETE statement and let me know if it looks proper, and
please have a go at writing the INSERT statement. Your help is very, very much appreciated.

Regards,

Patrick

Start your free trial to view this solution

Question Stats

Zone: Database

Question Asked By: matthewspatrick

Solution Provided By: ptjcb

Participating Experts: 3

Solution Grade: A

Translate:

Loading Advertisement...

20080236-EE-VQP-29 / EE_QW_1_20070628