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 |
|
lvirden
Starting Member
4 Posts |
 Posted - 2011-09-09 : 14:00:58
|
| Table 1 has a column which is a list of names of items.Table 2 is a list of problems reported, with columns for time the user reported the problem , name of item identified as part of the problem, etc.For example:Table 1TableChairLampRugTable 21,Table,table wobbles,1/1/112,Table,table is dirty,2/2/113,Run,rug is out of place,2/13/114,Lamp,bulb is burned out,4/1/11Now, what i am wondering is how to get 2 reports out of this setup1) a list of each item in table 1 and the number of times that value appears in table 2, sorted by the number of appearances. Note that in the above examples, the resulting report should show chair as having 0 appearances.2) a list of each table 1 item, and the most recent time it appeared in a reportIf an item has never appeared, its should be treated as a date of 0.Do these two report descriptions seem as if they could be done via straight SQL, or am I going to have to figure out how to write some code around the SQL?Thank you! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-09 : 14:07:56
|
| they both are straight forward. try it out and we will help you if you face issues. i will give you some hints1, use left join and count after doing group by2, use left join and max after doing group by------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
lvirden
Starting Member
4 Posts |
|
|
|
|
|
|
|
Determining number of occurrances of values