How to split a field into two fields

[fdtoo]

I have the following fields in table A:

GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI
===============================================
1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005
===============================================
1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006
===============================================
1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006
===============================================
1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006

The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance
===============================================
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000


The formula for the above calculated fields are as below:

Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit - Total Debit in Period 01
Balance = Total of Net Change + Opening Bal

Guys, hope someone out there can help me with the sql command for the above report?

[nosepicker]

There are a number of unclear issues here. Why is the opening balance 8,000 instead of -8,000? Why is the ending balance 6,000 instead of -16,000? Why is the net change 8,000 instead of -8,000? Is an Opening Balance always going to be the last entry of the previous year, or can it be the last entry of the previous period? Is an ending balance going to be calculated at the end of the data, or at the end of each period?

[fdtoo]

Hi Nosepicker,

First, i would like to apologised for the error on the above report, you
are right in all sense, below is the actual output in which it should be:

Period | Date | GL_Name_VC | Debit | Credit | Net Change | Balance
================================================== ===
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | -8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | -8,000 |-16,000

The Opening Balance is actually the sum of all previous entry from previous period regardless of which year.The ending balance is going to be calculated at the end of each period?

[nosepicker]

Well, you have a lot of unusual requirements, so the following query is quite messy, but I think it'll give you what you want:

DECLARE @current_period char(2), @current_year int, @prev_period char(2), @prev_year int 

SET @current_period = '01'
SET @current_year = 2006

SET @prev_period = CASE WHEN @current_period <> '01' THEN RIGHT('0' + CONVERT(varchar, CONVERT(int, @current_period) -1), 2) ELSE '12' END 
SET @prev_year = CASE WHEN @prev_period <> '12' THEN @current_year ELSE @current_year -1 END 


SELECT 
@current_period AS Period, 
CASE WHEN C.[date] IS NULL THEN CONVERT(varchar, A.[Date], 103) 
	ELSE CONVERT(varchar, CONVERT(datetime, CONVERT(varchar, @current_year) + @current_period + '01'), 103) END AS [Date], 
CASE WHEN C.[date] IS NULL THEN A.GL_Name_VC 
	ELSE 'Opening Bal' END AS GL_Name_VC, 
CASE WHEN C.[date] IS NULL THEN 
	CASE WHEN A.Amount >= 0 THEN A.Amount ELSE 0 END 
	ELSE 0 END AS Debit, 
CASE WHEN C.[date] IS NULL THEN 
	CASE WHEN A.Amount < 0 THEN -A.Amount ELSE 0 END 
	ELSE 0 END AS Credit, 
CASE WHEN B.MaxDate IS NOT NULL THEN B.SumAmount 
	ELSE 0 END AS [Net Change], 
CASE WHEN C.[date] IS NULL AND B.MaxDate IS NULL THEN 0 
	WHEN C.[date] IS NOT NULL THEN C.Amount 
	ELSE B.SumAmount + D.Amount END AS Balance 
FROM #TableA AS A 
LEFT JOIN (SELECT Period_TI, MAX([Date]) AS MaxDate, SUM(Amount) AS SumAmount FROM #TableA GROUP BY Period_TI) AS B 
ON A.Period_TI = B.Period_TI 
	AND A.[Date] = B.MaxDate 
	AND A.Year_SI = @current_year 
	AND A.Period_TI = @current_period 
LEFT JOIN (SELECT Year_SI, Period_TI, [date], Amount FROM #TableA WHERE Year_SI = @prev_year AND Period_TI = @prev_period 
		AND [date] = (SELECT MAX([date]) FROM #TableA WHERE Year_SI = @prev_year AND Period_TI = @prev_period)) AS C 
ON A.Year_SI = C.Year_SI AND A.Period_TI = C.Period_TI 
LEFT JOIN (SELECT @current_year AS Year_SI, @current_period AS Period_TI, [date], Amount FROM #TableA WHERE Year_SI = @prev_year AND Period_TI = @prev_period 
		AND [date] = (SELECT MAX([date]) FROM #TableA WHERE Year_SI = @prev_year AND Period_TI = @prev_period)) AS D 
ON A.Year_SI = D.Year_SI AND A.Period_TI = D.Period_TI

[fdtoo]

Hi Nosepicker,

You are a genius! It works perfectly.Many thanks again,
cya!