Query

I'm trying to find a way to create a query to obtain the last 30 hours ran
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance o
n
both cases to see which one bring the data faster.
Thanks: Cesar SandovalThere are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>sql