Relationship to older record in same table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Relationship to older record in same table

Author

Topic

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-24 : 05:55:36

Hi all,

I really want to calculate the difference between months by relating the current record to the same record a month ago. This is put very simply as the actual reason for doing this is a little more complicated.

Here's my initial thoughts of how to do it but it takes forever and doesn't seem to work, can you please let me know what I've done wrong on this?

Thank you


SELECT	T1.YearNum, 
		T1.MonthNum, 
		sum(T1.Revenue) AS Revenue, 
		sum(T1.Revenue)-sum(T2.Revenue) AS Demand
FROM	T1
	LEFT OUTER JOIN
		T1 AS T2	ON
		T1.YearNum = T2.YearNum		AND
		T1.MonthNum = T2.MonthNum
GROUP BY T1.YearNum, T1.MonthNum
ORDER BY T1 YearNum, T1, MonthNum

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-24 : 05:56:14

oops, I meant:


SELECT	T1.YearNum, 
		T1.MonthNum, 
		sum(T1.Revenue) AS Revenue, 
		sum(T1.Revenue)-sum(T2.Revenue) AS Demand
FROM	T1
	LEFT OUTER JOIN
		T1 AS T2	ON
		T1.YearNum = T2.YearNum		AND
		T1.MonthNum -1 = T2.MonthNum
GROUP BY T1.YearNum, T1.MonthNum
ORDER BY T1 YearNum, T1, MonthNum

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-24 : 06:58:44

what indices have you got on T1?

indices => speed
no indices = > no speed

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-24 : 07:51:46

If you mean what type of numbers they are, the MonthNum is a number but, because it is calculated from a field called TimeDate, it is probably stored as varchar. What do you mean by speed or no speed?

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-24 : 09:10:11

If you don't know what indices are you need to re-read your SQL manual.
Indices control record uniqueness and also how fast you can get data out of the database.

Do you know how to post the execution plan? If so, do so and others (and me) may be able to assist.

As a comment....Why should a number be stored as a VarChar? Data should be stored in datatypes most suitable for their normal use. There are plenty of number datatypes available...chosing Varchar would not be in my top 10.

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-24 : 10:59:10

I agree with you, it's just that I believed that when you do a calculation from a date to work out the month, the resulting field is varchar. But, after checking, the field is numerical 'int' with null decimal places - sorry for confusion.

As for unique identifiers, on this table there is a primary key called TS_ID. As I previously explained, the example I posted here was a simple version of what I was trying to achieve without adding the complexities of other governing factors. I thought that it would be enough to get an answer from and then I could build the rest around it.

The matching criterias other than the YearNum and MonthNum-1 are:

CompanyNumber: examples: 1,2,3,4,5,6,7,8,9....
and
ChargeCategory: examples: Creative, Design Production, Digital Media, etc

Sorry if I hadn't made myself clear

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-24 : 11:15:15

for real speed you need an index on the following fields....combined
YearNum & MonthNum

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-24 : 12:01:38

Interesting, so, when I've going through on the original Data import and taken the TimeDate field and created one called MonthNum and one called YearNum, are you saying that if I did one where it was month(TimeDate) + '-' + year(TimeDate) that it would be quicker than keeping them separate? Also, if I have all the records summarised by month and year, how would I recreate the value for month minus one? so that 11/2009 would become 10/2009 for the purpose of finding out the amount of hours in the previous month?

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-24 : 12:26:22

He's saying that if you had a covering index that included those two columns (yearNum and MonthNum) then because your JOIN uses the condition


T1.YearNum = T2.YearNum
AND T1.MonthNum = T2.MonthNum

Then the query plan would probably do an index seek to find matching rows which (on a bigger dataset) would be a lot quicker than the index scan it (probably) is doing now.

You wouldn't change the JOIN condition but you would have to use a covering index as you are referencing two columns.

hope that helps.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-25 : 03:58:45

Thanks for your help on that - does it mean that the actual query is correct then? Just that I need to use a covering index?

Kind Regards,

Matt

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-25 : 04:01:07

Can you post some sample data and desired results?

My gut instinct is that you don't need that LEFT JOIN as there will always be at least one match for each row (with itself) I don't know if that will skew the results though.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-25 : 05:52:49

Thanks Charlie,

Let's say I have a view that summarises the sales per month

Company: AVIVA York
YearNum: 2009
MonthNum: 4
Revenue: £3,598.50

Company: AVIVA York
YearNum: 2009
MonthNum: 5
Revenue: £2,498.50

I want a report that will show

Company: AVIVA York
YearNum: 2009
MonthNum: 5
Revenue: £2,498.50
Demand: -£1,100.50

The demand is month 5 revenue minus month 4 revenue showing a loss of £1,100.50. The relationship would be based upon Company, YearNum, MonthNum = MonthNum-1 to get this.

Thanks

Matt

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-11-25 : 06:06:28

If you don't know what indices

not everyone would, but most people know what indexes are.

More common term i'd say, so unless you use both terms i'd not advise anyone to go back to a manual really

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-25 : 06:34:11

Indexes, yes, I know about indexes, that information is not helping me with my query

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-25 : 06:40:51

It's the statement I need, not worried about performance at this point - the table will be regularly updated and rarely reported on using this statement. I would rather have performance on updating records rather than reporting on the monthnum and yearnum

thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-25 : 07:09:54

There's probably a much better way to do this using the raw data rather than your view.

But does this provide the right answers?


SET STATISTICS IO ON
SET STATISTICS TIME ON
-- Method 1
DECLARE @view TABlE (
	[monthNo] INT
	, [yearNo] INT 
	, [revenue] MONEY
	)
INSERT @view ([monthNo], [yearNo], [revenue])
      SELECT 5, 2009, 4567.30
UNION SELECT 4, 2009, 2043.35
UNION SELECT 1, 2009, 1000.00
UNION SELECT 12, 2008, 6577.89

SELECT
	v.[yearNo]
	, v.[monthNo]
	, v.[revenue]
	, v.[revenue] - v2.[revenue] AS [Change in Revenue]
FROM
	@view v

	LEFT JOIN @view v2 ON
		(v2.[monthNo] = v.[monthNo] - 1 AND v2.[yearNo] = v.[yearNo])
		OR
		(v2.[monthNo] = 12 AND v.[monthNo] = 1 ANd v.[yearNo] = v2.[yearNo] + 1)
GO

-- Method 2 (using datetimes)
DECLARE @view TABlE (
	[monthNo] INT
	, [yearNo] INT 
	, [revenue] MONEY
	)
INSERT @view ([monthNo], [yearNo], [revenue])
      SELECT 5, 2009, 4567.30
UNION SELECT 4, 2009, 2043.35
UNION SELECT 1, 2009, 1000.00
UNION SELECT 12, 2008, 6577.89

SELECT
	v.[yearNo]
	, v.[monthNo]
	, v.[revenue]
	, v.[revenue] - v2.[revenue] AS [Change in Revenue]
FROM
	@view v

	LEFT JOIN @view v2 ON
		DATEDIFF(MONTH
			, CAST(CAST(v2.[yearNo] AS CHAR(4)) + RIGHT('00' + CAST(v2.[monthNo] AS VARCHAR(2)), 2) + '01' AS DATETIME)
			, CAST(CAST(v.[yearNo] AS CHAR(4)) + RIGHT('00' + CAST(v.[monthNo] AS VARCHAR(2)), 2) + '01' AS DATETIME)
			)
			= 1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-25 : 08:06:18

Charlie, that's fantastic - exactly correct! Both of them. I would probably do an ISNULL value of the full amount if there was none the previous year because it would be a 100% increase in revenue on a particular thing.

That's great. I like the way it also has month 12 to look back at month 1 of the previous year, very smart thinking because, they are only number values that we are using.

I'm trying to work out how it will work with my existing table, I take it I replace references to @view v with dbo.FM_Timesheets table and not include the INSERT lines? Also, I would include additional LEFT JOIN criterias of the companyName and ChargeCode which will then give me the demand of particular services per company for each month and year.

Thanks again

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-25 : 08:35:25

Obviously the @view table and associated inserts are just to get some sample data in there.

Yes -- adjust the FROM portion to suit.

I'm sure though that if you post the actual data you have (with the original dates) then here will probably be a really fast efficient way to do this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources