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.
| Author |
Topic |
|
Knarf180
Starting Member
42 Posts |
 Posted - 2004-06-07 : 10:25:26
|
| I need to run a pretty complicated query (atleast at my skill level)Basically its checking how many edits to the table a user is making per day. It will be running agains the following..Add_Date (datetime)Add_User (int)Change_Date (datetime)Change_User (int)If the record has been added Add_Date and Change_Date will be the same, once it has been edited Change_Date changes to the date of change. Change_User and Add_User contain an integer which refers back to the user whom has made the changes. What I need to calculate his how many adds and changes a person has made year to date. I'm not sure if its possible to group it according to this (Prolly be pretty difficult) but if it can pull the record on the specified day it would be decent, I could simply just run it through a loop overnight.To sum it all up.... Calculate the amount of edits done by a user on a given day of the year or possibly year to date. Calculate them off of Add_Date and Change_Date. But if Add_Date = Change_Date it only counts as one change.Is it just me or is this seriously complicated? |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-07 : 10:38:45
|
| If change_date *changes* each time, then how do you expect to retrieve historical data? Do you mean that a record is added to the change table?You are best off providing some DDL.-------Moo. :) |
 |
|
|
Knarf180
Starting Member
42 Posts |
Posted - 2004-06-07 : 11:00:48
|
| Pretty much this is a one time thing. Whenever the boss wants a report on how hard the employees are working the report is run. Change_Date changes each time that record is changes, which is most likely only once. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-07 : 11:10:19
|
| Do you see what I'm saying though. If the change_date changes each time, then how can you expect to provide a list of the number of changes? You will only ever have a maximum of one change recorded.-------Moo. :) |
|
|
|
Knarf180
Starting Member
42 Posts |
Posted - 2004-06-07 : 11:12:03
|
| If Change_Date is changed more then once then the passed changes were most likely error corrections. Only the last change will really count since that is the person that did the work to finish off the record. |
|
|
|
Knarf180
Starting Member
42 Posts |
Posted - 2004-06-07 : 11:21:34
|
| Spoke with the boss and got her to drop this project and just got by year, that a monkey off my back. Thanks for the help though Mr Mist. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-07 : 12:03:50
|
| It's not that complicated....but you do need a historey table a trigger, thena sql statement with scalar functions....Brett8-) |
|
|
|
|
|
|
Complicated Query