Change query issues

I am trying to take this statement:

Select Total = SUM(gt)
FROM
(Select Distinct CustomerName,
CustomerNumber,
FeeGoal_AZ AS FG,
FeeSchedule,
(cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt
from DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule
) as dTable

and change up (cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt

I need that part to do this instead there: (PostedAmount + Sum of all PDC where the PDC in this month + Sum all CC where CC in this month) / FeeSchedule) * 100 as gt

Here is my failed attempt:

DECLARE @.today DATETIME,
@.datefirst datetime

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

BEGIN
Select Total = SUM(gt)
FROM
(Select Distinct CustomerName,
CustomerNumber,
FeeGoal_AZ AS FG,
FeeSchedule,
PostedAmount
+ SUM(select PCD from dcr where EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dc.DateEntered IS NOT NULL)
+ SUM(select CC from dcr where EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dc.DateEntered IS NOT NULL)
* FeeSchedule) * 100 AS gt
from DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule
) as dTable

Once again your problem is that you cannot do a subquery inside a select list, ever. You can use a subquery as a derived table, so long as that subquery is a single sql statement, and then join to that. I am not fully sure what you are trying to do here. First of all, the above query can be simplified somewhat, as I was not reading the SQL thoroughly. The top statement can be simplified to:

Select SUM((cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 )
FROM DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule

As for the rest of the query, you need to explain exactly you are trying to do here. You can probably join your subqueries into the overall query, and sum the PDC column by joining to the DCR column in the from clause, and adding the where conditions to the end of the query. Note that you can join to mulpiple copies of the same table by using different aliases. Ypu may also be able to use the MONTH function to avoid all that complex date calculation logic. Check out this link and see if there is an easier way to do the same thing:

http://msdn2.microsoft.com/en-us/library/ms186724.aspx

Anyway, should be something like:

DECLARE @.today DATETIME,
@.datefirst datetime

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

Select Distinct dcr.CustomerName,
dcr.CustomerNumber,
dcr.FeeGoal_AZ AS FG,
dcr.FeeSchedule,
(dcr.PostedAmount + SUM(dcr2.CC) + SUM(dcr3.CC)) / dcr.FeeSchedule) * 100
from DCR as dcr
join DCR as dcr2
on dcr2.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)
AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND dcr2.DateEntered IS NOT NULL
-- enter your own code in this line -- AND dcr2.PrimparyKeyField(s) = dcr.PrimparyKeyField(s)
join DCR as dcr3
on dcr3.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)
AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND dc3.DateEntered IS NOT NULL)
WHERE dcr.branch='00002'
group by
dcr.CustomerName,
dcr.CustomerNumber,
dcr.FeeGoal_AZ,
dcr.FeeSchedule

You will probably have to rewrite that slightly as I am guessing at your database schema. You also don't need to have the same condition in each join as they are all joining to the same table. So for example having dcr.DateEntered IS NOT NULL once is sufficient. You will also have to join the three copies of the DCR table together on their primary keys, as I do not know what they are.

HTH

Remember, as with the CASE statement problem a few days ago, you cannot have a subquery within the column list of a select statement. You can only have a single subquery as a derived table in the from clause where you join to it.

|||

I'm lost, here is what I put

Select Distinct dcr.CustomerName,

dcr.CustomerNumber,

dcr.FeeGoal_AZ AS FG,

dcr.FeeSchedule,

((dcr.PostedAmount + SUM(dcr2.CC) + SUM(dcr3.CC)) / dcr.FeeSchedule) * 100

from DCR as dcr

join DCR as dcr2 on dcr2.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)

AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND dcr2.DateEntered IS NOT NULL

AND dcr2.CustoemrNumber = dcr.CustomerNumber

join DCR as dcr3 on dcr3.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)

AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND dc3.DateEntered IS NOT NULL)

WHERE dcr.branch='00002'

group by dcr.CustomerName,

dcr.CustomerNumber,

dcr.FeeGoal_AZ,

dcr.FeeSchedule

|||

