Need help with slightly more complicated SQL statemenet

Applications Grid / Reports
#1

Hi All,

I wish to have a grid application that displays a trial balance from a double entry bookkeeping system. I have worked out the SQL statement to total each general ledger code, but I cannot yet work out how to filter by two dates (normally the financial year start and finish dates)

Here is the SQL statement

SELECT
g.gl_code,
g.gl_name,
FORMAT( SUM(a.ta_net*a.ta_drcr),2) total
FROM
allocations AS a
INNER JOIN
ledger_codes AS g ON a.ta_glcode = g.gl_id
GROUP BY
g.gl_code

The resultant query result is in the image

Can anyone help me to filter the sum by dates

Thanks

Tony

sql statement.jpg

#2

Add a WHERE with a BETWEEN clause:

SELECT
	g.gl_code,
	g.gl_name,
	FORMAT( SUM( a.ta_net * a.ta_drcr ) , 2 ) AS total
FROM
	allocations a
	INNER JOIN ledger_codes g
	 ON a.ta_glcode = g.gl_id
WHERE
	(date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
GROUP BY
	g.gl_code

where “date_field” is the date column - and you supply a “lower” and “upper” date.

NOTE: the upper and lower dates are inclusive.

#3

Thanks,

I had tried that but after the GROUP BY and of course it kept failing.

Now works fine

Cheers

Tony

#4

:slight_smile: .

#5

Spoke too soon.

That statement works fine in the MySQl admin program I use for testing queries, but if I try to import it into an SC grid, it fails.

I have added the fields for the body (block) of the grid, but when I run it, the following error is received on

The SQL script copoied into the SQL sectiohn in the grid application

SELECT
g.gl_code,
g.gl_name,
FORMAT( SUM(a.ta_net*a.ta_drcr),2) total
FROM
allocations AS a
INNER JOIN
ledger_codes AS g ON a.ta_glcode = g.gl_id
WHERE a.ta_date BETWEEN ‘2014-07-01’ AND ‘2015-06-30’
GROUP BY
g.gl_code

Thanks

Tony

SQL_error01.jpg

#6

You removed the parentheses around WHERE condition - does it make a difference if you put them back?

e.g.

WHERE (a.ta_date BETWEEN ‘2014-07-01’ AND ‘2015-06-30’)

#7

Thanks, but sadly no difference.

Tried it in the back end with the parentheses and it worked there OK, but just not SC.

I have also sent SC a support ticket for just in case they can sort it out, but I don’t usually get a response from them until the wee hours.

Tony

#8

Is it the intention that the grid is called, and then that grid just displays the records of a date range passed to it?

OR

Is the grid set to display ALL the records initially, and then a filter is applied (i.e. date range chosen by the user) from within that grid?

I suspect your issue may be that the Grid’s SQL should probably NOT include the BETWEEN stuff, and that you add the WHERE dynamically as required via the sc_select_where macro - possibly in conjunction with: sc_where_current and sc_where_orig

#9

Thanks adz1111,

I will investigate that shortly. SC came to the rescue, sort of, with a grid application that works. I still cannot do it by myself, so I may have to run comparisons between my and their source code.

Now I am on the next sticking point, the format of the total retrieved - it is a string, but I need to record negative amounts in a “credit” column and positives in a “debit” column. I am having difficulty with converting the data in the “onrecord” event. I have experimented with the SELECT statement, but to no avail.

SELECT
g.gl_code,
g.gl_name,
CAST( SUM(a.ta_net*a.ta_drcr) AS DECIMAL(15,2)) total
FROM
allocations a
INNER JOIN ledger_codes g ON a.ta_glcode = g.gl_id
WHERE a.ta_date BETWEEN ‘2014-07-01’ AND ‘2015-06-30’
GROUP BY g.gl_code

Look at the image of the grid and note that whilst the “total” column presents the correct data, the debit and credit columns do not, and the totals are all off as well.

Here is the code in the “onrecord” event

if ({total} <= 0){

{ta_debit} = NULL;
{ta_credit} = {total}*-1;

}else{
{ta_debit} = {total};
{ta_credit} = NULL;
}

So, how do I convert the strings to decimals? I have tried a number of suggestions from other php web sites but none seem to do it.

Thanks

Tony

tb01.jpg

#10

fiscal,

PHP’s sprintf() can be used format the strings properly, and number_format() can add “thousands” separator commas.

So, without commas:

{ta_debit} = sprintf('%0.2f', {ta_debit});
{ta_credit} = sprintf('%0.2f', {ta_credit});

Or, if it needs the commas (for thousands), then try:

{ta_debit} = sprintf('%0.2f', number_format({ta_debit},2));
{ta_credit} = sprintf('%0.2f', number_format({ta_credit},2));

Or something like that. :slight_smile:

#11

Thanks adz1111,

That, by itself, was only partially successful.

Any amounts in the 1000’s caused problems. It appeared that the thousands “,” separator truncated the result, so $2159.99 resulted in $2.00.

I found the solution bu also using the code

{total} = str_replace(’,’,’’,{total}); which basically meant that I removed the separator from the string and then applied your first suggestion.

So, the onrecord event is now -

{total} = str_replace(’,’,’’,{total});
if ({total} <= 0){

{ta_debit} = NULL;
{ta_credit} = sprintf('%0.2f',{total}*-1);

}else{
{ta_debit} = sprintf(’%0.2f’,{total});
{ta_credit} = NULL;
}

and that now works a treat, and the totaling also works - thank goodness.

I have spent a lot of time on this one issue, which is annoying, as I suppose I was expecting a simple function to do it for me, however, I suppose I have learned a little more and I should keep this snippet for future use.

Tony

#12

Glad you got it sorted.

… and yes - there’s no simple function. However, rather than keep it as snippet, make it into a function in your SC library, and then you can call it as function whenever.

The point being it was not an SC issue, but a PHP one - and one that you can use PHP to solve.

:slight_smile: