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.
-
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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.historyreturns 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 - 1will 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 EXISTSinstead 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.
- tmyers 75436 wrote:
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.
Table triggers do add overhead to a process, whether or not that change will be pertinent. We dont know if the OP only needs this data once a month or weekly for some reporting.
----------------------------------------------------
- tmyers 75436 wrote:
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.
Table triggers do add overhead to a process, whether or not that change will be pertinent. We dont know if the OP only needs this data once a month or weekly for some reporting.
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply