Dividing by fields from a previous row

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

Dividing by fields from a previous row

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-06-16 : 16:04:32

I have a query that looks like this:


	SELECT 
		h.description,
		h.GCO_001, 
		'Total= h.HCR_048,
                'All Agents' = h.HCR_024 + h.HCR_025,
		'Percentage' = h.HCR_048/(h.HCR_024 + h.HCR_025)
	FROM
		vw_human_capital h
	JOIN 
		TPReports_Dev..vw_info_subsidiary_ccms c on h.GCO_001 = c.SubsidiaryName
	WHERE
		c.subsidiaryid = 137

It produces results that look like this:

description GCO_001 Total Percentage All
February 2010 USA Company 8 .3 24
January 2010 USA Company 7 0.5 14

Currently the query is dividing 8/24 to make the percentage .3 and 7/14 to make the percentage .5.

I need to divide using the number from the previous month. So in my example above, I need 8/14 as the calulation for percentage in February. I've heard its common in finance to divide numbers from previous months to make comparisons, and that's really all that's going on here. I have no idea how to do it though.

Help! I hope I explained this clearly. If not I'm happy to clarify.

Thanks

Craig Greenwood

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-16 : 16:19:16

So....write a query to do this

>> I need to divide using the number from the previous month

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

craigwg
Posting Yak Master

154 Posts

Posted - 2010-06-16 : 17:40:57

Your message inspired me to try with some subqueries but I'm still struggling with this. How can I make this happen?

Craig Greenwood

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 02:33:20

Excellent response Brett.

What you're looking for is a slightly modified version of a "running total" and can be achieved like this:

DECLARE @table as table (ID int, PurchaseValue int)

INSERT INTO @table Values(1,2)
INSERT INTO @table Values(2,4)
INSERT INTO @table Values(3,6)
INSERT INTO @table Values(4,8)
INSERT INTO @table Values(5,10)
INSERT INTO @table Values(6,12)
INSERT INTO @table Values(7,14);

WITH cte (ID, PurchaseValue, RowNumber) 
	AS (SELECT ID, PurchaseValue, ROW_NUMBER() OVER (ORDER BY ID) FROM @table T1)
SELECT 
	ID, 
	PurchaseValue,
	RunningTotal = CASE 
		WHEN RowNumber = 1 THEN PurchaseValue
		ELSE PurchaseValue * (SELECT PurchaseValue FROM cte AS cte2 WHERE cte2.RowNumber = cte1.Rownumber - 1)
		END
FROM cte AS cte1

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 02:35:05

Let me know if you can't get it to work for your query...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-06-17 : 03:15:12

A form of this should be in order.

3 caveats
- get the right format of the dateadd statement ( i don't use it that often, and you can do some of the finishing off yourself

)
- handle null's for h2 values (i.e no previous month)
- your output data includes a date, but your query doesn't - so i'm assuming the date is not embedded in the description.
-prefixing your tables with "dbo." would be good "performance" practice...(caching)
it would also remove the possibility of your code running against "local" user tables.

SELECT
h1.description,
h1.GCO_001,
'Total= h1.HCR_048,
'All Agents' = h1.HCR_024 + h1.HCR_025,
'Percentage' = h1.HCR_048/(h2.HCR_024 + h2.HCR_025) -- put a case statement here to handle nnll h2 values (i'e you can't divide by zero/nothing)
FROM
vw_human_capital h1
JOIN
TPReports_Dev..vw_info_subsidiary_ccms c on h1.GCO_001 = c.SubsidiaryName
LEFT JOIN
vw_human_capital h2 on h2.GCO_001 = h1.GCO_001 and h2.tabledate = dateadd(h1.tabledate,-1,month)
WHERE
c.subsidiaryid = 137

craigwg
Posting Yak Master

154 Posts

Posted - 2010-06-17 : 11:16:51

I think I got this working, so thank you to everyone. I didn't use the cte idea, but I may circle back and try that. The biggest help to me was seeing the join:

LEFT JOIN
vw_human_capital h2 on h2.GCO_001 = h1.GCO_001 and h2.tabledate = dateadd(h1.tabledate,-1,month)

I havn't worked in the case statement yet but fully understand why it needs to be there eventually. One problem I have is that production data doesn't even exist yet! I have sample data in my dev and prod environment so I'm running everything against ideas of what should be there.

But this was helpful and I learned yet another thing about SQL that I didn't know before. This forum is absolutley fantastic! Thank you all times 57(?).

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources