Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
evvo1961
Starting Member
4 Posts |
Posted - 2013-11-07 : 08:34:27
|
i have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)heres my codeSELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment WHERE [TicketID] = t.TicketID ORDER BY DateAssigned ASC) = t.TicketID WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u INNER JOIN Profiles p ON u.UserID = p.UserID INNER JOIN Department d ON d.DepartmentID = p.DepartmentID WHERE d.ParentDepartmentID = 3 AND u.Active = 1) OR CreatedForUserID NOT IN (SELECT u.UserID FROM Users u INNER JOIN Profiles p ON u.UserID = p.UserID INNER JOIN Department d ON d.DepartmentID = p.DepartmentID WHERE d.ParentDepartmentID = 3 AND u.Active = 1)) AND (t.DateCreated BETWEEN '05/11/2013' AND '06/11/2013') AND RHDUserID IN (SELECT u.UserID FROM Users u INNER JOIN Profiles p ON u.UserID = p.UserID INNER JOIN Department d ON d.DepartmentID = p.DepartmentID WHERE d.ParentDepartmentID = 3 AND u.Active = 1) |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 09:02:47
|
[code]SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY TicketID ORDER BY DateAssigned) AS Rn,* FROM Assignment) a ON a.[TicketID] = t.TicketIDAND a.Rn=1....[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-11-08 : 01:57:32
|
hi, can you try this?SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t JOIN Assignment a ON a.DateAssigned = (SELECT MAX(DateAssigned) FROM Assignment WHERE [TicketID] = t.TicketID)WHERE EXISTS (SELECT 1 FROM Users u INNER JOIN Profiles p ON u.UserID = p.UserID INNER JOIN Department d ON d.DepartmentID = p.DepartmentID WHERE d.ParentDepartmentID = 3 AND u.Active = 1 AND (CreatedByUserID <> u.UserID OR CreatedForUserID <> u.UserID) AND (RHDUserID = u.UserID)) AND (t.DateCreated BETWEEN '05/11/2013' AND '06/11/2013') |
|
|
emam razib
5 Posts |
Posted - 2013-11-10 : 21:36:49
|
Hello! I am also agree with visakh16.I think it would be..........unspammed |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-11-11 : 14:23:52
|
SELECT ...FROM Ticket tCROSS APPLY (SELECT TOP 1 ... FROM Assignment a ORDER BY a.DateAssigned) b |
|
|
|
|
|
|
|