I would add the MAX function for your group by timestamp: MAX(CONVERT(VARCHAR(26), tat.DeploymentTimeStamp00, 107)) as 'Deployment Date',
Wendell Hutchison Network Software Specialist Deming Public Schools From: [email protected] [mailto:[email protected]] On Behalf Of Marcum, John Sent: Monday, February 15, 2016 12:36 PM To: [email protected] Subject: [mssms] SQL Help Anyone know how to return ONLY the most recent date if there are multiple rows containing dates? Here's my query, the row MicrosoftBDDInfo_DATA.DeploymentTimeStamp00 will return multiple rows with various dates. I ONLY want to see the most recent date. select distinct sys1.Name0 as 'Computer Name', CONVERT(VARCHAR(26), tat.DeploymentTimeStamp00, 107) as 'Deployment Date', oem.Model0 as 'Installed Task Sequence', hrd.Manufacturer0 as 'Manufacturer', hrd.Product0 as 'Family', hrd.Model0 as 'Model', bios.SoftwareElementID0 as 'BIOS Version', hrd.SerialNumber0 as 'Serial', ROUND (CONVERT (FLOAT, hrd.HDD_TotalSpace_MB0) / 1024, 2) as 'Disk Space in GB', ROUND (CONVERT (FLOAT, hrd.HDD_FreeSpace_MB0) / 1024, 2) as 'Free Space in GB', ROUND (ROUND(CONVERT (FLOAT ,hrd.RAM_TOTALPHYMEMORY_KB0) / 1048576, 2) * 1024, 0) AS 'Total Memory in MB', war.ShipDate0 as 'Ship Date', vru.User_Name0 as'User Name', vru.displayName0 as 'Display Name', l0 as 'Location', vru.department0 as 'Department', vru.Mail0 as'EMail' from v_R_System sys1 LEFT JOIN MicrosoftBDDInfo_DATA tat on tat.MachineID = sys1.ResourceID LEFT Join v_GS_OEMInformation0 oem on oem.ResourceID = sys1.ResourceID LEFT JOIN v_GS_BDNA_BDNA_HARDWARE_DATA_1_30 hrd on hrd.SMSUniqueIdentifier0 = sys1.SMS_Unique_Identifier0 LEFT JOIN v_GS_CUSTOM_WARRANTY_INFO_2_00 war on sys1.ResourceID = war.ResourceID LEFT JOIN V_GS_PC_BIOS as BIOS on BIOS.resourceid = sys1.resourceid LEFT JOIN vUsersPrimaryMachines prim on prim.MachineID = sys1.ResourceID INNER JOIN v_R_USER vru ON vru.ResourceID = prim.UserResourceID where OS_PlatformType0 = 'Desktop' group by sys1.Name0, tat.DeploymentTimeStamp00, oem.Model0, hrd.Manufacturer0, hrd.Product0, hrd.Model0, bios.SoftwareElementID0, hrd.SerialNumber0, hrd.HDD_TotalSpace_MB0, hrd.HDD_FreeSpace_MB0, hrd.RAM_TOTALPHYMEMORY_KB0, war.ShipDate0, vru.User_Name0, vru.displayName0, l0, vru.department0, vru.Mail0 ________________________________ John Marcum MCITP, MCTS, MCSA Desktop Architect Bradley Arant Boult Cummings LLP ________________________________ [H_Logo] ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer.
