dcsimg
Results 1 to 18 of 18

Thread: SQL Server - UPDATE query needs to find most recent record

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    SQL Server - UPDATE query needs to find most recent record

    I need to write an update query and was hoping for some help.
    I have a Trips table which hold records representing work an employee needs to do on any given day (they go out on a service call, or Trip).
    When these records are created by our scheduling department certain activities are captured in an audit table.
    So, you will have a Trips record with an audit record that it's been scheduled, TSC. You always have this.
    Trips can also be rescheduled, TSR. And this can happen a number of times and I capture the changes. I need to find the most recent TSR for a trip if TSR's exist and capture the employee id of the person who rescheduled.
    So far it's something like this:
    Code:
    update trips
    set ScheduledByTeam = ee.SchedulingTeam, ScheduledByEmp = ee.EmpID
    from trips
    join tbltripstatuschanges chg on chg.Control = Trips.Control
    join Emps ee on ee.EmployeeName = chg.ChangedBy
    WHERE ChangedTo = 'TSR'
    AND ee.SchedulingTeam is not null
    AND Trips.ScheduledByTeam is null
    AND ScheduledTime >= getdate()
    My first condition in the WHERE clause can't just be TSR though, it has be the most recent TSR if there are many.
    Code:
    Control	ChangedTo	empid	ChangedOn	COntrolID
    606144	TSR	24	2018-07-03 10:20:40.243	7023230
    606144	TSR	24	2018-07-03 10:20:15.610	7023220
    606144	TSR	24	2018-07-03 10:20:11.910	7023215
    (Sorry about the crappy formatting)

    So I want to pick up the 1st record in that list (sorry also about the bad example. They won't always be the same id or so close in time).
    I am thinking it will be max(ControlID) in the group but can't quite come up with how to "say" that in SQL.
    I've googled this but am kind of lost with some of the queries I am finding. (You guys know sql queries aren't my strong suit).
    Thanks in advance!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    Wait! I am writing a CTE. Let me give it a try and I will post again.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    I wrote a CTE. Here it is.

    Code:
    With CTE
    As
    (
    select Row_Number() Over(Partition by Control Order By ControlID desc) as Row_Num,
    Control, ChangedTo, ChangedOn 
    From tbltripstatuschanges 
    where changedto = 'TSR' 
    )
    select * from cte join trips on trips.Control = cte.Control
    where row_num = 1
     AND ScheduledTime >= getdate()
     and tripstatus != 'CP'
     and trip_type != 'Follow Up'
     order by trips.control desc
    Here's what is doing. Creates the inline temp table which is the TSR records including the Row_Number column so I can pick the top one since I am ordering by ControlID descending.
    From that table select row_num 1 joined to the Trips table so I only get Trips that aren't yet completed (CP) and that aren't Follow Ups (This cuts the records I'll be updating by 90% which I feel better about but either way I could do them but don't have to as they are a "special" kind of trip for people to stay on top of), and that are scheduled from today forward.

    I get back 60 records so I am going into a random sampling and if I look at the whole audit trail for a trip I see I am picking the correct employee id to use for the update. So far.
    Any comments on anything I may have missed?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    OK, so ultimately this needs to be an UPDATE query and right now it's a SELECT query so I can see what rows it says I have to UPDATE and what it would update them to.
    And now that I've verified it's the right rows, I don't think I can easily make this an UPDATE query because I don't want to update tblTripStatusChanges. I want to update Trips. So maybe I wrote it backwards?
    What I think I might do now that I have the 60 rows I need to update, is create an excel file with the rows and "generate" update commands using Excel formulas.
    Which I know will be a disappointment to you DB people, but it's a one-shot and it will get the job done and I did write a pretty good CTE!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,744

    Re: SQL Server - UPDATE query needs to find most recent record

    You had it right in your first post about MAX()

    Code:
    Update Trips Set SomeField=ScheduledByTeam = ee.SchedulingTeam, ScheduledByEmp = ee.EmpID
        From Trips TT
        Left Join tbltripstatuschanges chg on chg.Control = TT.Control
            and chg.ControlId=(Select Max(chgx.ControlId) From tbltripstatuschanges chgx Where chgx.Control = TT.Control)
        Left join Emps ee on ee.EmployeeName = chg.ChangedBy
    [edit] Change UPDATE to SELECT to test the logic [/edit]


    Code:
    --Update Trips Set SomeField=ScheduledByTeam = ee.SchedulingTeam, ScheduledByEmp = ee.EmpID
    Select *
        From Trips TT
        Left Join tbltripstatuschanges chg on chg.Control = TT.Control
            and chg.ControlId=(Select Max(chgx.ControlId) From tbltripstatuschanges chgx Where chgx.Control = TT.Control)
        Left join Emps ee on ee.EmployeeName = chg.ChangedBy

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    OK, thanks. I am going to run that and look at my results.
    However, I discovered through analyzing the results from the CTE query that I will only be updating 6 rows and I've decided to do that manually. So the CTE helped me to identify which ones. I will see if I get those same rows from your query in which case I won't have any regrets/misgivings over the weekend!
    Then, I have to write another update query based on tasks that were scheduled once and weren't rescheduled but that should be trivial.
    Have a good weekend and I hope you are taking the three days off from all your work!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    A few tweaks to your query (added criteria) and we matched perfectly so thank you again!

    Code:
    Select *
        From Trips TT
        Join tbltripstatuschanges chg on chg.Control = TT.Control
            and chg.ControlId=(Select Max(chgx.ControlId) From tbltripstatuschanges chgx Where chgx.Control = TT.Control  and changedto = 'TSR')
        join Emps ee on ee.EmployeeName = chg.ChangedBy
     WHERE ScheduledTime >= getdate()
     and tripstatus != 'CP'
     and trip_type != 'Follow Up'	
     AND ScheduledByTeam is null
    P.S. Yes, *always* select before you update!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,744

    Re: SQL Server - UPDATE query needs to find most recent record

    !=?

    Why not <>?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    OMG, is there a difference?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  10. #10

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,744

    Re: SQL Server - UPDATE query needs to find most recent record

    Yes - I just did that same research. I would never have thought to use such a C++ like operator in T-SQL. Apparently it has wide support across other SQL varieties. It's just not SQL92 compliant - as if that mattered!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    Now that you mention in though, I am pretty sure that in one's C# code you have to use <>, such as here:
    Code:
    FilterRepairUnitsGrid(string.Format("CustNo = '{0}' AND StatusName <> 'Complete'", txtCustNo.Text.Trim()));
    
            private void FilterRepairUnitsGrid(string sCriteria)
            {
    
    
                string sSort = "EnteredOn DESC";
    
                // Create a new datatable based on the filter and the sort.  Create a view from the original table, apply the filter and the sort, and make a new table out of the view.
                DataView view = new DataView(dtUnitsList, sCriteria, sSort, DataViewRowState.CurrentRows);
                DataTable dtFiltered = view.ToTable();
    So it's an interesting point.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,744

    Re: SQL Server - UPDATE query needs to find most recent record

    That's linq though - right?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,744

    Re: SQL Server - UPDATE query needs to find most recent record

    My C# code uses !=

    Code:
            private void dfxCalendarSlider_ValueChanged(object sender, RoutedPropertyChangedEventArgs<double> e)
            {
                if (dfxCalendarSlider.Tag != null && dfxCalendarSlider.Tag.ToString() == "started")
                {
                    DateTime sd = lastCalendarStart.AddDays(dfxCalendarSlider.Value - 1);
                    string[] prefixes = { "http://" + gstrServer + ":" + gstrPort + "/dcxReader/" };
                    string returnMessage = "";
                    SearchList[0].DesiredAction = "Search";
                    SearchList[0].SearchAction = "date";
                    SearchList[0].SearchText = txtSearch.Text;
                    SearchList[0].SelectedDate = sd;
                    startST(SearchList[0], prefixes, returnMessage);
                }
            }
    As does my JavaScript

    Code:
            function focusoutSmartTIN(wesAWC, wesParent) {
                var strOrigTIN = wesAWC.val();
                var strNewTIN = strOrigTIN;
                var strTINType = wesAWC.siblings(".acs-edit-smarttintype").val();
                strNewTIN = makeTIN(strNewTIN, strTINType);
                if (strOrigTIN != "") {
                    if (strOrigTIN != strNewTIN) {
                        wesAWC.val(strNewTIN);
                    }
                }
            }
    Last edited by szlamany; Aug 31st, 2018 at 12:24 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    No, it's not linq. It should be, probably, but it's not.
    I thought I had gotten an error in my code when I tried using !=. Maybe the only reason I used <> is because that's the example I hit on google.
    I do not always remember why I do some of the things I do...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,744

    Re: SQL Server - UPDATE query needs to find most recent record

    Quote Originally Posted by MMock View Post
    I do not always remember why I do some of the things I do...
    I can be that way - especially after a few glasses of Chianti or a few Limoncello's!

    Can't wait for this three day weekend and then half the office is off all of next week and even the week after!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    Quote Originally Posted by szlamany View Post
    My C# code uses !=
    Well of course it does.
    I thought you meant C# code when you build a query to use in sql objects (datatables, dataviews) like here:

    dataView.RowFilter = "Name <> 'John'" // string is not equal to 'John'
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  18. #18

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,046

    Re: SQL Server - UPDATE query needs to find most recent record

    Quote Originally Posted by szlamany View Post
    I can be that way - especially after ... a few Limoncello's!
    LOL, had my first "Spiked Seltzer" at the Yard Goats game last Tuesday (compliments of my company and our salesman...YG's are our customers!)
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width