Here's an attempt at it, left out the convert part you should be able to add
that to the subquery I'd think. Don't have anything handy to test with though,
check out
http://dba.stackexchange.com/questions/46870/select-which-has-max-date-or-latest-date
sys1.Name0 as 'Computer Name',
(select MAX(tat2.DeploymentTimeStamp00) from MicrosoftBDDInfo_DATA tat2
Where tat.MachineID = tat2.machineID) as [Deployment Date],
oem.Model0 as 'Installed Task Sequence',
hrd.Manufacturer0 as 'Manufacturer',
hrd.Product0 as 'Family',
From: [email protected] [mailto:[email protected]] On
Behalf Of [email protected]
Sent: Monday, February 15, 2016 3:14 PM
To: [email protected]
Subject: [mssms] RE: SQL Help
in that case, you'll need to use one of the other methods to sort the data and
get only the top result.
________________________________
From: [email protected]<mailto:[email protected]>
[[email protected]] on behalf of Marcum, John [[email protected]]
Sent: Monday, February 15, 2016 3:05 PM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: SQL Help
I don't know that the newest date will also be the first row though. Is that
true?
________________________________
John Marcum
MCITP, MCTS, MCSA
Desktop Architect
Bradley Arant Boult Cummings LLP
________________________________
[H_Logo]
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of
[email protected]<mailto:[email protected]>
Sent: Monday, February 15, 2016 1:56 PM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: SQL Help
add this to your group by section
limit 1
Here are some other methods:
https://www.periscopedata.com/blog/4-ways-to-join-only-the-first-row-in-sql.html<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.periscopedata.com_blog_4-2Dways-2Dto-2Djoin-2Donly-2Dthe-2Dfirst-2Drow-2Din-2Dsql.html&d=CwMF-g&c=aLnS6P8Ng0zSNhCF04OWImQ_He2L69sNWG3PbxeyieE&r=pQGVi_ygWZb0EWR_EeMFzgKJCQ8AFTQI7Ck6iiIPItI&m=EG4Ib7cmf3S8zQkLhDjMeuOm_QhNL7da_RM1KlakHiw&s=hxTgC95gfH1OCp1GNx3oTycjte7aobxztZwzNe6ROio&e=>
________________________________
From: [email protected]<mailto:[email protected]>
[[email protected]] on behalf of Marcum, John [[email protected]]
Sent: Monday, February 15, 2016 2:35 PM
To: [email protected]<mailto:[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.
________________________________
CONFIDENTIALITY NOTICE: This email contains information from the sender that
may be CONFIDENTIAL, LEGALLY PRIVILEGED, PROPRIETARY or otherwise protected
from disclosure. This email is intended for use only by the person or entity to
whom it is addressed. If you are not the intended recipient, any use,
disclosure, copying, distribution, printing, or any action taken in reliance on
the contents of this email, is strictly prohibited. If you received this email
in error, please contact the sending party by reply email, delete the email
from your computer system and shred any paper copies.
Note to Patients: There are a number of risks you should consider before using
e-mail to communicate with us. See our Privacy & Security page on
www.henryford.com for more detailed information as well as information
concerning MyChart, our new patient portal. If you do not believe that our
policy gives you the privacy and security protection you need, do not send
e-mail or Internet communications to us.
