January 28, 2004 at 8:27 am
Ok, here's an interesting one.
I have a table that I am inserting one new record for each unique record to summarize historical data for a data conversion.
Heres an example of the query. The first part of the union query returns data that is not converging into a single key item. The second part of the union query returns the data that is converging, causing the duplicated records.
INSERT
TABLENAME1
(
SELECT
Field1 = Field1,
Field2 = '052',
Field3 = CASE
WHEN Field2 = 045 AND Field3 = 096 THEN '096'
WHEN Field2 = 045 AND Field3 = 094 THEN '094'
WHEN Field2 = 045 AND Field3 = 090 THEN '090'
WHEN Field2 = 045 AND Field3 = 093 THEN '093'
ELSE NULL
END,
Field4 = Field4,
Field5 = SUM(Field5),
Field6 = SUM(Field6)
FROM
TABLENAME1
WHERE
Field2 IN (045, 054)
AND Field3 NOT IN (19, 29, 27, 89, 91)
GROUP BY
Field1,
Field2,
Field3,
Field4
)
UNION
(
SELECT
Field1 = Field1,
Field2 = '052',
Field3 = CASE
WHEN Field2 = 054 AND Field3 = 019 THEN '094'
WHEN Field2 = 054 AND Field3 = 029 THEN '094'
WHEN Field2 = 054 AND Field3 = 027 THEN '094'
WHEN Field2 = 045 AND Field3 = 089 THEN '091'
WHEN Field2 = 045 AND Field3 = 091 THEN '091'
END,
Field4 = Field4,
Field5 = SUM(Field5),
Field6 = SUM(Field6),
FROM
TABLENAME1
WHERE
Field2 IN (045, 054)
AND Field3 IN (19, 29, 27, 89, 91)
GROUP BY
Field1,
Field2,
Field3,
Field4
)
ORDER BY
Field1,
Field2,
Field3,
Field4
For each duplicated record, I need to create one record that summarizes Field5 and Field6. Uniqueness is determined by fields 1 - 4.
Any suggetions are appreciated.
January 28, 2004 at 12:38 pm
I stopped by Void's cube. This one can be solved by converging Field3 in a derived table and then performing a rollup from that derived table.
K. Brian Kelley
@kbriankelley
January 28, 2004 at 1:09 pm
Yes. Brian to the rescue.
Its actually quite simple. I guess I was overcomplicating it in my mind (easy for me to do)
Basically the query above can be rewritten as the following...
INSERT
TABLENAME1
SELECT
Field1 = A.Field1,
Field2 = A.Field2,
Field3 = A.Field3,
Field4 = A.Field4,
Field5 = SUM(A.Field5),
Field6 = SUM(A.Field6)
FROM
(
SELECT
Field1 = Field1,
Field2 = '052',
Field3 = CASE
WHEN Field2 = 054 AND Field3 = 019 THEN '094'
WHEN Field2 = 054 AND Field3 = 029 THEN '094'
WHEN Field2 = 054 AND Field3 = 027 THEN '094'
WHEN Field2 = 045 AND Field3 = 089 THEN '091'
WHEN Field2 = 045 AND Field3 = 091 THEN '091'
WHEN Field2 = 045 AND Field3 = 096 THEN '096'
WHEN Field2 = 045 AND Field3 = 094 THEN '094'
WHEN Field2 = 045 AND Field3 = 090 THEN '090'
WHEN Field2 = 045 AND Field3 = 093 THEN '093'
END,
Field4 = Field4,
Field5 = Field5,
Field6 = Field6,
FROM
TABLENAME1
WHERE
Field2 IN (045, 054)
) AS A
GROUP BY
Field1, Field2, Field3, Field4
ORDER BY
Field1, Field2, Field3, Field4
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply