Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Other Development Tools
 Joining Tables

Author  Topic 

Andym129
Starting Member

2 Posts

Posted - 2013-05-29 : 11:09:06
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-29 : 11:29:11
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

2 Posts

Posted - 2013-05-29 : 11:39:09
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
   

- Advertisement -