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

[Wondering123]

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?

 

[jjefferson]

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:

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:

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:

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-

 

[Wondering123]

Thank you so, so much. I'll try this.