Best Practice to Track Changes/New Records

Question

Best Practice to Track Changes/New Records

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
July 20, 2010 at 9:18 am

#235515

Hey All,
I need to come up with a reporting solution in SSRS to report on specific products that have gone thru changes by having added a new version. I'll explain:
For example, Prod# 713 originally had one revision# (1). A week later this product required a change in, say, it's UnitOfMeasure(UOM), so a user would add another revision# (2) to this product and entered the new UOM, so in the back end (SSMgmtConsole) if I query the table for this prod. I see there are two rows (Rev 1 and 2) with their own data. Additionally, this product is made up of many raw materials (RM) which reside in another table, so the new revision (2) may be created due to a new RM used by this product, or there's a change in one of the exisiting RM's, or one of the RM's was removed b/c is no longer used by this product. (hopefully this make sense so far). So, the SSRS report needs to show only those products that changed, but they only want to see on the report those fields that changes (whether new, changed or removed).
So I'm stumped here b/c I've never done somthing like this. On the sql side, what should I do?
?????

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 1

I've added an Excel worksheet w/ one record as a sample. It has 2 revision versions. Notice that a single product may have many RM's. They may change, they may add new one(s), or one or more may be removed.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

You will need to use change detection triggers or your queries will be quite complex.

Please follow the link in my signature to see how the case/question should be presented for the better answers.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

latingntlman

With what limited data you have posted, by the way in the most awarkward format for use .... Please Please next time you post to a forum asking a question ... follow the directions given by the first link in my signaure block. That said I abreviated your data to what I though was most important and posted it in the format most suitable for easy of use as shown here:

CREATE TABLE #T1(FGN INT,Rev INT,RMN VARCHAR(8),RMS VARCHAR(50))

INSERT INTO #T1

SELECT 713,1,'ING0090','18CE53E5-CF41-45D6-858C-C9BFD0C62A6C' UNION ALL

SELECT 713,1,'ING093','4601CF4F-A0D8-4922-BFFE-A7FE3CB43A16' UNION ALL

SELECT 713,1,'ING094','1E97EB27-924D-42E9-8208-094BBD5910B0' UNION ALL

SELECT 713,1,'ING095','32E9D829-5345-48D2-BFA6-34FCD697DE80' UNION ALL

SELECT 713,1,'PKG002','82A415DB-7CB2-4481-9F08-528230902BF6' UNION ALL

SELECT 713,1,'PKG024','9DD57BA1-B8EE-4C40-B4EC-6A4FB5CA56A4' UNION ALL

SELECT 713,1,'PKG046','09311E7B-C659-4757-9A36-C8763A573F88' UNION ALL

SELECT 713,1,'PKG078','C96C981B-9626-4362-8357-AC42648CFE8B' UNION ALL

SELECT 713,1,'PKG079','8FFF5EF5-3A7B-4537-A847-7E3A401CC401' UNION ALL

SELECT 713,1,'Z00713F','3DB2D945-49A1-45BF-BE28-FB7E03A9CECD' UNION ALL

SELECT 713,1,'Z00713H','F15FB632-A775-4E90-9583-DC7C652B441D' UNION ALL

SELECT 713,2,'ING0090','18CE53E5-CF41-45D6-858C-C9BFD0C62A6C' UNION ALL

SELECT 713,2,'ING093','4601CF4F-A0D8-4922-BFFE-A7FE3CB43A16' UNION ALL

SELECT 713,2,'ING094','1E97EB27-924D-42E9-8208-094BBD5910B0' UNION ALL

SELECT 713,2,'ING095','32E9D829-5345-48D2-BFA6-34FCD697DE80' UNION ALL

SELECT 713,2,'PKG002','82A415DB-7CB2-4481-9F08-528230902BF6' UNION ALL

SELECT 713,2,'PKG024','9DD57BA1-B8EE-4C40-B4EC-6A4FB5CA56A4' UNION ALL

SELECT 713,2,'PKG046','09311E7B-C659-4757-9A36-C8763A573F88' UNION ALL

SELECT 713,2,'PKG078','C96C981B-9626-4362-8357-AC42648CFE8B' UNION ALL

SELECT 713,2,'PKG079','8FFF5EF5-3A7B-4537-A847-7E3A401CC401' UNION ALL

SELECT 713,2,'Z00713G','4BEB8B44-3615-43EE-8F78-9657BDEF3540'

Now using this code snippet (by the way I am not the author of the originally posted code, but have modified this tool box snippet of mine to suit your conditions):

;WITH cteDupeName AS

(SELECT FGN,Rev FROM #T1 GROUP BY FGN,Rev HAVING COUNT(*) > 1)

SELECT source.FGN, source.Rev,source.RMN,source.RMS

FROM #T1 source

INNER JOIN cteDupeName dupe --understand this is the key making it all work

ON source.FGN = dupe.FGN AND Source.Rev > dupe.Rev

ORDER BY source.FGN;

Here are my results"

FGNRevRMNRMS

7132ING009018CE53E5-CF41-45D6-858C-C9BFD0C62A6C

7132ING0934601CF4F-A0D8-4922-BFFE-A7FE3CB43A16

7132ING0941E97EB27-924D-42E9-8208-094BBD5910B0

7132ING09532E9D829-5345-48D2-BFA6-34FCD697DE80

7132PKG00282A415DB-7CB2-4481-9F08-528230902BF6

7132PKG0249DD57BA1-B8EE-4C40-B4EC-6A4FB5CA56A4

7132PKG04609311E7B-C659-4757-9A36-C8763A573F88

7132PKG078C96C981B-9626-4362-8357-AC42648CFE8B

7132PKG0798FFF5EF5-3A7B-4537-A847-7E3A401CC401

7132Z00713G4BEB8B44-3615-43EE-8F78-9657BDEF3540

Is that what you require?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 4

I thought the case explanation and attachement were enough to explain, but in any way, it turns out the solution stake owner actually wants to see only the latest two versions and the changes between them two, so I figured the code out and wrote it and put it into a SSRS draft.

Thanks for the replies.

J.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

latingntlman

Great glad to hear you have solved the problem, would you care to post your code to this forum so that others with similar problems can benefit ?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]