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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate the % split (SOLVED)

Author  Topic 

Ziggadebo
Starting Member

20 Posts
Posted - 2011-04-13 : 07:22:24
Hi all, I'm new to this site and to SQL programming so apologies if I've used the wrong terminolgy within this post.

Basically I've created a table in SQL server 2008 as below, except the '%_Split' column is presently blank.

Month Event Owner HOURS % _Split
Jan EAT DAVE 5 38.46
Jan SLEEP BILL 3 25.00
Jan DRINK MAGGIE 6 85.71
Jan EAT PETE 8 61.54
Jan SLEEP BORIS 9 75.00
Jan DRINK JOHN 1 14.29

What I'd like to do is write some SQL code to populate this column with the % split based on the values in the HOURS column with variables of Month & Event.

Now I can easily do this in excel using the following formula
D2/SUMIF(B:B,B2,D:D)*100

Can anyone point me in the right direction for doing this in SQL?

I know I could find the totals by using the following SQL

Select Month,Event,sum(HOURS) from my_table
Group by Month,Event

but then how could i use them to calcuate the % split?


Thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-13 : 07:32:08
You'll probably need to use a sum(case when..) expression. I can't see your Excel spreadsheet from here, so I don't know what's in columns B and D! With the example you provided, could you describe what it is you're trying to do?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Ziggadebo
Starting Member

20 Posts
Posted - 2011-04-13 : 07:39:45
Sorry Columns in excel represent the 5 columns I've specified.

So column a is Month, b is Event, c is Owner, D is HOURS, E is %_Split

I'm trying to work out for each row the number of hours each person has spent doing an event as a percentage of the total number of hours for each event in that month.

So for example Two people ate in Jan (Dave & Pete) for 5 and 8 hours respectively. What I want in column %_Split is the percentage i.e 5/(5+8)*100 & 8/(5+8)*100

Although this example only contains one month, I intend to use the query on data that will contain multiple months.

Thanks for trying to help.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-13 : 08:25:50
Here ya go

DECLARE @Table TABLE (Month char(3),Event varchar(10),Owner varchar(10),Hours int, Split numeric (5,2))

INSERT INTO @Table
SELECT 'Jan','EAT','DAVE', 5, 38.46 UNION ALL
SELECT 'Jan','SLEEP','BILL', 3, 25.00 UNION ALL
SELECT 'Jan','DRINK','MAGGIE', 6 ,85.71 UNION ALL
SELECT 'Jan','EAT','PETE', 8 ,61.54 UNION ALL
SELECT 'Jan','SLEEP','BORIS', 9 ,75.00 UNION ALL
SELECT 'Jan','DRINK','JOHN', 1 ,14.29

SELECT [Month],[event],Hours,[owner]
,Hours*100.0/sum(hours) over(partition by [event]) as EventHours
FROM @table

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-13 : 08:34:56
This is exactly why I hang around here... I didn't realize you could use 'Over' with aggregates.

I always loved 2000... but I have to admit, 2005/8 have some pretty handy features

thanks for sharing

quote:
Originally posted by jimf

Here ya go

DECLARE @Table TABLE (Month char(3),Event varchar(10),Owner varchar(10),Hours int, Split numeric (5,2))

INSERT INTO @Table
SELECT 'Jan','EAT','DAVE', 5, 38.46 UNION ALL
SELECT 'Jan','SLEEP','BILL', 3, 25.00 UNION ALL
SELECT 'Jan','DRINK','MAGGIE', 6 ,85.71 UNION ALL
SELECT 'Jan','EAT','PETE', 8 ,61.54 UNION ALL
SELECT 'Jan','SLEEP','BORIS', 9 ,75.00 UNION ALL
SELECT 'Jan','DRINK','JOHN', 1 ,14.29

SELECT [Month],[event],Hours,[owner]
,Hours*100.0/sum(hours) over(partition by [event]) as EventHours
FROM @table

Jim

Everyday I learn something that somebody else already knew


Corey

I Has Returned!!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-13 : 08:42:52
quote:
Originally posted by Seventhnight

This is exactly why I hang around here... I didn't realize you could use 'Over' with aggregates.


And also to sue innocent people who sometimes make mistakes in their responses
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-13 : 08:46:24
Well yeah... of course there is that

Gotta get rich somehow.

quote:
Originally posted by sunitabeck

quote:
Originally posted by Seventhnight

This is exactly why I hang around here... I didn't realize you could use 'Over' with aggregates.


And also to sue innocent people who sometimes make mistakes in their responses


Corey

I Has Returned!!
Go to Top of Page

Ziggadebo
Starting Member

20 Posts
Posted - 2011-04-13 : 09:40:41
Firstly I'm glad that others have gotten something useful out of this, but this doesnt do what I want it to:

If I select all records from my table I have:

Month Event Owner HOURS % _Split
Jan EAT DAVE 5
Jan SLEEP BILL 3
Jan DRINK MAGGIE 6
Jan EAT PETE 8
Jan SLEEP BORIS 9
Jan DRINK JOHN 1

If I run the code you gave me, I get the %_Split column filled in, but only because we've put the values into select statements? I want SQL to work out the values???

Am I missing something???????
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-13 : 09:57:32
The split column in the table is just reference... It is not used in the select...

set all of the split values to 0 in the table... and you'll still get the correct results.

Corey

I Has Returned!!
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-13 : 09:58:46
Yes. That my code works! I loaded up your split values, but I am still calculationg them here:
,Hours*100.0/sum(hours) over(partition by [event]) as EventHours

Try This

DECLARE @Table TABLE (Month char(3),Event varchar(10),Owner varchar(10),Hours int, Split numeric (5,2))

INSERT INTO @Table
SELECT 'Jan','EAT','DAVE', 5, 38.46 UNION ALL
SELECT 'Jan','SLEEP','BILL', 3, 25.00 UNION ALL
SELECT 'Jan','DRINK','MAGGIE', 6 ,85.71 UNION ALL
SELECT 'Jan','EAT','PETE', 8 ,61.54 UNION ALL
SELECT 'Jan','SLEEP','BORIS', 9 ,75.00 UNION ALL
SELECT 'Jan','DRINK','JOHN', 1 ,14.29

SELECT [Month],[event],Hours,[owner]
,Split as SplitNumbersProvidedByZiggadebo
,Hours*100.0/sum(hours) over(partition by [event]) as SplitHoursCalculated
FROM @table


Everyday I learn something that somebody else already knew
Go to Top of Page

Ziggadebo
Starting Member

20 Posts
Posted - 2011-04-13 : 10:13:22
ok brilliant, I understand.

However I want the results written to the %_Split column in the table, how can I achieve that?

