December 21, 2007 at 8:59 am
OK, maybe it's because today is my last day with my current employer (moving on to bigger and better things 😀 ), but this has got me really stumped.
I'll start with test data and expected results, then give the rules.
DECLARE @sampleData TABLE
(regionCode VARCHAR(30)
,locationID INT
,fullName VARCHAR(80)
,jobTitle VARCHAR(80)
,billable NUMERIC(9,2)
,nonBillable NUMERIC(9,2)
,activityName VARCHAR(4000))
INSERT @sampleData
SELECT 'Central', 72, 'DRAKE, SEAN', '', 0.00, 153.00, 'Office' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 18.50, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 0.00, 14.75, 'Waiting' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 0.00, 1.25, 'Office' UNION ALL
SELECT 'Central', 72, 'JOHNSON, ROBERT', '', 8.00, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 39.00, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 0.50, 'Meeting' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 9.25, 'Waiting' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 1.00, 'Other'
/* Required Results
regionCode locationID fullName jobTitle billable nonBillable nonbillableActivityNames
Central 72 DRAKE, SEAN 0.00 153.00 Office
Central 72 FANN, ASHLEY Cook 18.50 16.00 Waiting, Office
Central 72 JOHNSON, ROBERT Cook 8.00 0.00
Central 72 LEE, CAROL 39.00 10.75 Meeting, Waiting, Other
*/
The complexity comes in with the nonBillable and nonbillableActivityNames columns.
- the nonBillable column is the sum for all values in the nonBillable column for any activityName other than 'Billable Time' for that instance of fullName (group by)
- the column nonbillableActivityNames needs to be a column delimited list of the activityName values other than 'Billable Time' for that instance of fullName (group by)
- the number of possible activityNames is unknown
I had thought of using a function, but I'm not too sure how to do that since this is a result set. I can't simply query the source data directly because there's a lot going on just to get the "sampleData" above.
Any suggestions are appreciated....
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 21, 2007 at 9:12 am
Not quite sure I understood. You have an activity called billable time, and then you have 2 columns called billable/non-billable?
What happens if the Billable time has something in the non-billable column?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 21, 2007 at 9:15 am
Still- looks to me that an old-style pivot syntax (the sum(case when ...end) type of statement) ought to do what you wish
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 21, 2007 at 9:15 am
Matt Miller (12/21/2007)
Not quite sure I understood. You have an activity called billable time, and then you have 2 columns called billable/non-billable?What happens if the Billable time has something in the non-billable column?
It won't, it's actually a by product of the first version of the request from our people. I can gaurantee that Billable time will NEVER have anything in the non-billable column.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 21, 2007 at 9:17 am
Matt Miller (12/21/2007)
Still- looks to me that an old-style pivot syntax (the sum(case when ...end) type of statement) ought to do what you wish
That only get s part of it. I need the nonbillableActivityNames to be comma delimited together...
And i hate to say it, but I'm off to a "going away" lunch so I won't be able to answer any questions for a couple hours.....
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 21, 2007 at 9:57 am
Well - the problem becomes MUCH simpler if you can implement CLR.
The Book online example for User-defined aggregates is a concatenate function, which would make this whole deal a whole lot easier....and it wouldn't take much to concatenate only unique values (although it will KILL performance on big sets).
Here's the URL
http://msdn2.microsoft.com/en-us/library/ms131056.aspx
Let me know if you need help that way. Otherwise - it's a temp table to concatenate the values, and then joining to the temp table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 21, 2007 at 10:14 am
If you don't mind creating a function, this will do it.
if object_id('udf_csvlist') > 0
drop function udf_csvlist
go
create function udf_csvlist (@name varchar(255))
returns varchar(8000)
as
begin
declare @list varchar(8000)
select @list = coalesce(@list,'') + activityName + ', ' from sampleData
where fullname = @name
and activityname <> 'Billable Time'
set @list = left(@list, len(@list) - 1)
if @list is Null
set @list = ''
return @list
end
go
if object_id('sampleData') > 0
drop TABLE sampleData
CREATE TABLE sampleData (regionCode VARCHAR(30) ,locationID INT ,fullName VARCHAR(80) ,jobTitle VARCHAR(80) ,billable NUMERIC(9,2) ,nonBillable NUMERIC(9,2) ,activityName VARCHAR(4000))
INSERT sampleData
SELECT 'Central', 72, 'DRAKE, SEAN', '', 0.00, 153.00, 'Office' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 18.50, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 0.00, 14.75, 'Waiting' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 0.00, 1.25, 'Office' UNION ALL
SELECT 'Central', 72, 'JOHNSON, ROBERT', '', 8.00, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 39.00, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 0.50, 'Meeting' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 9.25, 'Waiting' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 1.00, 'Other'
/* Required Results
regionCode locationID fullName jobTitle billable nonBillable nonbillableActivityNames
Central 72 DRAKE, SEAN Cook 0.00 153.00 Office
Central 72 FANN, ASHLEY Cook 18.50 16.00 Waiting, Office
Central 72 JOHNSON, ROBERT Cook 8.00 0.00
Central 72 LEE, CAROL Cook 39.00 10.75 Meeting, Waiting, Other
*/
SELECT regionCode, locationID, fullName, jobTitle , sum(billable) billable, sum(nonbillable)
nonbillable, dbo.udf_csvlist(fullname) nonbillableActivityNames
FROM sampleData GROUP BY regionCode, locationID, fullName, jobTitle
December 21, 2007 at 10:30 am
Heh... I'm the new guy to 2k5 and even I know that you don't need a CLR to solve this bad-whammer with some very simple and fast code... 😛
Here's the whole shootin' match including the original test data...
I even took the liberty of aligning the decimal points...
--===== Create a test table
DECLARE @sampleData TABLE
(regionCode VARCHAR(30)
,locationID INT
,fullName VARCHAR(80)
,jobTitle VARCHAR(80)
,billable NUMERIC(9,2)
,nonBillable NUMERIC(9,2)
,activityName VARCHAR(4000))
--===== Populate the test table
INSERT @sampleData
SELECT 'Central', 72, 'DRAKE, SEAN', '', 0.00, 153.00, 'Office' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 18.50, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 0.00, 14.75, 'Waiting' UNION ALL
SELECT 'Central', 72, 'FANN, ASHLEY', 'Cook', 0.00, 1.25, 'Office' UNION ALL
SELECT 'Central', 72, 'JOHNSON, ROBERT', '', 8.00, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 39.00, 0.00, 'Billable Time' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 0.50, 'Meeting' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 9.25, 'Waiting' UNION ALL
SELECT 'Central', 72, 'LEE, CAROL', '', 0.00, 1.00, 'Other'
--===== Solve the problem per the requirements
SELECT t2.RegionCode,
t2.LocationID,
t2.FullName,
t2.JobTitle,
STR(SUM(t2.Billable),8,2) AS Billable,
STR(SUM(t2.NonBillable),8,2) AS NonBillable,
ISNULL(
STUFF((SELECT ', '+t1.ActivityName
FROM @sampleData t1
WHERE t1.RegionCode = t2.RegionCode
AND t1.LocationID = t2.LocationID
AND t1.FullName = t2.FullName
AND t1.JobTitle = t2.JobTitle
AND t1.ActivityName NOT IN ('Billable Time') FOR XML PATH (''))
,1,2,'')
,'') AS NonBillableActivityNames
FROM @sampleData t2
GROUP BY
t2.RegionCode,
t2.LocationID,
t2.FullName,
t2.JobTitle
And, look Ma... no function!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2007 at 12:46 pm
Jeff, thank you, that's perfect! that makes this day that much better, cause now I can go home. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 21, 2007 at 1:06 pm
Think of it as a "going away present", Jason. Good luck on your new job!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2007 at 4:05 pm
Thanks, After 5 years of heavy development and studying and a tad of admin I'm off to my first DBA position. It's still going to be heavy development, but more admin.
It's thanks to you (Jeff), Matt, Steve J and countless others on this site that's helped me get to where I am. That's why I try to give back as much as possible on here all the while trying to learn more and more.
Happy holiday's all!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 21, 2007 at 6:11 pm
That's quite a compliment, Jason. Thanks. Glad to see someone take it to the next level by actually getting a better job from what they're learned by looking at the code on this forum AND trying things on their own in reply's to other posters.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2007 at 6:19 pm
No problem Jeff! I'm really starting to se the true value of the Tally table BTW.. 😀
Hey, you never answered my PM.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 21, 2007 at 6:34 pm
I take a few days off, and I forget one of the better tricks I learned this year.... But I see Jeff dusted it off! (Good to see you're starting to like some of the new tricks out there Jeff!)
Thank you for the high praise. I can hardly take credit for any help I might have provided, since I am usually spitting back out tips and tricks I learned on here. I'm happy to be of service though.
Good Luck in your new position!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 21, 2007 at 8:11 pm
(Good to see you're starting to like some of the new tricks out there Jeff!)
Shoot... once I learned it, I was just aching for a place to use it! It's the only way I can keep up with you guys!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply