If the account has cResolution_Codes_fk it means, that account was resolved. Otherwise the account is opened.

I used the following SQL:

TEXT TO lcSQLStr TEXTMERGE noshow
	SELECT cCode_Description, ;
	CAST(SUM(NVL(IIF(BETWEEN(tResolution_Date,##START_DATE##,##END_DATE##),1,0),0)) AS I) as iOriginally_Closed, ;
	CAST(SUM(NVL(IIF(not BETWEEN(tResolution_Date,##START_DATE##,##END_DATE##),1,0),0)) AS I) as iOriginally_Opened, ;
	CAST(SUM(NVL(IIF(ISNULL(cResolution_Codes_Fk) ,0,1),0)) AS I) as iClosed, ;
	CAST(SUM(NVL(IIF(ISNULL(cResolution_Codes_Fk),1,0),0)) AS I) as iOpened, ;
	CAST(COUNT(*) AS I) as iTotal ;
	FROM Trans ;
	left JOIN VisCodes ON Trans.cResolution_Codes_fk = VisCodes.cVisCodes_pk ;
	WHERE iBatch_Number = <<m.tiBatch_Number>> ;
	group BY 1
endtext
lcSQLStr = STRTRAN(m.lcSQLStr, '##START_DATE##', m.lcStart_Date)
lcSQLStr = STRTRAN(m.lcSQLStr, '##END_DATE##', m.lcEnd_Date)
=RunSQL(m.lcSQLStr, 'c_BatchStatistics')

COPY TO Batch_Statistics.XLS TYPE XLS

Now I want:

1) Properly format file in Excel, e.g. give columns meangful titles.
2) Perhaps create it as a treeview:

+ Originally Opened - 50
+ Originally Closed - 10
+ Open - 20
+ Closed - 40

When I would click on the opened accounts I would see them by status distribution, on closed - by resolution code...

That's my idea...

>Your question isn't clear because you didn't say wich fields tells when account was opended. Also, AFAIKS

iOpened = iTotal - iClosed

>
>
>>
>>I'd like to get statistics about accounts for particular batch. Some accounts were automatically closed on the day they were imported (could be various reasons - usually unsufficient balance).
>>
>>Bellow is my code:
>>

>>TEXT TO lcSQLStr TEXTMERGE noshow
>>	SELECT ;
>>	CAST(SUM(IIF(BETWEEN(tResolution_Date,##START_DATE##,##END_DATE##),1,0)) AS I) as iOriginally_Closed, ;
>>	CAST(SUM(IIF(ISNULL(cResolution_Codes_Fk) ,0,1)) AS I) as iClosed, ;
>>	CAST(SUM(IIF(ISNULL(cResolution_Codes_Fk),1,0)) AS I) as iOpened, ;
>>	CAST(COUNT(*) AS I) as iTotal ;
>>	FROM Trans WHERE iBatch_Number = <<m.tiBatch_Number>>	
>>endtext
>>lcSQLStr = STRTRAN(m.lcSQLStr, '##START_DATE##', m.lcStart_Date)
>>lcSQLStr = STRTRAN(m.lcSQLStr, '##END_DATE##', m.lcEnd_Date)
>>=RunSQL(m.lcSQLStr, 'c_BatchStatistics')

>>
>>E.g. I easily can get accounts closed on the particular day, but how can I get accounts opened on this day? Only Total - Closed?
>>
>>Any ideas?