Basically each month I will add more data, I then want to create a trigger which will populate the split column (I'll be back when I get into the trigger part!!!!!)

Thanks for all your help, I really apprciate it.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-13 : 10:27:57
Why come back... well not like that.. but why wait, when we are already working with this? Just do the trigger now...

Should be something like this... should definitely set up as a test first 


Create Trigger trg_WhatEvs
On dbo.YourTable
After Insert, Update, Delete
As

Set NoCount On

Declare @keys table (
	kMonth varchar(100), --hopefully the data doesn't represent you're exact key info
	kEvent varchar(100),
	Primary Key (kMonth, kEvent)
)

Insert Into @keys
Select kMonth, kEvent From inserted
Union 
Select kMonth, kEvent From deleted


	Update A
	Set
		Split = Hours*100.0/sum(hours) over(partition by kMonth, kEvent) as SplitHoursCalculated
	FROM YourTable A
	Inner Join @Keys B
	On A.kMonth = B.kMonth
	and A.kEvent = B.kEvent

Set NoCount Off
Go


* Edited to add in 'Set'

Corey

I Has Returned!!
Go to Top of Page

Ziggadebo
Starting Member

20 Posts
Posted - 2011-04-13 : 10:48:39
ok my table is called tablexyz

I've taken your code and changed the variable names to fit (I think)


Create Trigger trg_WhatEvs
On dbo.tablexyz
After Insert, Update, Delete
As

Set NoCount On

Declare @keys table (
kMonth nvarchar(255), --hopefully the data doesn't represent you're exact key info
kEvent nvarchar(255),
Primary Key (kMonth, kEvent)
)

Insert Into @keys
Select kMonth, kEvent From inserted
Union
Select kMonth, kEvent From deleted


Update A
Split = HOURS*100.0/sum(HOURS) over(partition by kMonth, kEvent) as SplitHoursCalculated
FROM tablexyz A
Inner Join @Keys B
On A.kMonth = B.kMonth
and A.kEvent = B.kEvent

Set NoCount Off
Go

but I'm getting the following error msg

Msg 102, Level 15, State 1, Procedure trg_WhatEvs, Line 22
Incorrect syntax near 'Split'.

Any Ideas?
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-13 : 10:56:16
Forgot the Set... sorry :P


Update A 
Set
	Split = HOURS*100.0/sum(HOURS) over(partition by kMonth, kEvent) as SplitHoursCalculated
FROM tablexyz A
Inner Join @Keys B
On A.kMonth = B.kMonth




Corey

I Has Returned!!
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-04-13 : 10:57:49
[code]Update A
SET Split = HOURS*100.0/sum(HOURS) over(partition by kMonth, kEvent) as SplitHoursCalculated
FROM tablexyz A
Inner Join @Keys B
On A.kMonth = B.kMonth
and A.kEvent = B.kEvent[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

Ziggadebo
Starting Member

20 Posts
Posted - 2011-04-13 : 11:04:07
Thanks getting closer I think, changing to this:
Update A
SET Split = HOURS*100.0/sum(HOURS) over(partition by kMonth, kEvent) as SplitHoursCalculated FROM tablexyz A
Inner Join @Keys B
On A.kMonth = B.kMonth
and A.kEvent = B.kEvent

now generates this error msg:

Msg 156, Level 15, State 1, Procedure trg_WhatEvs, Line 22
Incorrect syntax near the keyword 'as'.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-13 : 11:11:07
quote:
Originally posted by Ziggadebo

Thanks getting closer I think, changing to this:
Update A
SET Split = HOURS*100.0/sum(HOURS) over(partition by kMonth, kEvent) as SplitHoursCalculated FROM tablexyz A
Inner Join @Keys B
On A.kMonth = B.kMonth
and A.kEvent = B.kEvent

now generates this error msg:

Msg 156, Level 15, State 1, Procedure trg_WhatEvs, Line 22
Incorrect syntax near the keyword 'as'.


Corey

I Has Returned!!
Go to Top of Page

Ziggadebo
Starting Member

20 Posts
Posted - 2011-04-13 : 11:18:47
ok code looks like this now:

Create Trigger trg_WhatEvs
On dbo.tablexyz
After Insert, Update, Delete
As

Set NoCount On

Declare @keys tablexyz (
kMonth nvarchar(255)
kEvent nvarchar(255)
Primary Key (kMonth, kEvent)
)

Insert Into @keys
Select kMonth, kEvent From inserted
Union
Select kMonth, kEvent From deleted


Update A
SET Split = HOURS*100.0/sum(HOURS) over(partition by kMonth, kEvent)
FROM tablexyz A
Inner Join @Keys B
On A.kMonth = B.kMonth
and A.kEvent = B.kEvent




Set NoCount Off
Go

error message is:

Msg 102, Level 15, State 1, Procedure trg_WhatEvs, Line 10
Incorrect syntax near '('.
Msg 1087, Level 15, State 2, Procedure trg_WhatEvs, Line 15
Must declare the table variable "@keys".
Msg 1087, Level 15, State 2, Procedure trg_WhatEvs, Line 24
Must declare the table variable "@Keys".

I have changed this row from
Declare @keys table (
to
Declare @keys tablexyz (
was that correct?
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-13 : 11:22:38
quote:
Originally posted by Ziggadebo

ok code looks like this now:

Create Trigger trg_WhatEvs
On dbo.tablexyz
After Insert, Update, Delete
As

Set NoCount On

Declare @keys tablexyz (
kMonth nvarchar(255)
kEvent nvarchar(255)
Primary Key (kMonth, kEvent)
)

Insert Into @keys
Select kMonth, kEvent From inserted
Union
Select kMonth, kEvent From deleted


Update A
SET Split = HOURS*100.0/sum(HOURS) over(partition by kMonth, kEvent)
FROM tablexyz A
Inner Join @Keys B
On A.kMonth = B.kMonth
and A.kEvent = B.kEvent




Set NoCount Off
Go

error message is:

Msg 102, Level 15, State 1, Procedure trg_WhatEvs, Line 10
Incorrect syntax near '('.
Msg 1087, Level 15, State 2, Procedure trg_WhatEvs, Line 15
Must declare the table variable "@keys".
Msg 1087, Level 15, State 2, Procedure trg_WhatEvs, Line 24
Must declare the table variable "@Keys".

I have changed this row from
Declare @keys table (
to
Declare @keys tablexyz (
was that correct? No. The 'Declare @keys table' defines a table variable called '@keys'


Corey

I Has Returned!!
Go to Top of Page

Ziggadebo
Starting Member

20 Posts
Posted - 2011-04-13 : 11:27:44
you must be thinking, wish I'd just helped him update without the trigger?

Still getting an error message with the code below:


Create Trigger trg_WhatEvs
On dbo.tablexyz
After Insert, Update, Delete
As

Set NoCount On

Declare @keys table (
kMonth nvarchar(255),
kEvent nvarchar(255),
Primary Key (kMonth, kEvent)
)
Insert Into @keys
Select kMonth, kEvent From inserted
Union
Select kMonth, kEvent From deleted


Update A
SET Split = HOURS*100.0/sum(HOURS) over(partition by kMonth, kEvent)
FROM tablexyz A
Inner Join @Keys B
On A.kMonth = B.kMonth
and A.kEvent = B.kEvent




Set NoCount Off
Go


error msg:

Msg 207, Level 16, State 1, Procedure trg_WhatEvs, Line 15
Invalid column name 'kMonth'.
Msg 207, Level 16, State 1, Procedure trg_WhatEvs, Line 15
Invalid column name 'kEvent'.
Msg 207, Level 16, State 1, Procedure trg_WhatEvs, Line 17
Invalid column name 'kMonth'.
Msg 207, Level 16, State 1, Procedure trg_WhatEvs, Line 17
Invalid column name 'kEvent'.
Msg 207, Level 16, State 1, Procedure trg_WhatEvs, Line 24
Invalid column name 'kMonth'.
Msg 207, Level 16, State 1, Procedure trg_WhatEvs, Line 25
Invalid column name 'kEvent'.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-13 : 11:38:43
Parentheses!
Declare @keys table
(
kMonth nvarchar(255),
kEvent nvarchar(255),
Primary Key (kMonth, kEvent)
)

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site