another way to get most recent records using row number
http://eskonr.com/2014/06/sccm-configmgr-2012-sql-query-get-most-recent-10-records-with-free-disk-space-history/
Regards,
Eswar Koneti
www.eskonr.com
sent from mobile device, please excuse any typo's as a result.
From: "Marcum, John" <jmar...@babc.com>
Sent: Feb 16, 2016 4:53 AM
To: ms...@lists.myitforum.com
Subject: [mssms] RE: SQL Help
This does work. Of course the part that was throwing me off all along was how
to do MAX and Convert. I was able to get one or the other to work but not both.
:)
I don't understand how but the last example from Steve is in the correct format
without having to be converted and it is showing on the most recent date. So
that works great but I don't understand how. LOL
________________________________
John Marcum
MCITP, MCTS, MCSA
Desktop Architect
Bradley Arant Boult Cummings LLP
________________________________
[H_Logo]
From: listsadmin@lists.myitforum.com [mailto:listsadmin@lists.myitforum.com] On
Behalf Of Krueger, Jeff
Sent: Monday, February 15, 2016 2:38 PM
To: ms...@lists.myitforum.com
Subject: [mssms] RE: SQL Help
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: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com>
[mailto:listsadmin@lists.myitforum.com] On Behalf Of
christopher.catl...@us.sogeti.com<mailto:christopher.catl...@us.sogeti.com>
Sent: Monday, February 15, 2016 3:14 PM
To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com>
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: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com>
[listsadmin@lists.myitforum.com] on behalf of Marcum, John [jmar...@babc.com]
Sent: Monday, February 15, 2016 3:05 PM
To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com>
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: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com>
[mailto:listsadmin@lists.myitforum.com] On Behalf Of
christopher.catl...@us.sogeti.com<mailto:christopher.catl...@us.sogeti.com>
Sent: Monday, February 15, 2016 1:56 PM
To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com>
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: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com>
[listsadmin@lists.myitforum.com] on behalf of Marcum, John [jmar...@babc.com]
Sent: Monday, February 15, 2016 2:35 PM
To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com>
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<http://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.
