Announcement

Collapse
No announcement yet.

Need help with slightly more complicated SQL statemenet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help with slightly more complicated SQL statemenet

    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

    sql statement.jpg

    Can anyone help me to filter the sum by dates

    Thanks

    Tony

  • #2
    Add a WHERE with a BETWEEN clause:

    Code:
    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.
    Last edited by adz1111; 04-16-2015, 07:44 AM.

    Comment


    • #3
      Thanks,

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

      Now works fine

      Cheers

      Tony

      Comment


      • #4
        :-) .

        Comment


        • #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

          SQL_error01.jpg

          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

          Comment


          • #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')

            Comment


            • #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

              Comment


              • #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

                Comment


                • #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

                  Comment


                  • #10
                    fiscal,

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

                    So, without commas:

                    Code:
                    {ta_debit} = sprintf('%0.2f', {ta_debit});
                    {ta_credit} = sprintf('%0.2f', {ta_credit});
                    Or, if it needs the commas (for thousands), then try:

                    Code:
                    {ta_debit} = sprintf('%0.2f', number_format({ta_debit},2));
                    {ta_credit} = sprintf('%0.2f', number_format({ta_credit},2));
                    Or something like that.

                    Comment


                    • #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

                      Comment


                      • #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.

                        Comment

                        Working...
                        X