October 1, 2009 at 1:51 am
Hi all,
I have 3 fields, 1) Date, 2) [Asset Tag], 3) Status
I need to create a table from the source 2 million row table, from a source that looks like this:
Source Table Example Rows. (Comma represents a field not required)
ID, Date, ,,,[EDS Asset Tag],,,,Status,,,
101, 12/7/2009, 35607234,,,,,Deployed,,,
...
102, 12/7/2009, 35607248,,,,,Excess,,,
...
223, 14/7/2009, 35607234,,,,,Deployed,,,
...
259, 18/7/2009, 35607234,,,,,Retired,,,
....
263, 18/7/2009, 35607248,,,,,Deployed,,,
...
324, 1/8/2009, 35607234,,,,,Excess,,,
...
389, 12/8/2009, 35607234,,,,,Deployed,,,
...
401, 18/8/2009, 35607248,,,,,Retired,,,
Desired Table (and or View) Rows
ID, Date, ,,,[EDS Asset Tag],,,,Status,,,
1, 12/7/2009, 35607234,,,,,Deployed,,,
2, 12/7/2009, 35607248,,,,,Excess,,,
3, 18/7/2009, 35607234,,,,,Retired,,,
4, 18/7/2009, 35607248,,,,,Deployed,,,
5, 1/8/2009, 35607234,,,,,Excess,,,
6, 12/8/2009, 35607234,,,,,Deployed,,,
7, 18/8/2009, 35607248,,,,,Retired,,,
Why Do i need this table ??
An asset(server, router, switch desktop, laptop, etc), uniquely identified by its [EDS Asset Tag] comes into and out of usage and thus its "Status" (Deployed, Excess, Retired) changes over time.
One Asset (EDS Asset Tag) can be Deployed today, Excess tomorrow and next week appear Retired - then in a month Deployed again.
I need a separate table that i can update, which contains only the relevant date+[EDS Asset Tag]+Status in a uniquefield (maybe a constraint ?) as well as each of the fields.
Can anyone help ?
October 5, 2009 at 5:38 pm
Hi All - for those with a similar problem...
i found an awesome solution that i worked through to learn how to use at this link...
http://www.sqlmag.com/Articles/Print.cfm?ArticleID=93462
This is what i came up with that works !!
SELECT
[Asset Centre Bar Code],
MAX(CASE WHEN recID = 1 THEN date ELSE NULL END) AS from_date,
MAX(CASE WHEN seqID = 1 THEN date ELSE NULL END) AS to_dt,
status,
COUNT(*) AS cnt
FROM
( SELECT
[Asset Centre Bar Code], status, grp, date,
ROW_NUMBER()OVER (PARTITION BY [Asset Centre Bar Code], status, grp ORDER BY date) AS recID,
ROW_NUMBER()OVER (PARTITION BY [Asset Centre Bar Code], status, grp ORDER BY date desc) AS seqID
FROM
(SELECT
[Asset Centre Bar Code], date, status,
ROW_NUMBER() OVER(PARTITION BY [Asset Centre Bar Code] ORDER BY date)
- ROW_NUMBER() OVER(PARTITION BY [Asset Centre Bar Code], status ORDER BY date
) AS grp
FROM
DWC_AP_AM772
) AS d
) AS d
GROUP BY [Asset Centre Bar Code], status, grp
ORDER BY [Asset Centre Bar Code], 2, 3
October 5, 2009 at 9:40 pm
Heh... you're sure? That's an SQL Server 2005 solution and this is and SQL Server 2000 forum. Even for 2k5, though, it seems a bit complex compared to what you originally asked for. The following will do the trick in 2k and 2k5 for what you asked for...
SELECT ID = IDENTITY(INT,1,1),
Date, ,,,[EDS Asset Tag],,,,Status,,,
INTO dbo.NewTable
FROM dbo.OldTable
ORDER BY Date,Status,Tag
... then, add indexes, etc ... PK would be on the ID column and IDENTITY makes that NOT NULL so no need to delare it as NOT NULL.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 6, 2009 at 3:54 am
Hey Jeff,
Thanks for your message --- oops, i am using 2005 and didn't realise it was a 2000 forum...my mistake.
Well, the code works amazing... on 2.5 million tables it returns in 53 seconds with exactly what i needed.
Can i ask you or the forum another modified question - if this is reallocated to the 2005 section ?
My next question is on how to turn the ORDER BY clause error message (generated when i wrap the query into a view) up so it then works as a normal query...
Thanks either way, i appreciate your kindness and interest.
Tai
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply