| Author |
 Topic  |
|
|
sprotson
Yak Posting Veteran
70 Posts |
 Posted - 12/17/2012 : 19:09:28
|
I have a view that contains version data for skills, including
Name of Agent
Name of Skill
Level of Skill
Version Number of Skill
Date/Time skill level changed
For example
Agent 1, Test Skill, Level 1, v1, 10/12/12 00:00
Agent 1, Test Skill, Level 2, v2, 11/12/12 01:00
Agent 1, Test Skill 1, Level 1, v1, 11/12/12 01:00
I need to be able to combine all the skills and levels at each version date into 1 cell as follows
Agent 1, 10/12/12 00:00, Test Skill Level 1
Agent 1, 11/12/12 01:00, Test Skill Level 2 & Test Skill Level 1
I have used the following code in the past to combine all the skills and levels:
SELECT Agent,SkillandLevel,addeddate,STUFF
((SELECT ',' + Skillandlevel
FROM ViewName
WHERE Agent = h.Agent ANDFOR XML PATH('')), 1, 1, '') AS SkillsCombined
FROM (SELECT DISTINCT Agent,SkillandLevel,addeddate
FROM ViewName)h
As it stands this would combine all the column SkillandLevel for each agent into one cell.
How do I modify this to only combine SkillandLevel per agent for active skills - eg added date is greater than addeddate of v-1 but less than addeddate of v+1?
Any help would be appreciated
Edit: I forgot to say that I can also get the addeddate for v+1 and v-1 on the same line as the current version if required through joining duplicate tables |
Edited by - sprotson on 12/17/2012 20:10:52
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 12/19/2012 : 00:51:36
|
As per your posted sample data and output, this is what you want
SELECT Agent,addeddate,STUFF
((SELECT ',' + Skillandlevel
FROM ViewName
WHERE Agent = h.Agent AND addeddate = h.addeddate FOR XML PATH('')), 1, 1, '') AS SkillsCombined
FROM (SELECT DISTINCT Agent,addeddate
FROM ViewName)h
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 12/19/2012 : 01:56:25
|
Thanks, however I don't believe it is that simple.
That would work on the sample data, but possibly not on all data.
If the last entry in the sample data occurred even 1 second later then surely the data would not combine as the changes occurred at different times.
|
|
|
|
moldag
Starting Member
Norway
1 Posts |
Posted - 12/19/2012 : 02:54:08
|
If you are using SQL Server 2008 or later, you can cast the field addeddate as date to get rid of the time part.
SELECT Agent,addeddate,STUFF
((SELECT ',' + Skillandlevel
FROM ViewName
WHERE Agent = h.Agent AND cast(addeddate as date) = h.addeddate FOR XML PATH('')), 1, 1, '') AS SkillsCombined
FROM (SELECT DISTINCT Agent, cast(addeddate as date) as addeddate
FROM ViewName)h
/DM |
Edited by - moldag on 12/19/2012 02:56:26 |
|
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 12/19/2012 : 03:11:30
|
| I want to include the time. It needs to be precise as changes can happen multiple times a day. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 12/19/2012 : 03:19:38
|
quote:
Originally posted by sprotson
Thanks, however I don't believe it is that simple.
That would work on the sample data, but possibly not on all data.
If the last entry in the sample data occurred even 1 second later then surely the data would not combine as the changes occurred at different times.
then tell us how eaxctly is your merge condition? whats the timeslot over which you want skills to be merged
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 12/19/2012 : 03:39:39
|
The data for each skill/level is versioned, 1, 2, 3, 4 etc
For each version I can easily get the date each version was created.
I thought it may be as simple as added between addeddate v-1 and addeddate v+1, however I can see a coiuple of issues wuth that.
1) What if there is only 1 version, so not version +1 or -1
2) What if there are only 2 versions, so no version +1
3) The skills and levels can be on different versions, eg in the sample data Test Skill is on v2 but Test Skill 1 is on v1.
Essentially I need to see the max version of each skill/level combined at the point any change is made to a skill/level (eg a new version created).
So if I addd a fee more items in the sample data:
Agent 1, Test Skill, Level 1, v1, 10/12/12 00:00
Agent 1, Test Skill, Level 2, v2, 11/12/12 01:00
Agent 1, Test Skill 1, Level 1, v1, 11/12/12 01:00
Agent 1, Test Skill 2, Level 2, v1, 12/12/02:00
Agent 1, Test Skill 3, Level 1, v1, 12/12/02:30
Agent 1, Test Skill 3, Level 2, v2, 12/12/03:00
I need to be able to combine all the skills and levels at each version date into 1 cell as follows
Agent 1, 10/12/12 00:00, Test Skill Level 1
Agent 1, 11/12/12 01:00, Test Skill Level 2 & Test Skill 1 Level 1
Agent 1, 12/12/12 02:00, Test Skill Level 2 & Test Skill 1 Leve
l 1 & Test Skill 2 Level 2
Agent 1, 12/12/12 02:30, Test Skill Level 2 & Test Skill 1 Leve
l 1 & Test Skill 2 Level 2, Test Skill 3 Level 1
Agent 1, 12/12/12 03:00, Test Skill Level 2 & Test Skill 1 Leve
l 1 & Test Skill 2 Level 2, Test Skill 3 Level 2
Does that give you enough information on what O am trying to do?
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 12/19/2012 : 03:47:56
|
nope..not quite...didnt understand how test skill level 2 got merged with agent 1 for 12/12/12 02:00 for example as its version is v1 and skills version is v2
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 12/19/2012 : 04:07:00
|
These are seperate skills.
At 12/12/12 02:00 Test Skill 2 Level 2 was added to Agent 1. At that time the agent already had Test Skill Level 2 and Test Skill 1 Level 1.
I need to show the mac skill/level each time any of teh skills assigned to the agent changes. An agent may have 100 skills, and each time 1 of those skills changes I need to combine all the skills/levels they have even the other 99 that did not change |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 12/19/2012 : 05:00:06
|
ok..now its somewhat clear
try this and see if this is what you're after
SELECT h.Agent,h.addeddate,h.Skill + ' ' + h.Level + '&' + STUFF
((SELECT '&' + v.Skill + ' ' + v.level
FROM ViewName v
INNER JOIN (SELECT Agent,Skill,MAX(addedDate) AS latestdate
FROM ViewName
GROUP BY Agent,Skill
)v1
ON v1.Agent = v.Agent
AND v1.Skill = v.Skill
AND v1.latestdate = v.addedDate
WHERE v1.Agent = h.Agent AND v1.addeddate < h.addeddate FOR XML PATH('')), 1, 1, '') AS SkillsCombined
FROM (SELECT DISTINCT Agent,addeddate,Skill,Level
FROM ViewName)h
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 12/19/2012 : 05:47:19
|
Thanks for that, not tested it yet but will do when I get a chance.
A colleague of mine just got it to work in his own way, so will be interesting to see if it is any different to your method.
Thanks Again |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 12/19/2012 : 08:07:21
|
ok...let me know once you've tested...also curious on other approach as well if you could post!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Combing mutiple rows into one cell with conditions
