noobie needs help with SP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Livin - 19 Aug 2008 16:57 GMT

I have no idea where to start. Usually I can do a web search but this
one is not so obvious with search terms. And, I've never built an SP,
only basic queries and used excel formulas but this it too complicated
for excel.

I have an Excel workbook of software application names and versions.
Many have the same 'root' name but different version number.

e.g. root = "McAfee VirusScan"

I need to find out all the 'matching' names and list each version that
exists, and count # of each version

There are thousands of items so I will not know what all the possible
versions are so I'd like to do this problematically.

Anyone do this before and have some example code or formulas?

Thx a ton!

Reply to this Message

Scho - 19 Aug 2008 17:18 GMT

Are you sure you need a procedure?
Maybe a view would be better; this way it would be called at any one
time and do a list with a count for you as well as be simply an
extension of your current TSQL skills.

BOL link for procedures; http://msdn.microsoft.com/en-us/library/ms187926.aspx
BOL link for Views; http://msdn.microsoft.com/en-us/library/ms187956.aspx

I'd suggest something like...

Create View Vw_ListSoftware
AS
SELECT Name, Version, Count(Name) FROM SoftwareDataTable

Bit basic and you've not really explained the table your holding the
data but should give you an idea but sure there are a few MVPs hanging
around that can do something a lot more clever!
Scho

Reply to this Message

Livin - 19 Aug 2008 21:09 GMT

the table is very simple...
Columns: APP NAME -- VERSION # -- MANUF -- INSTALL COUNT

A view might be best, just not sure how to create it so that we SUM
all the 'simular' items and use fuzzy logic to determine "like" APP
NAMEs

thx for the help!

> Are you sure you need a procedure?
> Maybe a view would be better; this way it would be called at any one
[quoted text clipped - 14 lines]
> around that can do something a lot more clever!
> Scho

Reply to this Message

Scho - 20 Aug 2008 09:42 GMT

Yeah can see what you mean but if you cut out the version number you
may be okay although I can see how this would be a bad thing too.

How about combining the name & version number and then doing a count
on that? If it doesn't work below you could create temp table with
that field and then select it from that; bit long winded but it would
work.... eventually lol

CREATE VIEW Vw_ListSoftware
AS
Select [App Name], [Version#], COUNT ( DISTINCT ( [AppName] + ' ' +
[Version#] ) ) AS Total
FROM TBL_DataDataTable
WHERE [Install Count] >= 1

Good luck!
Scho

Reply to this Message

Livin - 25 Aug 2008 19:53 GMT

> Yeah can see what you mean but if you cut out the version number you
> may be okay although I can see how this would be a bad thing too.
[quoted text clipped - 13 lines]
> Good luck!
> Scho

4 tables... EMEA, US, CANLA, APAC
each with columns... SUITE NAME -- VERSION -- PUBLISHER -- COUNT

Is a cross join good here?

Combining the Name & Version imigh work. I'd like to try both that and
first several characters (or 2 words) & Version so that the "suites"
that might be called something different get captured together. I'd
like to try both to see what turns out best.

any code thoughts?
thx

Reply to this Message