SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 Joining Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Andym129
Starting Member

USA
2 Posts

Posted - 05/29/2013 :  11:09:06  Show Profile  Reply with Quote
I don't know if this is the proper place, but I saw a posting for Logixml here and thought maybe I could get some assistance. At my job, my colleague and I are using Logixml (now Logi Analytics) as our dashboard for work. We are attempting to divide two tables. This is what we have thus far: (before the code this is the explanation from him as to what he's looking for--he posted this to Reddit about a week ago and now is trying to get me to find others who might be able to assist:

I need to have the quotient grouped by another another column, cell_extention. Is there anyway to do this? I've pasted my code below. Any help you could provide would be appreciated.

Select a.Paid / b.Owing From (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.de_transaction.tr_to_agency) As Paid From d00_collect.debtor Inner Join d00_collect.de_transaction On d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid Where d00_collect.debtor.de_cell_extension <> 'TEST' And d00_collect.de_transaction.tr_type <> '196' And d00_collect.de_transaction.tr_type <> '301' And d00_collect.de_transaction.tr_type <> '499' Group By d00_collect.debtor.de_cell_extension) a, (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.debtor.de_principal + d00_collect.debtor.de_interest + d00_collect.debtor.de_misc) As Owing From d00_collect.debtor Where d00_collect.debtor.de_principal <> 0 Group By d00_collect.debtor.de_cell_extension) b



I appreciate anyone that might be able to assist. We have been spinning over this for awhile and if we can't get this, this dashboard might not work to what we need. Thank you again.

Andrew

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 05/29/2013 :  11:29:11  Show Profile  Reply with Quote
quote:
Originally posted by Andym129

I don't know if this is the proper place, but I saw a posting for Logixml here and thought maybe I could get some assistance. At my job, my colleague and I are using Logixml (now Logi Analytics) as our dashboard for work. We are attempting to divide two tables. This is what we have thus far: (before the code this is the explanation from him as to what he's looking for--he posted this to Reddit about a week ago and now is trying to get me to find others who might be able to assist:

I need to have the quotient grouped by another another column, cell_extention. Is there anyway to do this? I've pasted my code below. Any help you could provide would be appreciated.

Select a.Paid / b.Owing From (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.de_transaction.tr_to_agency) As Paid From d00_collect.debtor Inner Join d00_collect.de_transaction On d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid Where d00_collect.debtor.de_cell_extension <> 'TEST' And d00_collect.de_transaction.tr_type <> '196' And d00_collect.de_transaction.tr_type <> '301' And d00_collect.de_transaction.tr_type <> '499' Group By d00_collect.debtor.de_cell_extension) a, (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.debtor.de_principal + d00_collect.debtor.de_interest + d00_collect.debtor.de_misc) As Owing From d00_collect.debtor Where d00_collect.debtor.de_principal <> 0 Group By d00_collect.debtor.de_cell_extension) b



I appreciate anyone that might be able to assist. We have been spinning over this for awhile and if we can't get this, this dashboard might not work to what we need. Thank you again.

Andrew

Which table has the column cell_extension? Is it the same as d00_collect.debtor.de_cell_extension? If it is, try something like shown below. What I am showing below is T-SQL syntax - don't know whether that would work in Logixml:
SELECT  a.debtor.de_cell_extension ,a.Paid / b.Owing
FROM    ( SELECT    d00_collect.debtor.de_cell_extension ,
                    SUM(d00_collect.de_transaction.tr_to_agency) AS Paid
          FROM      d00_collect.debtor
                    INNER JOIN d00_collect.de_transaction ON d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid
          WHERE     d00_collect.debtor.de_cell_extension <> 'TEST'
                    AND d00_collect.de_transaction.tr_type <> '196'
                    AND d00_collect.de_transaction.tr_type <> '301'
                    AND d00_collect.de_transaction.tr_type <> '499'
          GROUP BY  d00_collect.debtor.de_cell_extension
        ) a  INNER JOIN
        ( SELECT    d00_collect.debtor.de_cell_extension ,
                    SUM(d00_collect.debtor.de_principal
                        + d00_collect.debtor.de_interest
                        + d00_collect.debtor.de_misc) AS Owing
          FROM      d00_collect.debtor
          WHERE     d00_collect.debtor.de_principal <> 0
          GROUP BY  d00_collect.debtor.de_cell_extension
        ) b ON a.debtor.de_cell_extension = b.debtor.de_cell_extension
Go to Top of Page

Andym129
Starting Member

USA
2 Posts

Posted - 05/29/2013 :  11:39:09  Show Profile  Reply with Quote
quote:
Originally posted by James K

quote:
Originally posted by Andym129

I don't know if this is the proper place, but I saw a posting for Logixml here and thought maybe I could get some assistance. At my job, my colleague and I are using Logixml (now Logi Analytics) as our dashboard for work. We are attempting to divide two tables. This is what we have thus far: (before the code this is the explanation from him as to what he's looking for--he posted this to Reddit about a week ago and now is trying to get me to find others who might be able to assist:

I need to have the quotient grouped by another another column, cell_extention. Is there anyway to do this? I've pasted my code below. Any help you could provide would be appreciated.

Select a.Paid / b.Owing From (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.de_transaction.tr_to_agency) As Paid From d00_collect.debtor Inner Join d00_collect.de_transaction On d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid Where d00_collect.debtor.de_cell_extension <> 'TEST' And d00_collect.de_transaction.tr_type <> '196' And d00_collect.de_transaction.tr_type <> '301' And d00_collect.de_transaction.tr_type <> '499' Group By d00_collect.debtor.de_cell_extension) a, (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.debtor.de_principal + d00_collect.debtor.de_interest + d00_collect.debtor.de_misc) As Owing From d00_collect.debtor Where d00_collect.debtor.de_principal <> 0 Group By d00_collect.debtor.de_cell_extension) b



I appreciate anyone that might be able to assist. We have been spinning over this for awhile and if we can't get this, this dashboard might not work to what we need. Thank you again.

Andrew

Which table has the column cell_extension? Is it the same as d00_collect.debtor.de_cell_extension? If it is, try something like shown below. What I am showing below is T-SQL syntax - don't know whether that would work in Logixml:
SELECT  a.debtor.de_cell_extension ,a.Paid / b.Owing
FROM    ( SELECT    d00_collect.debtor.de_cell_extension ,
                    SUM(d00_collect.de_transaction.tr_to_agency) AS Paid
          FROM      d00_collect.debtor
                    INNER JOIN d00_collect.de_transaction ON d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid
          WHERE     d00_collect.debtor.de_cell_extension <> 'TEST'
                    AND d00_collect.de_transaction.tr_type <> '196'
                    AND d00_collect.de_transaction.tr_type <> '301'
                    AND d00_collect.de_transaction.tr_type <> '499'
          GROUP BY  d00_collect.debtor.de_cell_extension
        ) a  INNER JOIN
        ( SELECT    d00_collect.debtor.de_cell_extension ,
                    SUM(d00_collect.debtor.de_principal
                        + d00_collect.debtor.de_interest
                        + d00_collect.debtor.de_misc) AS Owing
          FROM      d00_collect.debtor
          WHERE     d00_collect.debtor.de_principal <> 0
          GROUP BY  d00_collect.debtor.de_cell_extension
        ) b ON a.debtor.de_cell_extension = b.debtor.de_cell_extension




Yes I believe so. I'm shipping this off to my colleague for him to input into the program.

Thank you for the assistance... I'll report back once I know if it works or not.

Andrew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000