SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Sql Script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Datadave21
Starting Member

Canada
3 Posts

Posted - 02/05/2013 :  14:13:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 02/05/2013 :  14:18:34  Show Profile  Reply with Quote
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

Canada
3 Posts

Posted - 02/05/2013 :  14:39:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 02/05/2013 :  14:50:04  Show Profile  Reply with Quote
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

Canada
3 Posts

Posted - 02/05/2013 :  16:56:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 02/05/2013 :  19:10:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000