INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need previous month end and current month end data on one row

Need previous month end and current month end data on one row

(OP)
Can someone please guide me as to how this would be done. I'm using Crystal 2008.

I want to pull in data for the previous month-end and put it in the same row as the current month, so there will be two figure columns: one with the Month-End figures and the other will be Previous Month-End Figures. I want it to look like this:



How would I get the Prev Month-End Figures based on the Month-End dates in the first column?

RE: Need previous month end and current month end data on one row

This is pretty crude and not sure your data will lend itself to this technique, but maybe it will spark something to get you to your answer.

I'll create a temp table (table var in this case) and load in the first few months of your data, in reverse order to illustrate your data does not need to be ordered to begin with:

CODE -->

DECLARE @Figures TABLE (
	MonthEnd date
	,Account varchar(10)
	,Figure int
	,SeqNbr int
	)
INSERT INTO @Figures
VALUES
('3/31/14','64EG544',33,NULL),
('2/28/14','3285592',22,NULL),
('1/31/14','21A3233',11,NULL) 

Next, we will populate the SeqNbr column with an integer representing the order of the rows by month-end, so we can have gaps in the dates and not have to worry about those:

CODE -->

UPDATE fig
	SET SeqNbr = x.RowNbr
FROM @Figures fig
	JOIN (SELECT MonthEnd, ROW_NUMBER() OVER(ORDER BY MonthEnd) AS RowNbr FROM @Figures) x ON fig.MonthEnd = x.MonthEnd 

Now, we can join the table to itself using the SeqNbr to go back one row:

CODE -->

SELECT
	fig1.MonthEnd
	,fig1.Account
	,fig1.Figure
	,fig2.Figure AS PrevMonthEndFigure
FROM @Figures fig1
	LEFT JOIN @Figures fig2 ON fig2.SeqNbr = fig1.SeqNbr - 1
ORDER BY fig1.MonthEnd 

There are probably (certainly?) more elegant ways to achieve this, but this has worked for us in similar circumstances. The same could be accomplished by putting an identity column in the temp table and then inserting with ordered data so the identity is set in the desired order, and then join on the identity column same as done above on the SeqNbr column.

-Jim-

RE: Need previous month end and current month end data on one row

(OP)
Thank you so, so much. I'll try this.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close