Extremely complex query - group by

[cpuphantom]

Hello all, I'm trying to do something with T-SQL in a Stored Procedure, that I imagine is possible but so far have not been able to do. Hopefully someone here can offer some insight.

I have a program, that reports on user's sales totals for my office. The basic output of the query is a sorted list of each user and that user's billing sum for any time period fed into it.

Now, I am trying to make the application work with a Currency Conversion. An exchange rate is entered into a table monthly (yeah, I know it should be more often, but the bosses said no). Offices enter their sales in their local currency (say Euros for example), and I store the values in dollars by dividing it by my exchange rate. That part works great too.

The problem comes when trying to generate a one query report like I used to but display it in their local currency for a time period over a month. Say you ran it for 3 months... essentially I need the sum of month 1 * month 1's exchange + sum of month 2 * month 2's exchange and so on to give me one total of everything that office entered in their own currency.

The closest I've been able to get is this:

select
user_id,
sum(billings) * (select dollar from tbCurrencyExchange where symbol = @symbol and month = month(datestamp) and year = year(datestamp)) as sum_billings
from
tbNumbers
where
user_id = @user_id and
datestamp between @startdate and @enddate
group by
user_id,
datestamp

It gives me almost what I want. Something like this:

user_id sum_fee
1 0
1 0
1 10000
1 15000
1 0
1 5000

But what I really want to be able to do is just get this:

user_id sum_fee
1 30000

One sum for the entire time period. Entering "datestamp" in the group by is what's breaking it up by datestamp, but if I don't put it there it tells me I have to since I used it up before the "From"

Is it possible at all to do a sum of the sum??

Thanks for your help in advanced... sorry this is so long, but its a complicated problem and hard to explain in brief.

 

[cpuphantom]

And I should point out too... that this is a simplified version of the query. While it shows only one person being requested... what I really want to do is have it where office_id = @office_id so the results show one line for each person in an office with a total for that person for the whole time period.

 

[r937]

something like this ...

select N.user_id
     , sum(N.billings) 
        * CE.dollar  as sum_billings
  from tbNumbers as N
inner
  join tbCurrencyExchange as CE
 where N.user_id = @user_id 
   and N.datestamp between @startdate 
                       and @enddate
   and CE.symbol = @symbol 
   and CE.month = month(N.datestamp) 
   and CE.year = year(N.datestamp)
group 
    by N.user_id
     , CE.dollar

rudy

http://r937.com/

SQL Consulting

 

[cpuphantom]

Thanks, I tried that.

First I got a "Incorrect syntax near the keyword 'where'" error, so I changed your code to use a simple join. I don't know if that would make the difference but it looked like this:

select 
   N.user_id,
   sum(N.billings) * CE.dollar as sum_billings
from 
   tbNumbers as N,
   tbCurrencyExchange as CE
where 
   N.user_id = @user_id 
   and N.datestamp between @startdate 
                       and @enddate
   and CE.symbol = @symbol 
   and CE.month = month(N.datestamp) 
   and CE.year = year(N.datestamp)
group 
    by N.user_id, 
   CE.dollar

and I got basically the same thing as I did before, except only rows with a number in sum_billings, but still four rows and not one total.

Thanks! Maybe removing the inner join is what messed me up. If so, what do I need to fix to get rid of the error. I'm using MS SQL 7 btw.

 

[r937]

no, the inner join was not the source of your problem

problem was a brain cramp on my part

try this:

select N.user_id
     , sum(N.billings 
          * CE.dollar)  as sum_billings
  from tbNumbers as N
inner
  join tbCurrencyExchange as CE
 where N.user_id = @user_id 
   and N.datestamp between @startdate 
                       and @enddate
   and CE.symbol = @symbol 
   and CE.month = month(N.datestamp) 
   and CE.year = year(N.datestamp)
group 
    by N.user_id

rudy

http://r937.com/

SQL Consulting

 

[cpuphantom]

You sir, are my hero.

Seriously, I asked everyone I knew and no one could give me that answer. Thank you so much!