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)  Software Licenses

Author  Topic  SteevR Starting Member 18 Posts Posted - 2009-08-25 : 14:49:02 Hi, This may be difficult to explain... I am building an asset tracker and logging software licenses. Some volume licenses can be transferred down to lower versions if you install an older version and only the new licenses are available to purchase at the time. So I am trying to calculate actual available licenses and need a little help. Here are the simplified tables: tblSoftware(softwareID,softwareName) 1,MS Office 2000 2,MS Office XP 3,MS Office 2003 tblSoftwareType(softwareTypeID,softwareType) 1,Open License Program (OLP) 2,OEM 3,Boxed Purchase tblSoftwareInstallType(softwareID,softwareTypeID,CDKey,licensesOwned,coverageSoftwareID,coverageSoftwareTypeID) 1,1,XX,100,NULL,NULL 2,1,XX,50,1,1 3,1,XX,25,2,1 tblSoftwareInstall(assetID,softwareID,softwareTypeID) ... tblAssetManagement(assetID,...) ... Lets say that we have the following scenerio: software,installed,remaining 2000,100,0 XP,50,0 2003,20,5 ...and for some reason, 2000 is installed... so XP decreases by 1 (which I have working) softwareName,licensesOwned,active,coverageLicensesOwned,coverageActive,remaining (calculated: licensesOwned+coverageLicensesOwned-coverageActive-active) 2000,OLP,100,101,0,0,-1 XP,OLP,50,50,100,101,-1 2003,OLP,25,20,50,50,5<--this is wrong ...XP is now over by 1 and a 2003 needs to be subracted. This trickle down I can't figure out. SELECT tblAssetSoftware.softwareName,tblAssetSoftwareType.softwareTypeName, tblAssetSoftwareInstallType.licensesOwned, COALESCE ((SELECT COUNT(*) AS active FROM tblAssetSoftwareInstall LEFT OUTER JOIN tblAssetManagement ON tblAssetSoftwareInstall.assetID = tblAssetManagement.assetID GROUP BY tblAssetSoftwareInstall.softwareID, tblAssetSoftwareInstall.softwareTypeID HAVING (tblAssetSoftwareInstall.softwareTypeID = tblAssetSoftwareInstallType.softwareTypeID) AND (tblAssetSoftwareInstall.softwareID = tblAssetSoftwareInstallType.softwareID)), 0) AS active, COALESCE ((SELECT licensesOwned AS coverageLicensesOwned FROM tblAssetSoftwareInstallType WHERE (tblAssetSoftwareInstallType.coverageSoftwareTypeID = softwareTypeID) AND (tblAssetSoftwareInstallType.coverageSoftwareID = softwareID)), 0) AS coverageLicensesOwned, COALESCE ((SELECT COUNT(*) AS coverageActive FROM tblAssetSoftwareInstall LEFT OUTER JOIN tblAssetManagement ON tblAssetSoftwareInstall.assetID = tblAssetManagement.assetID GROUP BY tblAssetSoftwareInstall.softwareID, tblAssetSoftwareInstall.softwareTypeID HAVING (tblAssetSoftwareInstall.softwareTypeID = tblAssetSoftwareInstallType.coverageSoftwareTypeID) AND (tblAssetSoftwareInstall.softwareID = tblAssetSoftwareInstallType.coverageSoftwareID)), 0) AS coverageActive, FROM tblAssetSoftwareInstallType INNER JOIN tblAssetSoftware ON tblAssetSoftwareInstallType.softwareID = tblAssetSoftware.softwareID INNER JOIN tblAssetSoftwareType ON tblAssetSoftwareInstallType.softwareTypeID = tblAssetSoftwareType.softwareTypeID ORDER BY tblAssetSoftwareType.softwareTypeName, tblAssetSoftware.softwareName Sorry for the long post... if anyone has an idea, or an even better way, please let me know. Thanks.