SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Max Values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sprotson
Yak Posting Veteran

75 Posts

Posted - 01/06/2013 :  06:37:41  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/06/2013 :  10:11:48  Show Profile  Reply with Quote
you can do something like

SELECT STUFF((SELECT ',' + Skill FROM table WHERE dateTimefield = t.dateTimefield FOR XML PATH('')),1,1,''),... other columns
FROM table t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 01/06/2013 :  11:46:14  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

you can do something like

SELECT STUFF((SELECT ',' + Skill FROM table WHERE dateTimefield = t.dateTimefield FOR XML PATH('')),1,1,''),... other columns
FROM table t


Would 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 MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2013 :  10:05:26  Show Profile  Reply with Quote
it wont show it in multiple lines if you use it correctly
You'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 solution

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000