March 21, 2013 at 11:36 am
I am trying to update the GP%age column without a loop.
I have a table with 3 columns:
PostDate, Category and Amount
PostDateCategoryAmount
3/1/20131 Gross Sales500
3/1/20132 Sales Adjustments-50
3/1/20133 Freight25
3/1/20134 Net Sales475
3/1/20135 COGS300
3/1/20135 Gross Profit175
3/1/20136 GP%age0
3/1/20138 Operating Expense-50
3/4/20131 Gross Sales1000
3/4/20132 Sales Adjustments-100
3/4/20133 Freight 100
3/4/20134 Net Sales1000
3/4/20135 COGS700
3/4/20135 Gross Profit300
3/4/20136 GP%age0
3/4/20138 Operating Expense-25
I need to take '5 Gross Profit' / '4 Net Sales' and put that into '6 GP%age' grouping by day.
I appreciate the help I am totally blanking on a slick solution. I am using Excel PowerPivot to pivot the data, very cool. Just need the %age. Thanks
March 21, 2013 at 12:49 pm
You aren't new around here so you shouldn't be surprised that we need to see ddl, sample data and desired output. Please take a few minutes and read the article at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2013 at 2:31 pm
Thanks for the response. I was just hoping someone would say look at xxxx or such. It's my first ever request for help I believe. I can write the loop code faster than I can post the ddl and it's basically instant so no need I guess.
March 21, 2013 at 2:35 pm
John Hanrahan (3/21/2013)
Thanks for the response. I was just hoping someone would say look at xxxx or such. It's my first ever request for help I believe. I can write the loop code faster than I can post the ddl and it's basically instant so no need I guess.
I figured that with over 300 points you had perused the forums. Without something a little concrete to go on it is pretty hard to offer much help. I can say with certainty that a loop is not going to be the best performer here but if you are ok with it then I guess that works. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2013 at 2:50 pm
John Hanrahan (3/21/2013)
Thanks for the response. I was just hoping someone would say look at xxxx or such. It's my first ever request for help I believe. I can write the loop code faster than I can post the ddl and it's basically instant so no need I guess.
Sorry, but it really would not have taken you that long to write a CREATE TABLE and an INSERT INTO statement to help us out.
In the future you should consider doing it as we may not take the time ourselves. The more you do to help us, the more we will help you.
By the way: Look Ma no loops!
create table #TestTable (
PostDate date,
Category varchar(24),
Amount decimal(12,2)
);
insert into #TestTable
values
('3/1/2013', '1 Gross Sales', 500),
('3/1/2013', '2 Sales Adjustments', -50),
('3/1/2013', '3 Freight', 25),
('3/1/2013', '4 Net Sales', 475),
('3/1/2013', '5 COGS', 300),
('3/1/2013', '5 Gross Profit', 175),
('3/1/2013', '6 GP%age', 0),
('3/1/2013', '8 Operating Expense', -50),
('3/4/2013', '1 Gross Sales', 1000),
('3/4/2013', '2 Sales Adjustments', -100),
('3/4/2013', '3 Freight', 100),
('3/4/2013', '4 Net Sales', 1000),
('3/4/2013', '5 COGS', 700),
('3/4/2013', '5 Gross Profit', 300),
('3/4/2013', '6 GP%age', 0),
('3/4/2013', '8 Operating Expense', -25);
select * from #TestTable;
with Profit as (select PostDate, Category, Amount from #testTable where Category = '5 Gross Profit'),
NetSales as (select PostDate, Category, Amount from #testTable where Category = '4 Net Sales'),
GPUpdate as (select PostDate, Category, Amount from #testTable where Category = '6 GP%age')
update gp set
Amount = p.Amount/n.Amount
from
GPUpdate gp
inner join Profit p
on (gp.PostDate = p.PostDate)
inner join NetSales n
on (gp.PostDate = n.PostDate);
select * from #TestTable;
drop table #TestTable;
Edit: Fix quote bug.
March 21, 2013 at 2:56 pm
See no loop-I knew it was possible. Thanks Lynn. In the future I will post more info but I did write the loop in under 5 minutes. Speed doesn't really matter for this as it's only got to do 31 iterations at most. My big problem is my biggest strength. I am lazy. 🙂
March 21, 2013 at 3:02 pm
John Hanrahan (3/21/2013)
See no loop-I knew it was possible. Thanks Lynn. In the future I will post more info but I did write the loop in under 5 minutes. Speed doesn't really matter for this as it's only got to do 31 iterations at most. My big problem is my biggest strength. I am lazy. 🙂
You say this now: Speed doesn't really matter for this as it's only got to do 31 iterations at most, but we all know things change. Maybe not this process, but something else you may need to do in the future. You should not settle for "this works for what I have to do here because <whatever reason>" because it keeps you from looking for better ways now instead of when it is crunch time.
March 21, 2013 at 3:09 pm
In this particular case it's can only ever have one month's worth of data so 9 * 31 rows. That said it's always better to do it better (or is that gooder). What's funny is I use a WITH statement do build most of the data. If I had realized you could use them with UPDATE I might have gone for that. Continuous learning is the best.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply