July 22, 2015 at 1:41 pm
Hi Experts,
I need your help in writing this query. See the attached picture that shows the tables and relationship between them.
I want to write a query to show the EmployeeName, AID, ComputerType, Manufacturer, SystemFamily, SUM_Of_RAM_in_each_Computer, SUM_of_DiskCapacity_in_Each_Computer.
I tried and wrote this query:
///////////////////////////////////////////
[font="Courier New"]SELECT
Employee.EmployeeName,
ComputerInfo.AID,
ComputerInfo.ComputerType,
ComputerInfo.Manufacturer,
ComputerInfo.SystemFamily,
Round(Sum(RAM.Capacity)/1024/1024/1024) AS [RAM (GB)],
Round(Sum(Disk.DiskSize)/1000/1000/1000) AS [Total Disk (GB)]
FROM Employee
INNER JOIN ((ComputerInfo INNER JOIN Disk ON ComputerInfo.AID = Disk.AssetID)
INNER JOIN RAM ON ComputerInfo.AID = RAM.AssetID) ON Employee.EID = ComputerInfo.EmployeeID
GROUP BY
Employee.EmployeeName,
ComputerInfo.AID,
ComputerInfo.ComputerType,
ComputerInfo.Manufacturer,
ComputerInfo.SystemFamily,
RAM.Capacity,
Disk.DiskSize
[/font]
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
however the output was terribly wrong - here is a sample of the output based on the data at the bottom of this thread:
[font="Courier New"]EmployeeName AIDComputerTypeManufacturerSystemFamily RAM (GB)Total Disk (GB)
GEORGE 5 Laptop LENOVO ThinkPad W510 16 1280
Michael 6 Laptop LENOVO ThinkPad W530 8 240
Michael 7 WorkstationDell Inc. Precision T7600 32 958
Michael 7 WorkstationDell Inc. Precision T7600 32 3999
Michael 7 WorkstationDell Inc. Precision T7600 32 3999
[/font]
However if the query was correct I would see something like this (based on the data below):
[font="Courier New"]EmployeeName AIDComputerTypeManufacturerSystemFamily RAM (GB)Total Disk (GB)
GEORGE 5 Laptop LENOVO ThinkPad W510 16 320
Michael 6 Laptop LENOVO ThinkPad W530 8 240
Michael 7 Workstation Dell Inc. Precision T7600 32 2239
[/font]
Here is a sample of the data in each table:
Employee:
[font="Courier New"]EIDEmployeeNameJobTitleLocation
11GEORGE IRT Italy
12Michael SEE Italy
[/font]
ComputerInfo:
[font="Courier New"]AIDComputerTypeEmployeeIDManufacturerSystemFamily SystemModel MSAssetID DateBoughtComments
5Laptop 11 LENOVO ThinkPad W510 4391BZ6 2984070 10/1/2010
6Laptop 12 LENOVO ThinkPad W530 2447L76 4972562 10/1/2014
7Workstation12 Dell Inc. Precision T7600 Precision T7600 5438636 1/1/2013
[/font]
Disk:
[font="Courier New"]DIDDeviceID Interface DiskSize DiskModel AssetID
19\\.\PHYSICALDRIVE0 IDE 320069836800HITACHI HTS725032A9A364 5
24\\.\PHYSICALDRIVE0 IDE 240054796800INTEL SSDSC2BW240A3L 6
25\\.\PHYSICALDRIVE2 SCSI 999653308416DELL PERC H310 SCSI Disk Device7
26\\.\PHYSICALDRIVE1 SCSI 999651179520DELL PERC H310 SCSI Disk Device7
27\\.\PHYSICALDRIVE0 SCSI 239437900800DELL PERC H310 SCSI Disk Device7
[/font]
RAM:
[font="Courier New"]RIDDeviceLocation Manufacturer Capacity AssetID
25DIMM 1 80CE 42949672965
26DIMM 2 80CE 42949672965
27DIMM 3 80CE 42949672965
28DIMM 4 80CE 42949672965
29ChannelB-DIMM1 Samsung 85899345926
30DIMM1_CPU1 Samsung-00CE 85899345927
31DIMM3_CPU1 Samsung-00CE 85899345927
32DIMM2_CPU1 Samsung-00CE 85899345927
33DIMM4_CPU1 Samsung-00CE 85899345927
[/font]
Can you please help me achieve my requirement?
Thanks in advance.
July 23, 2015 at 9:23 am
Maybe start here:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
The data you posted explains your table structures, but it's not usable.
July 23, 2015 at 9:30 am
For instance, you have to remove the RAM.Capacity and Disk.DiskSize from your GROUP BY. You might also need to aggregate the values before joining the tables. This can be done with a subquery or a CTE.
On a different topic:
Are you aware of the issues you face with integer division?
Why do you use 1024 for RAM and 1000 for disk size?
July 31, 2015 at 3:51 pm
Agreed with the above comments but here is what Luis is talking about in a query. I am not sure if this works for your data but it might get you a little further along:
SELECT
e.EmployeeName,
ci.AID,
ci.ComputerType,
ci.Manufacturer,
ci.SystemFamily,
diskSize.DiskSizeTotal,
ramSize.RamSizeTotal
FROM dbo.employee e
INNER JOIN dbo.computerInfo ci
ON ci.EmployeeID = e.EID
LEFT JOIN
(
SELECT
AssetID,
SUM(DiskSize) AS DiskSizeTotal
FROM dbo.[disk]
GROUP BY
AssetID
) AS diskSize
ON diskSize.assetID = ci.AID
LEFT JOIN
(
SELECT
AssetID,
SUM(Capacity) AS RamSizeTotal
FROM dbo.[ram]
GROUP BY
AssetID
) AS ramSize
ON ramSize.assetID = ci.AID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply