February 11, 2015 at 3:16 pm
Sean Lange (2/11/2015)
Lynn Pettis (2/11/2015)
Sean Lange (2/11/2015)
Lynn Pettis (2/11/2015)
Sean Lange (2/11/2015)
Stubby Bunny (2/11/2015)
Hi,I have tried all of that, but nothing returns with the Top 1 clause, if I take that out I get records but it multiplies them cause of the many registration records that come over. Only 1 record exists in Drat_Received but in drat registration many records with the same strSN can exist but I only need the newest which is datetime stamped with the dttRegistration field. If I do this only the very top record comes across, no other records.
Think about your subquery. It does not fire once per row, it fires once. Which means you are going to get 1 and only 1 value there because of the top 1. Since you have no order by it isn't always going to return the same row either.
There are no tables here for me to work so I am shooting in the dark but I think you need something more like this.
with SortedValues as
(
Select strSn
, strComputerName
, ROW_NUMBER() over (Partition by strSN, strComputerName order by dttRegistration desc) as RowNum
from Drat_Registration
group by strSN, strComputerName
)
Select dr.intRecId
, c.strCategory
, mm.strManufacturer
, dm.strMake
, m.strModel
, dr.strSn
, Convert(varchar(10), dr.dtWarrantyStart, 111) as WS
, Convert(varchar(10), dr.dtWarrantyEnd , 111) as WE
, strPoNum
, strTransaction
, dr.intDurableId
, mn.strRank + ' ' + mn.strFullName as NAME
, dr.intModelId
, m.intMakeId
, dr.bitAssetScheduler
, r.strComputerName
From Drat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN SortedValues sv on sv.strSN = dr.strSN and sv.RowNum = 1
Yes, that is another solution to the problem.
Seems that your excellent solution was posted while I was in the middle of writing mine. I hadn't seen yours or your aha! moment when I posted. 😀
Well, I was only rewriting a small part of the code, not the entire query. 😉
LOL. I just reformatted it a little bit. I don't know why but having trailing commas just drives me nutty. There goes my OCD again...err I mean CDO (now they are in the proper order).
And leading commas bother me, but not enough to always change them.
February 11, 2015 at 3:23 pm
Lynn Pettis (2/11/2015)
And leading commas bother me, but not enough to always change them.
I prefer them at the beginning so you can just remove a column from the query with 3 keystrokes [ctrl + k + c] OR [home--]. I don't do it manually either, I have a formatting plugin that does most of that pretty easily.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply