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 |
|
sprotson
Yak Posting Veteran
75 Posts |
 Posted - 2013-01-06 : 06:37:41
|
| I am working on a stored procedure whihc is quite complex (well for my SQL knowledge level anyway).The purpose of the stored procedure is show the skills a call centre agent has assigned to them when any changes to these skills occur.For instance, an agent may have 5 skills, and when anither skill is added the stored procedure returns the date/time this skill was added and (in one column) the 6 skills they now have assigned (active skills).It should be noted that multiple skills can be added at the exact same date/time and these show in seperate lines of the query result.All values returned by the query are the same in this instance except the name of the skill added.This is working, but now I need to modify the stored procedure for another report.In this report where multiple skills are added at the same date/time, I only want to return one line, showing what the active skills are at that point.I have thought about using the MAX function, but have never used this before and was hoping for some advice.Firstly I need to know what column to use the max function against, and then does it return the max value for the whole query or could I use it to return the max value for each date/time a skill is added, ie if 5 skills are added only 1 is shown.Also, if it returns the max, how does this work when all the dates/times are the same. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-06 : 10:11:48
|
you can do something likeSELECT STUFF((SELECT ',' + Skill FROM table WHERE dateTimefield = t.dateTimefield FOR XML PATH('')),1,1,''),... other columnsFROM table t------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2013-01-06 : 11:46:14
|
quote:
Originally posted by visakh16
you can do something likeSELECT STUFF((SELECT ',' + Skill FROM table WHERE dateTimefield = t.dateTimefield FOR XML PATH('')),1,1,''),... other columnsFROM table tWould that not just give me a column with all the skills that were added at the same date/time?The code you suggested still shows the same data on multiple lines.I already use code similar to that to achieve that very same result.I need to be able to only show 1 line per date/time that a skill change occured.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-07 : 10:05:26
|
| it wont show it in multiple lines if you use it correctlyYou've not told us so far how exactly is your tables. I'm not sure whether all data in your case resides in same table or not.If you can post sample in below format, We might be able to provide more accurate solutionhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Max Values