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 |
|
joe_ryan
Starting Member
2 Posts |
 Posted - 2007-11-19 : 15:24:38
|
| I have the need to pull a query on an attendance table. The table definition looks somewhat like this.user_id INT, class_id INT, att_type CHAR(1), att_date DATETIMEEvery time someone is marked as attending a class, a row is added so a table that looks like this...user_id | class_id | att_type | att_date |------------------------------------------1 34 A 1/1/20071 34 A 1/1/20071 22 P 1/2/20071 66 T 1/3/2007 2 34 A 1/1/20072 22 P 1/2/20072 66 T 1/3/2007 2 34 A 1/1/20073 22 P 1/2/20073 66 T 1/3/2007 3 22 P 1/2/20073 66 T 1/3/2007What I am trying to do is generate a query that has columns like this... name_id | class_id | [1/1/2007] | [1/2/2007] | [1/3/2007] |-----------------------------------------------------------1 22 A P P2 22 A A P3 22 P P Awhere I would say get me the last 6 meeting dates only. (Obviously getting all the dates would be crazy)Now, this table has millions of rows so I am looking for the best way to do this performance wise. I have written a pretty lengthy Dynamic SQL query but obviously that doesn't help with performance at all.The query I have basically does this.1. Finds the last 6 dates and puts them in a temp table.2. For each date in that table do join setting the column to the date, join on the user_id and get the att_type.I have searched all over looking for a better solution so any help would be appreciated.Thanks in advance, Joe Ryan |
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-11-19 : 17:07:02
|
| If you are using sql server 2005 then u can use "pivot"...and its easy. I did answered previously something like this on this forum and I couldnt find it to give the solution. |
 |
|
|
joe_ryan
Starting Member
2 Posts |
Posted - 2007-11-20 : 11:11:14
|
| Thanks for the suggestion but unfortunately we are still stuck with SQL 2000. I searched for how to do a pivot (cross-tab) query in T-SQL 2000 and many articles came up. I made a simple one using case statements generated dynamically and that was much faster than my multiple join method.The only real difference in my query is that it only summarizes one unique value per aggregated group and the value is of the type CHAR. Summarizing with the MAX function takes care of all that and I have my solution.Just a note to anyone else reading this. This kind of data manipulation is best done in the presentation or reporting layer so this solution for us is temporary. |
|
|
|
|
|
|
Attendance Query