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 |
|
jonnygrim
Starting Member
1 Post |
 Posted - 2010-01-05 : 18:32:34
|
| Ok, where to start..I'm new to sql, always used access and decided its time to move on with the times and start using sql. So..I have created a table that has a list of all our company numbers and users. Then each month ive added a new table with the expenditure of the top 10 users. Ideally I want a report that shows each number and which months they have been listed in so that i can track repeat offenders.Whats the best way to do this and is it possible to achieve what i want from sql.Thanks in advance. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-05 : 20:47:23
|
can you show us how does your tables looks like ?quote:
Then each month ive added a new table
This doesn't sound very good. You should not be creating one table for each month. Add a datetime column to the table and make it part of the primary key. Store the first day of the month in that column
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-06 : 03:15:32
|
you can just use a single table as Tan suggested and put aggregated top 10 users info each month (may be schedule a job to run each month once to populate date with months first date). then for interested period, just run a query to look into table and take count of occurances of users to identify repeat offenders.so if you period is from 01/01/2009 to 01/01/2010 query will be likeSELECT TOP 10 UserNameFROM(SELECT UserName,COUNT(*) AS OccuranceFROM YourTableWHERE MonthDate > '2009-01-01'AND MonthDate < '2010-01-01'GROUP BY UserName)tORDER BY Occurance DESC to list top 10 repeated offenders |
|
|
|
|
|
|
Noobie to Sql