January 13, 2012 at 12:51 am
Hi SQL Gurus,
I need your help here to enhance my script below. This script here is to check whether the TB, AR or AP file is loaded into the database by organization. I had attached the output of the script here in excel.
declare @mytemp Table (
org_id varchar (7),
org_name varchar (50),
TB varchar (1),
AR varchar (1),
AP varchar (1)
)
Insert @mytemp (org_id, org_name, TB, AR, AP)
Select distinct a.organization, a.name, '', '', ''
From organizations AS a
join org_hrchy_details as b on a.organization = b.organization
join financial_datas as c on b.organization = c.organization
Where c.organization = '2806000'
or c.organization = '2806100'
or c.organization = '9006000'
or c.organization = '2000000'
--TB
UPDATE @mytemp
SET TB = 'x'
where 'BASE' = (
Select distinct data_view
From financial_datas
Where organization = org_id
and account not like '%D'
and data_view = 'BASE')
--AR
UPDATE @mytemp
SET AR = 'x'
where 'BASE' = (
Select distinct data_view
From financial_datas
Where organization = org_id
and account like '2%D'
and data_view = 'BASE')
--AP
UPDATE @mytemp
SET AP = 'x'
where 'BASE' = (
Select distinct data_view
From financial_datas
Where organization = org_id
and account like '4%D'
and data_view = 'BASE')
select org_id, org_name, TB, AR, AP
from @mytemp
By looking at the script above, I had hard coded the four organization ID in the WHERE clause (line 14 to line 17). How can I modify the script so that it will directly search all the organization ID available in Organization_table? Currently, I have one Organization_table that stores all 300+ IDs.
I had tried to substitute the four line organization ID checking to this condition:
where c.organization in (select distinct organization from Organization_table)
And I get this error...
Msg 8152, Level 16, State 14, Line 9
String or binary data would be truncated.
The statement has been terminated.
Kindly advise. Thanks!
January 13, 2012 at 1:06 am
yingchai (1/13/2012)
....By looking at the script above, I had hard coded the four organization ID in the WHERE clause (line 14 to line 17). How can I modify the script so that it will directly search all the organization ID available in Organization_table? ...
Remove the WHERE clause?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 13, 2012 at 1:18 am
Ooops sorry my mistake - you want to check that [Organization] exists in the Organization_table.
Firstly, can you try this alternative to the existing code? I don't think you need that temp table at all:
SELECT DISTINCT
org_id = a.organization,
org_name = a.name,
TB = CASE WHEN c.data_view = 'BASE' AND c.account NOT LIKE '%D' THEN 'x' END,
AR = CASE WHEN c.data_view = 'BASE' AND c.account LIKE '2%D' THEN 'x' END,
AP = CASE WHEN c.data_view = 'BASE' AND account LIKE '4%D' THEN 'x' END
From organizations AS a
join org_hrchy_details as b on a.organization = b.organization
join financial_datas as c on b.organization = c.organization
WHERE c.organization IN ('2806000','2806100','9006000','2000000')
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 13, 2012 at 1:21 am
Hi ChrisM@home,
Removing the WHERE clause does not help. It still throws the same error...
Msg 8152, Level 16, State 14, Line 9
String or binary data would be truncated.
The statement has been terminated.
January 13, 2012 at 1:27 am
yingchai (1/13/2012)
Hi ChrisM@home,Removing the WHERE clause does not help. It still throws the same error...
Msg 8152, Level 16, State 14, Line 9
String or binary data would be truncated.
The statement has been terminated.
I suspect the columns in the temp table.
Can you test the code I posted?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 13, 2012 at 2:02 am
Hi ChrisM@home,
I had tried your script and it indeed simplify a lot of things. But it shows some duplication results if an organization has TB, AR and AP loaded in. This will display 3 records. Please find your output attached.
Thanks.
January 13, 2012 at 2:19 am
Cool, that's good so far. Thanks for posting the results.
You can get rid of the "extra rows" by using a simple aggregate:
SELECT DISTINCT
org_id = a.organization,
org_name = a.name,
TB = MAX(CASE WHEN c.data_view = 'BASE' AND c.account NOT LIKE '%D' THEN 'x' END),
AR = MAX(CASE WHEN c.data_view = 'BASE' AND c.account LIKE '2%D' THEN 'x' END),
AP = MAX(CASE WHEN c.data_view = 'BASE' AND c.account LIKE '4%D' THEN 'x' END)
From organizations AS a
join org_hrchy_details as b on a.organization = b.organization
join financial_datas as c on b.organization = c.organization
WHERE c.organization IN ('2806000','2806100','9006000','2000000')
GROUP BY a.organization, a.name
I think the CASE statements can be simplified:
SELECT DISTINCT
org_id = a.organization,
org_name = a.name,
TB = MAX(CASE WHEN c.account NOT LIKE '%D' THEN 'x' END),
AR = MAX(CASE WHEN c.account LIKE '2%D' THEN 'x' END),
AP = MAX(CASE WHEN c.account LIKE '4%D' THEN 'x' END)
From organizations AS a
join org_hrchy_details as b on a.organization = b.organization
join financial_datas as c on b.organization = c.organization
WHERE c.data_view = 'BASE'
AND c.organization IN ('2806000','2806100','9006000','2000000')
GROUP BY a.organization, a.name
Then, I'd try filtering a.organization (or c.organization - I'd use the first table in the FROM list):
WHERE c.data_view = 'BASE'
AND a.organization IN (select distinct organization from Organization_table)
Are there really dupes of [organization] in Organization_table? If not, then use a join:
SELECT DISTINCT
org_id = a.organization,
org_name = a.name,
TB = MAX(CASE WHEN c.account NOT LIKE '%D' THEN 'x' END),
AR = MAX(CASE WHEN c.account LIKE '2%D' THEN 'x' END),
AP = MAX(CASE WHEN c.account LIKE '4%D' THEN 'x' END)
From organizations AS a
INNER JOIN Organization_table ot ON ot.organization = a.organization
join org_hrchy_details as b on a.organization = b.organization
join financial_datas as c on b.organization = c.organization
WHERE c.data_view = 'BASE'
GROUP BY a.organization, a.name
It probably doesn't matter if there are dupes in Organization_table because you're aggregating anyway - without using any aggregate functions.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 13, 2012 at 2:50 am
Hi ChrisM@home,
There is still one last final problem. After I apply the group by aggregate, the company "9006000 ISZL Consortium" does not appear in the result as this company does not have TB, AR or AP loaded. I'd attach your result here.
But I still want to show this result. Kindly refer to my first attachment.
Thanks.
January 13, 2012 at 3:23 am
No problem - take "c.data_view = 'BASE' " out of the WHERE clause and put it back into the CASE statements.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 13, 2012 at 7:05 am
Thanks ChrisM@home! 🙂
January 18, 2012 at 3:28 am
Hi ChrisM@home,
I am currently using this sql script below to check whether the TB, AR or AP file is loaded into the database by organization and would like to enhance it by checking whether the TB (trial balance) is balanced.
declare @submission varchar(20), @Period int, @Year int
set @submission = 'actual'
set @Period = '06'
set @Year = '2011'
use fcs
SELECT DISTINCT
org_id = a.organization,
org_name = a.name,
TB = MAX(CASE WHEN c.account NOT LIKE '%D' and c.account NOT LIKE 'CF%' THEN 'x' END),
AR = MAX(CASE WHEN c.account LIKE '2%D' and c.account NOT LIKE 'CF%' THEN 'x' END),
AP = MAX(CASE WHEN c.account LIKE '4%D' and c.account NOT LIKE 'CF%' THEN 'x' END),
CF = MAX(CASE WHEN c.account LIKE 'CF%' THEN 'x' END),
last_import = b.updated_at,
TB_Diff = SUM(c.amount)
From organizations AS a
left join financial_datas as c on a.organization = c.organization and c.submission = @submission and c.period = @Period and c.year = @Year and c.data_view = 'BASE'
left join import_dashboard as b on b.organization = c.organization and b.submission = @submission and b.period = @Period and b.year = @Year
WHERE a.organization IN (SELECT distinct organization FROM org_hrchy_details WHERE (organization not IN (SELECT DISTINCT parent FROM org_hrchy_details )))
GROUP BY a.organization, a.name, b.updated_at
order by org_id asc
From the script above, I added an extra column to calculate the total TB for each organization. Based on the script above, it is totaling the amount for TB, AR, AP and CF for each organization, which is not correct.
I had tried to put a condition inside the WHERE clause to sum up the amount for TB only (by putting the condition c.account NOT LIKE '%D' and c.account NOT LIKE 'CF%') but the result will only show organizations loaded with TB. This is not my desired output as I need to display all the organizations.
Please advise.
January 18, 2012 at 4:03 am
You will need to wrap the SUM around a conditional which selects only the rows you wish to sum, like this:
set @submission = 'actual'
set @Period = '06'
set @Year = '2011'
use fcs
SELECT DISTINCT
org_id = a.organization,
org_name = a.name,
TB = MAX(CASE WHEN c.account NOT LIKE '%D' and c.account NOT LIKE 'CF%' THEN 'x' END),
AR = MAX(CASE WHEN c.account LIKE '2%D' and c.account NOT LIKE 'CF%' THEN 'x' END),
AP = MAX(CASE WHEN c.account LIKE '4%D' and c.account NOT LIKE 'CF%' THEN 'x' END),
CF = MAX(CASE WHEN c.account LIKE 'CF%' THEN 'x' END),
last_import = b.updated_at,
--TB_Diff = SUM(c.amount)
TB_Diff = SUM(CASE WHEN c.account NOT LIKE '%D' and c.account NOT LIKE 'CF%' THEN c.amount END)
From organizations AS a
left join financial_datas as c on a.organization = c.organization and c.submission = @submission and c.period = @Period and c.year = @Year and c.data_view = 'BASE'
left join import_dashboard as b on b.organization = c.organization and b.submission = @submission and b.period = @Period and b.year = @Year
WHERE a.organization IN (SELECT distinct organization FROM org_hrchy_details WHERE (organization not IN (SELECT DISTINCT parent FROM org_hrchy_details )))
GROUP BY a.organization, a.name, b.updated_at
order by org_id asc
EDIT: Remove the DISTINCT, it confuses the issue when you already have an aggregate operating over the result - and may well skew the results.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply