Find record where 1 field value has changed

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find record where 1 field value has changed

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-02 : 06:18:01

I need to find all items in an inventory table where a field has been unticked in the last 24 hours (there is no audit trail), as well as the contract number of the contract it has been added to (it will not have existed in that table before).

These are the two table querys in their basic form:

select item (nvarchar(20)), inactive (bit) from inventory

select item (nvarchar(20) , contract (nvarchar(20))  , original_start_rent (datetime) from deltickitem

I would like to see just the item number and the contract number it has been added to.

What is the best and most efficient way to accomplish this?

Many thanks
Martyn

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 09:05:49

What does "unticked" mean and how do you know a field has been unticked?

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-02 : 09:51:16

In the ERP system this field shows as a tick box called Inactive on the form, in SQL it is 1 if ticked, 0 if unticked and NULL if not set.

All this does from the point of view of the ERP system is to decide whether a brand new piece of equipment is ready and available to hire out on a contract or not - if ticked it is NOT available, if unticked that piece of equipment is available to be hired.

What I'm trying to achieve is to pick up what brand new pieces of equipment have been released for hire in the last 24 hours and what, if any, contract they have been hired out on.

Hope that makes sense.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 09:55:16

So, that's the "inactive" column?

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-02 : 10:11:44

Yes it is.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 10:22:19

so:


select item, contract
from inventory i
join deltickitem d
on i.item = d.item
where i.inactive = 0 or i.inactive is null -- not sure what you do with null, here
and original_start_rent >= dateadd(day, 0, datediff(day, 0, GETDATE()))
and original_start_rent < dateadd(day, 0, datediff(day, 0, GETDATE()+1))

would that do it? I'm assuming that the original_start_rent column can be used to test "released for hire in the last 24 hours"

Kristen
Test

22859 Posts

Posted - 2015-04-02 : 10:27:45

This maybe?


DECLARE @StartTime datetime,
        @EndTime datetime

SELECT @StartTime = DATEADD(Hour, DATEDIFF(Hour, 0, GetDate())-24, 0),
       @EndTime = GetDate()

SELECT ...
FROM deltickitem AS T
     JOIN inventory AS I
         ON I.item = T.item
WHERE original_start_rent >= @StartTime AND original_start_rent < @EndTime
      AND inactive = 1

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-02 : 10:54:14

That seems to return everything when I try it. If I exclude the nulls, the list is a lot smaller but the result still contains items for which it is not their first hire.

Could a comparison be done somehow, using the item number and original_start_rent dates? If the item was not in a query of all items that have ever been in deltickitem, this would mean it's likely a brand new item. Not sure how to do that though.

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-02 : 10:59:16

Kristen

If I change it to inactive = 0 in the where clause, I get exactly the same result as with gbritton's code. As I said in response to him, I wonder if a comparison query could be done.

Is there any auditing within SQL server that could be used to check with the value of the inactive field is changed?

Thanks
Martyn

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 11:00:22

quote:
Originally posted by wembleybear

Kristen

If I change it to inactive = 0 in the where clause, I get exactly the same result as with gbritton's code. As I said in response to him, I wonder if a comparison query could be done.

Is there any auditing within SQL server that could be used to check with the value of the inactive field is changed?

Thanks
Martyn

there is no automatic auditing. You could enable Change Data Capture for that table and column.

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-02 : 11:20:15

quote:
there is no automatic auditing. You could enable Change Data Capture for that table and column.

OK I'll look it up, that might be an option as I only really need to know when it is changed and the item number.

Thanks
Martyn

Kristen
Test

22859 Posts

Posted - 2015-04-02 : 11:59:20

You could create a TRIGGER on [inventory] table and INSERT to a [inventoryAudit] table when a record is inserted or updated. You could do that conditionally on the [inactive] column changing on UPDATE, and always on INSERT.

Then you could report on the [inventoryAudit] table.

You would also need to create a "Purge" routine for [inventoryAudit], otherwise it will continue to grow forever!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources