help with a query

Question

help with a query

Christopher Klein

SSCrazy

Points: 2648
More actions
May 14, 2007 at 1:44 pm

#173694

Hi there,
I have a question on how I can calculate a value.
I have a column of month-end dates. each month end date has a value. how can I calculate the sum of one month-end minus the previous to get a difference?
the data looks as such:
2002-12-31 3191729.51
2003-01-31 3192016.75
2003-02-28 3192303.95
So I am looking for the difference between 01/31 and 12/31, 2/28 and 1/31
I figure I want to skip the MIN(date) since there is nothing to compare it to.
Any way to do it without something as ugly as a cursor?
Thanks,
Chris

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

How will you know what dates you are tyring to calculate? will they be passed in from your application?

Is this something that is the result of a process that runs on the last day of the month so you need to calculate your difference on the first? In other words is this something where you can say use substract a day from getdate() and get one of your values?

Or do you need to do this for the entire table and you won't knwo what data is there to begin with?

To help us help you read this[/url]For better help with performance problems please read this[/url]

Christopher Klein SSCrazy Points: 2648 More actions · Answer 2

basically I need it for the entire table and won't know what the starting date is going to be. On 12/31 you had 10000 and on 1/31 you had 12000. I need to know you made 2000. On 2/28 you had 9000, so I need to know you lost 3000.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

This sounds fairly straight-forward, but it would help us if you would give us your table DDL, sample data, and an example of the result set that you desire.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jason- SSCrazy Points: 2795 More actions · Answer 4

This should give you a basic example of what you are looking for.

---------------------------------------------------------------------------------

-- Just building dummy data

---------------------------------------------------------------------------------

if exists(select 1 from sysobjects where id = OBJECT_ID(N'tblTest'))

DROP TABLE tblTest

GO

CREATE TABLE tblTest (iRowId INT IDENTITY(1,1),dtEndDate DATETIME,iValue REAL)

INSERT INTO tblTest Select '2005-07-31 10:09:16.000',1000

UNION

Select '2005-08-31 10:09:16.000',10000

UNION

Select '2005-09-30 10:09:16.000',3000

UNION

Select '2005-10-31 10:09:16.000',400

UNION

Select '2005-11-30 10:09:16.000',80000

GO

---------------------------------------------------------------------------------

-- THIS IS THE WHAT APPLIES TO YOUR TABLE

---------------------------------------------------------------------------------

select datename(mm,dtEndDate)+' '+datename(yyyy,dtEndDate) BaseDate

,datename(mm,PrevDate)+' '+datename(yyyy,PrevDate) PrevDate

,iValue BaseValue

,PrevValue

,iValue - PrevValue Variance

from (

select OQ.iRowId,OQ.dtEndDate,OQ.iValue,SQ.iValue PrevValue,SQ.dtEndDate PrevDate

From tblTest OQ FULL OUTER JOIN tblTest SQ ON

datediff(mm,OQ.dtEndDate,SQ.dtEndDate) = -1

) ResultQuery

The base data looks like:

iRowId dtEndDate iValue

----------- ------------------------------------------------------ ------------------------

1 2005-07-31 10:09:16.000 1000.0

2 2005-08-31 10:09:16.000 10000.0

3 2005-09-30 10:09:16.000 3000.0

4 2005-10-31 10:09:16.000 400.0

5 2005-11-30 10:09:16.000 80000.0

(5 row(s) affected)

After the query:

BaseDate PrevDate BaseValue PrevValue Variance

------------------------------------------------------------------------------------------

July 2005 NULL 1000.0 NULL NULL

August 2005 July 2005 10000.0 1000.0 9000.0

September 2005 August 2005 3000.0 10000.0 -7000.0

October 2005 September 2005 400.0 3000.0 -2600.0

November 2005 October 2005 80000.0 400.0 79600.0

NULL November 2005 NULL 80000.0 NULL

(6 row(s) affected)

-

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

Perhaps I'm not understanding the question, but the number of days between the last day of the month and the previous last day of the month is the day of the month.

Jan 31 = 31 days since the last day of the previous month

Feb 28 = 28 days since the last day of the previous month

June 30 = 30 days since the last day of the previous month

Select day(dtEndDate)

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Christopher Klein SSCrazy Points: 2648 More actions · Answer 6

thanks Jason, that did the trick. I always forget about derrived tables and joining on yourself.

Thanks,

Chris