So what is the problem with that code? You didn't say. You haven't joined on the primary key of the table, which you will need to do otherwise you will get a cartesian product. So you will need a

join dcr2 on dcr.dcrID = dcr2.drcid

or something like that. You may also need to have outer joins.

What subsets are you trying to get from DCR? I suggest you define clearly what they are, write three separate queries to get them, test them and then combine them into one statement. You may not need three copies of one table either, you may be able to do this with one copy. If you want three different calculations performing on the same subset of data, then one table will suffice. However, if you want to intersect three different subsets of the same data, then three table copies will be required.

Please add more explanation, I often have trouble determining what exactly your problems are, I need more information to solve.

|||

I don't know, that's why I need some hand holding here ! You probably have difficulty because this isn't usual requests for an IT programmer do code this kind of crap.

OK, let me try to explain from scratch.

I have a table called DCR

I am basically having to recreate this complicated Excel doc into SQL Server and Reporting Services to automate it.

Right now, I am not able to create the expression I need in my Reporting Services 2005 footer field which simply sums up that group column so I have to resort to doign the sum in SQL then bring in the sum using a dataset in my Report....which to me is totally stupid, overdone.

Anyway, For this total (subtotal of my column), I have to calculate the grand total of the following equation for each customer record. This is a transaction table therefore each customer record will have several entries.

First, let me give you some business formulas.

In-House1 = PostedAmount + Sum of PDC where EnteredDate between '2/1/2006' and '2/28/2006' + Sum of CC where EnteredDate between '2/1/2006' and '2/28/2006' + Sum of PDC where EnteredDate not between '2/1/2006' and '2/28/2006' + Sum of CC where EnteredDate not between '2/1/2006' and '2/28/2006'

In-House2 = InHouse1 * FeeSchedule

Now, my table is like this

DCR Table fields:

TransID (not needed in my query, just for fyi)

CustomerNumber

PDC (number)

CC (number)

Entered Date

FeeSchedule (int)

FeeGoal (bigint) - is updated from user entry using an asp.net form, i then update each customer trans record in dcr with the same fee goal that the user entered

Example data

--

TransID CustomerNumber PDC CC EnteredDate FeeGoal FeeSchedule

1 200 100 250 '2/2/2006' 2233 50

2 200 300 200 '2/5/2006' 2233 50

3 400 233 444 '2/6/2006' 1000 30

4 400 3333 332 '2/6/2006' 1000 30

5 400 2233 300 '2/18/2006' 1000 30

So now, if you figure out InHouse1's forumula using the fields from this table

then I want to take InHouse1 for each company and perform InHouse2's formula

this is as best as I can describe it...once again, their trying to shove an excel into a dtabase which I don't believe is normal, at least not in my 8 years of reporting, we wouldn't even touch that and have once and know what pains you have to go through...that's why they make Excel!

|||

Thank you, that makes things a lot clearer.

After a quick look at this, it would appear this problem needs a different approach. I will describe it now, I don;t have time to write it for you. Please try doing it this way and let me know if
it works, I will be online later to check. OK, instead of doing one big query, use a union, with a different query for each subtotal. As you are doing a UNION ALL, you will need to ensure
that each column returned from each unioned query has the same number of columns, with the same data type and precision. The names can differ. Now place all of that UNION ALL into a derived table
(just surround it all in parentheses and refer to the whole thing as if it were a table. The column names out the UNIONEd columns will assume whatever name you specified in the first of your UNIONED query.
Do you follow so far. Then in the outer query, SUM up the subtotal column as one column, and perform your second in house formula in the second statement. To to this, you will have to return the FeeSchedule column
in the UNION as well, and then repeat the formula from the previous column, multiplying it by that amount. It will look something like

SELECT SUM(sub.a), SUM(sub.a * sub.FeeSchedule)
FROM
(SELECT xyz....
FROM DCR
UNION ALL
SELECT xyz....
FROM DCR
SELECT xyz....
FROM DCR) as sub

You will have to fill in the details. Let me know how you get on....

|||

Ok, try this:

select SUM(subTotal) * FeeSchedule
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL)
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL)
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
)
group by FeeSchedule

You may want to change the date formulas, I just hard coded the values based on your description. Do you need these totals separately, or do you just want the end number per customer? If you want to know the values individually before summing them up, then you will have to resort to using multiple copies of the table, joining by customer number and some other field, to make sure like is joining like, and you are getting a semi cartesian join. I am confused by your date ranges. They are mutually exclusive. If you just want the grandtotal of both, don't bother to add the date filter clauses, adding the value for all the days in february to the value for all the days not in february is the same as saying add the value for all days in the table together! Do you see my point. It is like saying add 3, 4 and 5. Now add all the other numbers, 1 to 10 that are not 3,4 and 5. It is the same as saying add all the numbers from 1 to 10, yet the latter is a lot simpler to perform.

Is the FeeSchedule the same value for each customer? If it is, the above query will work. If it differs for the same customer, then I would assume you would want the total of all FeeSchedules for each customer as well, before multiplying by the SUM(PDC) + SUM(CC). If that is the case, remove FeeSchedule from the group by's in the UNION expression, and add a SUM in front of the colum to get the SUM of FeeSchedules instead in the UNION sub query\derived table (both terms are interchangeable I think).

I hope I explained that well, it is quite a complex query, I am not surprised it is causing some issues. Let me know if this solved the issue or not,

Simon

|||

Oops, slight mistake in above query. I am only human after all.

select SUM(subTotal) * FeeSchedule, CustomerNumber
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL)
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL)
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(CC)
)
group by FeeSchedule, CustomerNumber

I forgot you needed the grand total by customer.

HTH

|||Shughes , I really appreciate your support. I have never used unions before, never had the need until now. I'll give it a try, thanks for really taking the time to show me this! You are saving my life here, I'm still trying to figure this thing out after an entire day!|||

I am getting:

Incorrect syntax near the keyword 'where'.

Msg 156, Level 15, State 1, Procedure SSRS_Get_Tot_InHouse2, Line 29

Incorrect syntax near the keyword 'where'.

let me see if I can fix it...

|||My bad, I added an extra closing parenthesis at the end of the where clause (after the NOT NULL). Sorry, but unable to test my end as do not have database. Try this:

select SUM(subTotal) * FeeSchedule, CustomerNumber
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(CC)
)
group by FeeSchedule, CustomerNumber

That should do it!
|||

the where clause needs to come before the group by:

select SUM(subTotal) * FeeSchedule, CustomerNumber

from

(

selectSUM(PDC) as subTotal,

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate between '2/1/2006' and '2/28/2006')

or (EnteredDate not between '2/1/2006' and '2/28/2006'))

and DateEntered IS NOT NULL

group by CustomerNumber, FeeSchedule

union all

selectSUM(CC),

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')

or (not between '2/1/2006' and '2/28/2006'))

and DateEnteredIS NOT NULL

group by CustomerNumber, FeeSchedule

)

group by FeeSchedule, CustomerNumber

Now I'm getting one error but can't see it:

Msg 156, Level 15, State 1, Line 18

Incorrect syntax near the keyword 'where'.

|||

in the end I should end up with only one sum, one result. It looks like your query will end up with

Customer tot

Customer tot

...

but that/s now what I want...anyway, still trying to fix the last error.

|||Thar should work. Are you still getting an error?|||

select SUM(subTotal) * FeeSchedule, CustomerNumber

from

(

select SUM(PDC) as subTotal,

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate between '2/1/2006' and '2/28/2006')

or (EnteredDate not between '2/1/2006' and '2/28/2006'))

and DateEntered IS NOT NULL

group byCustomerNumber,

FeeSchedule

union all

select SUM(CC),

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')

or (not between '2/1/2006' and '2/28/2006'))

and DateEnteredIS NOT NULL

group byCustomerNumber,

FeeSchedule

)

group byFeeSchedule,

CustomerNumber

same error but I don't see anything wrong:

Msg 156, Level 15, State 1, Line 18

Incorrect syntax near the keyword 'where'.