group by by date/month and another parameter

[jozino01]

hi,
i have the following query (datasource for a report):

SELECT [REVENUE SUM BY ACCT ALL MONTHLY].ACCT_NUM, [REVENUE SUM BY ACCT ALL MONTHLY].TOT_REV, Sum([COMMISSION SUM BY ACCT ALL MONTHLY].TOT_COMMISSION) AS TOT_COMMISSION, Sum([EXPENSE SUM BY ACCT ALL MONTHLY].TOT_EXPENSE) AS TOT_EXPENSE, Sum(([REVENUE SUM BY ACCT ALL MONTHLY].[TOT_REV]-[COMMISSION SUM BY ACCT ALL MONTHLY].[TOT_COMMISSION]-[EXPENSE SUM BY ACCT ALL MONTHLY].[TOT_EXPENSE])) AS BALANCE, [COMMISSION SUM BY ACCT ALL MONTHLY].DATE
FROM [COMMISSION SUM BY ACCT ALL MONTHLY], [EXPENSE SUM BY ACCT ALL MONTHLY], [REVENUE SUM BY ACCT ALL MONTHLY]
GROUP BY [REVENUE SUM BY ACCT ALL MONTHLY].ACCT_NUM, [REVENUE SUM BY ACCT ALL MONTHLY].TOT_REV, [COMMISSION SUM BY ACCT ALL MONTHLY].DATE, [EXPENSE SUM BY ACCT ALL MONTHLY].ACCT_NUM, [COMMISSION SUM BY ACCT ALL MONTHLY].ACCT_NUM
HAVING ((([REVENUE SUM BY ACCT ALL MONTHLY].ACCT_NUM)=[EXPENSE SUM BY ACCT ALL MONTHLY].[acct_num] And ([REVENUE SUM BY ACCT ALL MONTHLY].ACCT_NUM)=[commission sum BY ACCT ALL MONTHLY].[acct_num]));

the query worked ok untill i was asked to changed it so output data would be grouped by date (monthly)
now i am getting multiple rows... is there any way how to fix it/make it work, please?

 

[r937]

select R.ACCT_NUM
     , R.TOT_REV
     , Sum(C.TOT_COMMISSION) AS TOT_COMMISSION
     , Sum(E.TOT_EXPENSE) AS TOT_EXPENSE
     , Sum((R.[TOT_REV]
           -C.[TOT_COMMISSION]
           -E.[TOT_EXPENSE])) AS BALANCE
     , [b]month(C.DATE)[/b]
  from [COMMISSION SUM BY ACCT ALL MONTHLY] as C
     , [EXPENSE SUM BY ACCT ALL MONTHLY] as E
     , [REVENUE SUM BY ACCT ALL MONTHLY] as R
 where R.ACCT_NUM 
     = E.[acct_num] 
   And R.ACCT_NUM
     = C.[acct_num]     
group 
    by R.ACCT_NUM
     , R.TOT_REV
     , [b]month(C.DATE)[/b]

r937.com | rudy.ca

 

[jozino01]

thanks, how can i change 'month(C.DATE)' so it would show month and year since data are for more years, please?

 

[r937]

select R.ACCT_NUM
     , R.TOT_REV
     , Sum(C.TOT_COMMISSION) AS TOT_COMMISSION
     , Sum(E.TOT_EXPENSE) AS TOT_EXPENSE
     , Sum((R.[TOT_REV]
           -C.[TOT_COMMISSION]
           -E.[TOT_EXPENSE])) AS BALANCE
     , [b]year(C.DATE)[/b]
     , [b]month(C.DATE)[/b]
  from [COMMISSION SUM BY ACCT ALL MONTHLY] as C
     , [EXPENSE SUM BY ACCT ALL MONTHLY] as E
     , [REVENUE SUM BY ACCT ALL MONTHLY] as R
 where R.ACCT_NUM 
     = E.[acct_num] 
   And R.ACCT_NUM
     = C.[acct_num]     
group 
    by R.ACCT_NUM
     , R.TOT_REV
     , [b]year(C.DATE)[/b]
     , [b]month(C.DATE)[/b]

r937.com | rudy.ca

 

[jozino01]

thank you, it's getting there...
but the calculated/showed values are not correct :-(

here are the correct values (without grouping by month)

ACCT_NUM TOT_REV TOT_COMMISSION TOT_EXPENSE BALANCE
1 $0.00 0 $42,830.97 -$42,830.97
2 $711,032.46 12879.178 $998,710.53 -$300,557.25
3 $0.00 0 $980.00 -$980.00
4 $0.00 0 $106,150.12 -$106,150.12
8 $1,622,217.15 30874.766 $1,577,874.80 $13,467.58
16 $0.00 0 $68,383.00 -$68,383.00
17 $0.00 0 $10,238.09 -$10,238.09
19 $1,210,177.39 32275.54 $1,217,868.86 -$39,967.01
50 $254,556.97 3926.794 $250,705.17 -$74.99
51 $219,005.51 3929.108 $214,701.40 $375.00
52 $149,090.11 2540.008 $146,550.09 $0.01
60 $2,058,839.53 27817.79 $2,031,021.74 $0.00
71 $524,611.20 102608.8 $425,038.19 -$3,035.79
77 $682,919.94 36775.28 $646,144.66 $0.00
87 $3,260,917.07 41063.568 $3,075,850.81 $144,002.70
97 $2,318,231.71 0 $2,514,957.60 -$196,725.89
98 $885,899.41 24133.054 $868,759.75 -$6,993.39
99 $2,685.00 0 $17,164.08 -$14,479.08

and is a part of output grouped by month:

ACCT_NUM TOT_REV TOT_COMMISSION TOT_EXPENSE BALANCE MYEAR MMONTH
1 $0.00 0 $42,830.97 -$42,830.97 2005 7
2 $1,211.90 26648.8 $1,997,421.06 -$1,963,474.86 2005 4
2 $1,211.90 208575.9 $5,992,263.18 -$6,019,054.08 2005 5
2 $1,211.90 49900.5000000001 $1,997,421.06 -$1,986,726.56 2005 6
2 $1,211.90 36854.2500000001 $2,996,131.59 -$2,942,093.34 2005 7
2 $3,143.39 26648.8 $1,997,421.06 -$1,866,900.36 2005 4
2 $3,143.39 208575.9 $5,992,263.18 -$5,729,330.58 2005 5
2 $3,143.39 49900.5000000001 $1,997,421.06 -$1,890,152.06 2005 6

values are much higher...

maybe i should mention that there are no always data for each acct_num and each month.