Query help please

  • I have a history table that tracks changes to client records, im having trouble writing a query that will capture BOTH of these records based on (Where changedcolumns = 'Paid'). I need the OLD and NEW history in the result.

    Capture

  • SELECT ca.*
    FROM dbo.history h2
    CROSS APPLY (
        SELECT h1.*
        FROM dbo.history h1
        WHERE h1.hist_id = h2.hist_id - 1
        UNION ALL
        SELECT h2.*
    ) AS ca
    WHERE h2.changedcolumns = 'Paid' /*?LIKE '%Paid%'?*/

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Alternatively:

    ;WITH CTE AS
    (
    SELECT h1.hist_id,
    'NEW' history,
    h1.historydate,
    h1.changedcolumns,
    h1.id#,
    h1.status,
    h1.transdate,
    h1.billstatus,
    h1.comment,
    h1.paid,
    h1.billable
    FROM dbo.history h1
    OUTER APPLY(SELECT TOP(1)
    hist_id AS hist_id_old,
    'OLD' AS history_old,
    historydate AS historydate_old,
    changedcolumns AS changedcolumns_old,
    id# AS id#_old,
    status AS status_old,
    transdate AS transdate_old,
    billstatus AS billstatus_old,
    comment AS comment_old,
    paid AS paid_old,
    billable AS billable_old
    FROM dbo.history h2
    WHERE h2.hist_id < h1.hist_id
    AND h2.id = h1.id
    ORDER BY h2.hist_id DESC) h2
    WHERE h1.changedcolumns = 'Paid' /*?LIKE '%Paid%'?*/)
    SELECT x.*
    FROM CTE
    CROSS APPLY (VALUES (
    hist_id,
    history,
    historydate,
    changedcolumns,
    id#,
    status,
    transdate,
    billstatus,
    comment,
    paid,
    billable
    ),
    (
    hist_id_old,
    history_old,
    historydate_old,
    changedcolumns_old,
    id#_old,
    status_old,
    transdate_old,
    billstatus_old,
    comment_old,
    paid_old,
    billable_old
    )
    ) x(hist_id, history, historydate, changedcolumns, id#, status, transdate, billstatus, comment, paid, billable)
    ;
  • The problem with your question, is that you've not given enough data to rule out potential incorrect queries.  For example, SELECT * FROM dbo.history returns the correct results for your limited data, but it surely doesn't return the correct results for your full table.

    I also suspect that WHERE h1.hist_id = h2.hist_id - 1 will not work for your full table, even though it works here, because it's likely that you'll be updating other records in between, so the hist_id relationship won't be that simple.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Couldn't it be as straightforward as this??

     

    SELECT t.[pick your columns]
    FROM SomeTable t
    INNER JOIN (SELECT [ID#]
    FROM SomeTable
    WHERE ChangedColumns = 'Paid') p ON t.[ID#] = p.[ID#]


    -- OR:

    SELECT t.[pick your columns]
    FROM SomeTable t
    WHERE [ID#] IN (SELECT [ID#]
    FROM SomeTable
    WHERE ChangedColumns = 'Paid') p ON t.[ID#] = p.[ID#]
  • SoCal_DBD wrote:

    Couldn't it be as straightforward as this??

    SELECT t.[pick your columns]
    FROM SomeTable t
    INNER JOIN (SELECT [ID#]
    FROM SomeTable
    WHERE ChangedColumns = 'Paid') p ON t.[ID#] = p.[ID#]


    -- OR:

    SELECT t.[pick your columns]
    FROM SomeTable t
    WHERE [ID#] IN (SELECT [ID#]
    FROM SomeTable
    WHERE ChangedColumns = 'Paid') p ON t.[ID#] = p.[ID#]

    I would use a WHERE EXISTS instead of either of those, assuming that this approach would give the correct results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • SoCal_DBD wrote:

    Couldn't it be as straightforward as this??

    SELECT t.[pick your columns]
    FROM SomeTable t
    INNER JOIN (SELECT [ID#]
    FROM SomeTable
    WHERE ChangedColumns = 'Paid') p ON t.[ID#] = p.[ID#]


    -- OR:

    SELECT t.[pick your columns]
    FROM SomeTable t
    WHERE [ID#] IN (SELECT [ID#]
    FROM SomeTable
    WHERE ChangedColumns = 'Paid') p ON t.[ID#] = p.[ID#]

    That could be what the OP requires, it depends on what data is in the table. The query I provided gets the most recent "old" row.

    If there is only one old row for each new row then I think your method will work.

  • Perhaps I am not understanding your need. What I am reading is that you make changes to your client records (without naming the table that contains the clients)  and that as these changes are made you wish to log 2 entries in a separate history table based on changes to the as-yet-unnamed client table.

    Based on my reading, I would create an UPDATE trigger on the client table that automatically adds both records to the history table.

    INSERT and DELETE triggers can also be useful for logging changes to tables.

    If you are unclear on how triggers work, I am willing to review your needs with you.

    • This reply was modified 3 days, 14 hours ago by  tmyers 75436.
    • This reply was modified 3 days, 14 hours ago by  tmyers 75436.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply