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
 Help with Sql Script

Author  Topic 

Datadave21
Starting Member

3 Posts

Posted - 2013-02-05 : 14:13:21
Hello,

I have inherited a database from a previous employee who has written a script that will tell me how many records have been added or updated for a specific month or a range of months. What I would like to have also in this query is, which user made the additions or updates. The script I have so far looks like this:
SELECT Year(modify_date) as "Year",month(modify_date) as "Month", count(*) as "Profiles added or updated" FROM `hrdb`.`person`
Where Modify_date > '2012-12-01'
group by Year(modify_date),month(modify_date)
order by Year(modify_date) desc,month(modify_date) desc;

The user table has in it a user_id feild.

Can you let me know what else I need to tell you to help me out, completely a newbie with sql, but am very comforatable with access.
Thanks in advance.

Dave

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 14:18:34
Two questions:

Does the person table have a column that indicates which user_id made the updates?

If the additions or updates were made by multiple people in any given month, what do you want to get in your results?
Go to Top of Page

Datadave21
Starting Member

3 Posts

Posted - 2013-02-05 : 14:39:51
No the only fields it has are person_id, entry_date, modify_date .
Yes they were made by multiple people, I would like to see which people updated or added and how many records they did. I see that we will not be able to get that information from that table. I now think that this will be too difficult to do unless you can see the whole works. Let me know what you think.

Dave
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 14:50:04
Is the person_id in that table the same as the user_id in the user table? Or do they refer to two different entities (such as the person_id being a "customer_id" and user_id being an "employee_id")?

If they do not refer to the same entity, I don't see a way to capture the information on who made the insertion/update. Usually such info is stored in another column in the same table if you don't need to keep history of changes, or in audit tables if you do need history.
Go to Top of Page

Datadave21
Starting Member

3 Posts

Posted - 2013-02-05 : 16:56:48
Yes they represent two different identities. So I believe you are correct. However I managed a long work around, I get the person_id's from the list and then get the names that refers to the person_id, then check to see which community the person is from, that tells me who entered the information. Its not a true count but will work for now as long as there are not too many entries. Thanks for your help.

Dave

Dave
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 19:10:01
Even if it is the long way as you described, if all that data is in some tables in a manner that can be related to each other, it can be calculated via a T-SQL query.

So if you have a table that has the person_id's and the community id, and another one that has community_id and user_id, then those tables can be joined to the person table and user table to get the information you need. If that is the case, post the DDL for the tables and someone on the forum will be able to figure it out.

If you need help with generating DDL's, this article: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
   

- Advertisement -