Asset Software pool - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Asset Software pool

Author

Topic

SteevR
Starting Member

18 Posts

Posted - 2009-05-12 : 14:37:19

Hi,
I have a table that tracks software installed on our computers. When a computer is trashed, the non-OEM software is automatically pooled (set the assetID to null) so it can be transferred to another computer. Setting the assetID to null leaves the other info intact, like license keys, so we don't have to run around looking for CDs.

When I add a new computer, I have a dropdown for pooled software displaying the software name and the softwareInstallID. As you can imagine, the list is quite large and repetative. What I would like is to display how many are left of a particular software (ddlSoftwarePool.text) and the first instance of the softwareInstallID (ddlSoftwarePool.value). When the user selects one from the pool and chooses save, it sets the assetID to that softwareInstallID.

Below is a stab at the SQL, but I am getting an error.

SELECT tblAssetSoftware.softwareName + ' (' + CAST(COUNT(*) AS varchar(5)) + ')' AS theText,
(SELECT TOP (1) softwareInstallID
FROM tblAssetSoftwareInstall
WHERE (assetID IS NULL) AND (softwareID = tblAssetSoftwareInstall_1.softwareInstallID)) AS firstSoftwareInstallID
FROM tblAssetSoftwareInstall AS tblAssetSoftwareInstall_1 INNER JOIN
tblAssetSoftware ON tblAssetSoftwareInstall_1.softwareID = tblAssetSoftware.softwareID
GROUP BY tblAssetSoftwareInstall_1.assetID, tblAssetSoftware.softwareName
HAVING (tblAssetSoftwareInstall_1.assetID IS NULL)

TIA

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-12 : 15:00:05

You may want to post your table structure, some sample data and expected results. Also the error you're getting with your SQL query may be helpful.

Some days you're the dog, and some days you're the fire hydrant.

SteevR
Starting Member

18 Posts

Posted - 2009-05-12 : 15:13:26

Sorry. Here you go:

tblSoftwareInstall
softwareInstallID softwareID assetID CDKEY
99 1 NULL xxxxx
133 3 NULL xxxxx
155 1 15 xxxxx
166 1 NULL xxxxx
177 1 NULL xxxxx

tblSoftware
softwareID softwareName
1 Adobe Acrobat 8 Professional
2 Adobe Photoshop CS3
3 Microsoft Office 2007

What I would like to show up in the dropdown is:
Text Value
Adobe Acrobat 8 Professional (3) 99
Microsoft Office 2007 (1) 133

And the error I get from running the above SQL is:
tblAssetSoftwareInstall_1.softwareI is invalid in the select list because it is not contained in either an aggregate function or GROUP BY clause.

Does this make more sense?
Steve

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-12 : 16:13:36

Yeah, you'd have to also group by tblAssetSoftwareInstall_1.softwareInstallID since you using it in a correlated sub-query.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-12 : 16:21:35

To ammend my previosu post, adding that column to the GROUP BY doesn't give you the results you wanted. Here is a change that should work:

DECLARE @tblSoftwareInstall TABLE (softwareInstallID INT, softwareID INT, assetID INT, CDKEY VARCHAR(20))
INSERT @tblSoftwareInstall
SELECT 99, 1, NULL, 'xxxxx'
UNION ALL SELECT 133, 3, NULL, 'xxxxx'
UNION ALL SELECT 155, 1, 15, 'xxxxx'
UNION ALL SELECT 166, 1, NULL, 'xxxxx'
UNION ALL SELECT 177, 1, NULL, 'xxxxx'

DECLARE @tblSoftware TABLE (softwareID INT, softwareName VARCHAR(255))
INSERT @tblSoftware
SELECT 1, 'Adobe Acrobat 8 Professional'
UNION ALL SELECT 2, 'Adobe Photoshop CS3'
UNION ALL SELECT 3, 'Microsoft Office 2007'


SELECT
	tblAssetSoftware.softwareName + ' (' + CAST(COUNT( *) AS VARCHAR(5)) + ')' AS theText,
	(
		SELECT 
			MIN(softwareInstallID)
		FROM
			@tblSoftwareInstall
		WHERE
			assetID IS NULL
			AND softwareID = tblAssetSoftwareInstall_1.softwareID
	) AS firstSoftwareInstallID
FROM
	@tblSoftwareInstall AS tblAssetSoftwareInstall_1
INNER JOIN 
	@tblSoftware AS tblAssetSoftware
	ON tblAssetSoftwareInstall_1.softwareID = tblAssetSoftware.softwareID
GROUP BY
	tblAssetSoftwareInstall_1.assetID,
	tblAssetSoftware.softwareName,
	tblAssetSoftwareInstall_1.softwareID
HAVING
	tblAssetSoftwareInstall_1.assetID IS NULL

dewacorp.alliances

452 Posts

Posted - 2009-05-12 : 22:37:02

You can try this:

DECLARE @tblSoftwareInstall TABLE (softwareInstallID INT, softwareID INT, assetID INT, CDKEY VARCHAR(20))
INSERT @tblSoftwareInstall
SELECT 99, 1, NULL, 'xxxxx'
UNION ALL SELECT 133, 3, NULL, 'xxxxx'
UNION ALL SELECT 155, 1, 15, 'xxxxx'
UNION ALL SELECT 166, 1, NULL, 'xxxxx'
UNION ALL SELECT 177, 1, NULL, 'xxxxx'

DECLARE @tblSoftware TABLE (softwareID INT, softwareName VARCHAR(255))
INSERT @tblSoftware
SELECT 1, 'Adobe Acrobat 8 Professional'
UNION ALL SELECT 2, 'Adobe Photoshop CS3'
UNION ALL SELECT 3, 'Microsoft Office 2007'

SELECT SoftwareName, Count(a.SoftwareID), Min(softwareInstallID) FROM @tblSoftware a INNER JOIN @tblSoftwareInstall b ON a.softwareID = b.softwareID
GROUP BY SoftwareName

There is a business rule though that the first of softwareInstallID used as based.

SteevR
Starting Member

18 Posts

Posted - 2009-05-13 : 08:25:49

Thanks guys,

Both your solutions worked. Thanks for helping me out.

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources