if data missing for certain period

[jozino01]

hi
i have the following query:

(SELECT Format( R.[DATE],"mmmm yyyy",0,0) AS rdate,
Sum(R.CAD+R.[B/S_VALUE]+R.[B/G_VALUE]+R.LOGS_SCALING+R.[B/S_SCALING]) AS tot_revenue,
Sum(E.AMOUNT) AS tot_expenses

FROM REVENUE R INNER JOIN EXPENSES E
ON R.ACCT_NUM = E.ACCT_NUM

GROUP BY Format( R.[DATE],"mmmm yyyy",0,0) )

UNION ALL (SELECT "TOTALS",
Sum(R.CAD+R.[B/S_VALUE]+R.[B/G_VALUE]+R.LOGS_SCALING+R.[B/S_SCALING]),
Sum(E.AMOUNT)

FROM REVENUE R INNER JOIN EXPENSES E
ON R.ACCT_NUM = E.ACCT_NUM

GROUP BY "TOTALS" );

i need to fix it since it doesn't show the right values. the problem is that there are no data certain periods (only expenses and no revenue or no data for some acct_num)

any idea, please?

 

[Golom]

Probably simplest to deal with each case separately

(SELECT Format( R.[DATE],"mmmm yyyy",0,0) AS rdate, 
        Sum(R.CAD+R.[B/S_VALUE]+R.[B/G_VALUE]+R.LOGS_SCALING+R.[B/S_SCALING]) AS tot_revenue, 
        Sum(E.AMOUNT) AS tot_expenses

 FROM REVENUE R INNER JOIN EXPENSES E 
      ON R.ACCT_NUM = E.ACCT_NUM

 GROUP BY Format( R.[DATE],"mmmm yyyy",0,0) )

UNION ALL

(SELECT Format( E.[DATE],"mmmm yyyy",0,0), 
        0, 
        Sum(E.AMOUNT) 

 FROM REVENUE R RIGHT JOIN EXPENSES E 
      ON R.ACCT_NUM = E.ACCT_NUM

 WHERE R.ACCT_NUM IS NULL

 GROUP BY Format( E.[DATE],"mmmm yyyy",0,0) )

UNION ALL

(SELECT Format( R.[DATE],"mmmm yyyy",0,0), 
        Sum(R.CAD+R.[B/S_VALUE]+R.[B/G_VALUE]+R.LOGS_SCALING+R.[B/S_SCALING]) , 
        0 

 FROM REVENUE R LEFT JOIN EXPENSES E 
      ON R.ACCT_NUM = E.ACCT_NUM

 WHERE E.ACCT_NUM IS NULL

 GROUP BY Format( R.[DATE],"mmmm yyyy",0,0) )


UNION ALL 

(SELECT TOP 1 "TOTALS", 
        (Select Sum(R.CAD+R.[B/S_VALUE]+R.[B/G_VALUE]+R.LOGS_SCALING+R.[B/S_SCALING]) From Revenue R), 
        (Select Sum(E.AMOUNT) From Expenses E)

 FROM REVENUE );

I'm assuming that you have a date field E.[DATE] in the Expense table.

"No data for some account number" really can't be "fixed" because there's nothing to retrieve ... hence nothing that can be reported.

 

[jozino01]

I'm assuming that you have a date field E.[DATE] in the Expense table.
- yes.

"No data for some account number" really can't be "fixed" because there's nothing to retrieve ... hence nothing that can be reported.
- that's true , I am thinking about something like
if recordset = null/empty then 0 else recordset
is something like that possible in query/report?

 

[Golom]

If the SQL I provided is the source for a recordset then it will be empty (i.e. both EOF and BOF are true) only if both the revenue and expense tables are also empty. Is that a possibility in your environment?

 

[jozino01]

yes, in most cases there are transactions (revenue/expenses) only for a few acct_num, never for all.

 

[Golom]

OK. But your "definition" of account number from your posting is that it exists in either Revenue or Expenses and possibly both. You may have another table somewhere that defines all account numbers but you haven't indicated that here. If such a table exists (assume it's called "Account_Numbers" then the UNION query needs another clause of the form

(Select A.Acct_Num, 0, 0
 From Account_Numbers A
 Where Not Exists (Select * From Revenue R Where R.Acct_Num = A.Acct_Num) AND
       Not Exists (Select * From Expense E Where E.Acct_Num = A.Acct_Num))

 

[JonFer]

Assuming there is a master account table, I would create a query to get a list of records representing each account number for each revenue and expense date.

Query: AcctNumDates
Select er.[Date], a.AcctNum
From AcctNumbers a,
(Select Distinct [Date] from Revenue Union
Select Distinct [Date] from Expenses) as er

Then use this query in a Left Join to both the expense and revenue tables. Use Nz(Amount,0) to convert the missing revenue or expense to 0.


John

http://www.interautomateconsulting.com

 

[jozino01]

thanks to both of you.
yes, i have the master table of account numbers:
CODE NAME STATUS NOTE
01 KITKATLA ACTIVE
02 DANE CAMPBELL ACTIVE
03 NEW PROJECT ACTIVE
04 NEW ACTIVE
08 IGNAS (ICP) CLOSED
16 SLIAMMON WOODLOT ACTIVE
17 LEQWA ACTIVE
19 CRWL/CRIB5 ACTIVE
50 HENRY SYRJALA CLOSED
51 HENRY SYRJALA CLOSED
52 BURGER LOGGING CLOSED
60 EMBLEY CREEK CLOSED
71 BROUGHTON ISLAND 1 CLOSED
77 BROUGHTON ISLAND 2 CLOSED
87 BANKS ISLAND CLOSED STARTED IN 2002
97 KENIQWALA (CRFN/CRIB4) ACTIVE
98 APT CLOSED
99 MISCEL. ACTIVE

i also created a table of dates/months (it started in 2002)

i just don't know how to put everything together...

i will try later today according to your advices and will post the results.

 

[jozino01]

RE: JonFer
I got your query working

SELECT er.[Date], a.CODE
FROM PROJECTS AS a, [Select Distinct [Date] from Revenue1 Union
Select Distinct [Date] from Expense1]. AS er

but i failed with the left join :-(
can you give me a hint, please?

-------------------------

RE: Golom
I added this

UNION ALL (SELECT A.CODE, 0 AS Expr1, 0 AS Expr2
FROM PROJECTS AS A
WHERE (((Exists (Select * From Revenue1 R Where R.Acct_Num = A.CODE))=False) AND ((Exists (Select * From Expense1 E Where E.Acct_Num = A.CODE))=False)));

to my original query, but the results don't seem right...

rdate tot_revenue tot_expenses
TOTALS $13,900,183.45 $14,624,287.93
September 2004 $16,809,758.55 $22,090,247.20
September 2003 $6,663,771.59 $2,031,021.74
September 2002 $33,546,269.93 $39,986,060.53
October 2004 $48,286,549.53 $39,164,725.00
October 2003 $31,047,847.23 $8,124,086.96
November 2004 $8,562,162.51 $5,762,028.85
November 2003 $4,476,894.78 $3,075,850.81
November 2002 $120,966.23 $3,075,850.81
May 2005 $37,035,354.60 $34,685,718.04
May 2004 $69,689,297.47 $57,058,966.33
March 2005 $12,109,165.28 $4,871,475.44
March 2004 $1,316,385.00 $5,815,301.94
March 2003 $5,868,869.20 $501,410.34
June 2005 $4,413,515.44 $8,428,000.90
June 2004 $74,501,070.60 $47,784,194.40
June 2003 $101,732,753.26 $16,673,212.11
June 2002 $84,297,377.01 $18,455,104.86
July 2005 $12,505,205.96 $22,289,462.95
July 2004 $17,238,373.68 $37,724,364.00
July 2003 $101,598,274.01 $10,155,108.70
July 2002 $31,723,848.03 $24,606,806.48
January 2005 $1,571,773.45 $3,155,749.60
January 2003 $2,683,901.99 $9,227,552.43
February 2005 $11,732,245.36 $7,307,213.16
February 2004 $6,805,005.77 $2,632,915.51
February 2003 $5,366,666.01 $3,577,261.15
December 2004 $2,781,559.29 $7,659,193.05
December 2003 $0.00 $199,580.15
December 2002 $0.00 $210,777.92
August 2004 $99,080,215.99 $69,330,070.40
August 2002 $92,287,361.07 $58,441,165.39
April 2005 $27,311,369.60 $12,739,082.47
April 2004 $133,023,376.71 $106,274,363.86