Change to "Total Hours" Difference

How can I change the output into the total hours between 2 smalldatetime data? TIF

DECLARE @.iDAY AS SmallDatetime
SET @.iDAY = '2004-08-12 10:05:00'

PRINT @.iDAY
PRINT GETDATE()

PRINT GETDATE()-@.iDAY
+++++++++++++++
Aug 12 2004 10:05AM
Aug 13 2004 10:05AM
Jan 2 1900 12:00AM <-- I want to get the total hours (i.e. 24)I'd use:SELECT DateDiff(minute, '2004-08-12 10:05', '2004-08-13 10:05') / 60-PatP|||Thanks. That did the trick.

I found out that I can also use this one instead.

PRINT DATEDIFF(hour,GETDATE(),@.iDAY)|||I don't think that using hour will give you what you want, although it might. Using hour will return the number of hour boundaries crossed, which isn't what I think you want. Consider the case of:SELECT DateDiff(minute, '2004-08-12 10:55', '2004-08-13 10:05') / 60
, DateDiff(hour, '2004-08-12 10:55', '2004-08-13 10:05')-PatP|||It looks OK. It's rounding off giving me a minor difference but I'm not really particular with exact figures.

Thanks again.

DECLARE @.iDAY AS SmallDatetime
SET @.iDAY = '2004-08-12 11:34:00'

PRINT @.iDAY
PRINT GETDATE()

PRINT DATEDIFF(minute,GETDATE(),@.iDAY)/60
PRINT DATEDIFF(hour,GETDATE(),@.iDAY)

**** RESULTS ****

Aug 12 2004 11:34AM
Aug 17 2004 10:32AM

-118
-119

Change the total (sum generated function) in a matrix

Hi,

Can somebody help me ? I really need to create a matrix with totals but I don't need a to use a sum

I need another function (AVG, Count). Can it be done ?

How can I change the Total function to put another aggregate function ?

Thanks

cyprix,

Yes, you can change the SUM function to any aggregate, the BIDS just automatic places SUM for you but you can alter it.

Ham

|||

Hi hammer

I'd love 2 know where to alter it.
Fine i click on the little green traingle,
I can still only set the properties.

No option for an expression.

G

|||

Please tell me how to make the change. If I click the green triangle I cannot edit the formula.

Thanks,

|||

cyprix,

I'm giving you a link to Robert B. MSFT solution for your problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=364020&SiteID=1

Ham

|||

Thanks H

Good to see an alternate workaround to the one i'm using

G

Change the Parameter in an query

I have a report "Clearance Summary Report:
Each page summarized the total sales for one agency.
Everything works great except for one total "Adjustments:"
An agency can have one order but have 50 adjustments to that order.
That is why the Adjustments can not be included in the "dsReport" query.
For that reason the Adjustments number is calculate with its own query
(dsAdjustments). but gets two parameters ClearanceID and MagazineID.
NOW the problem.
The help file says to create two parameters. ClearanceID and MagazineID.
Declear them internal.
Link them to the fields: ClearanceID and MagazineID.
Then the dsAdjustments query can refreance the paramters ClearanceID and
MagazineID.
When the report is run the dsAjustments query gets it value from the
parameters ClearanceID and MagazineID and then returns a number. That is how
I get the adjusments.
This works if I lookup only one Agency. But if there are two or more
agencies in the report the parameters ClearanceID and MagazineID are only
fill in at the begining of the report. All the other Agencies reports ty to
use the first agency adjustmets numbers.
Question: How do I get the parameters ClearanceID and MagzineID to update
for every new Agency''
Thank you
Scott BurkeRead up on subreports. Subreports are exactly for this sort of this.
Master-detail type reports pretty much must be done using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
>I have a report "Clearance Summary Report:
> Each page summarized the total sales for one agency.
> Everything works great except for one total "Adjustments:"
> An agency can have one order but have 50 adjustments to that order.
> That is why the Adjustments can not be included in the "dsReport" query.
> For that reason the Adjustments number is calculate with its own query
> (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
> NOW the problem.
> The help file says to create two parameters. ClearanceID and MagazineID.
> Declear them internal.
> Link them to the fields: ClearanceID and MagazineID.
> Then the dsAdjustments query can refreance the paramters ClearanceID and
> MagazineID.
> When the report is run the dsAjustments query gets it value from the
> parameters ClearanceID and MagazineID and then returns a number. That is
> how
> I get the adjusments.
> This works if I lookup only one Agency. But if there are two or more
> agencies in the report the parameters ClearanceID and MagazineID are only
> fill in at the begining of the report. All the other Agencies reports ty
> to
> use the first agency adjustmets numbers.
> Question: How do I get the parameters ClearanceID and MagzineID to update
> for every new Agency''
> Thank you
> Scott Burke
>|||Hi Bruce, This report org used a subreport to produce the totals. HOWEVER;
For some reason unknow to everyone I talked to the Report server tends to
lose/ can't find the subreport!!!!!
The last time it happed I had to redeploy the main report. That did not
work this time.
We only had the report server for four months! I dont look forward to
redeploying the CommisionSummaryReport every two months!
my solution is to replace the subreport. Besides running faster the only
problem I am having is getting the right values to the dsAdjustment query.
My only problem is getting the right ClearanceID and MagazineID value to the
query.
Is it possible to load a value into the parameters?
I am trying this in the textbox:
=Parameters!MagazineID.Value = Fields!MagazineID.Value
=(Fields!Adjustment.Value, "dsAdjustment")
The problem here is that "=Parameters!MagazineID.Value =Fields!MagazineID.Value" is being treaded like a comparason. The textbox
displays "True" or "False".
Any Ideals?
Scott Burke
"Bruce L-C [MVP]" wrote:
> Read up on subreports. Subreports are exactly for this sort of this.
> Master-detail type reports pretty much must be done using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
> news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
> >I have a report "Clearance Summary Report:
> > Each page summarized the total sales for one agency.
> >
> > Everything works great except for one total "Adjustments:"
> >
> > An agency can have one order but have 50 adjustments to that order.
> > That is why the Adjustments can not be included in the "dsReport" query.
> >
> > For that reason the Adjustments number is calculate with its own query
> > (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
> >
> > NOW the problem.
> > The help file says to create two parameters. ClearanceID and MagazineID.
> > Declear them internal.
> > Link them to the fields: ClearanceID and MagazineID.
> > Then the dsAdjustments query can refreance the paramters ClearanceID and
> > MagazineID.
> >
> > When the report is run the dsAjustments query gets it value from the
> > parameters ClearanceID and MagazineID and then returns a number. That is
> > how
> > I get the adjusments.
> >
> > This works if I lookup only one Agency. But if there are two or more
> > agencies in the report the parameters ClearanceID and MagazineID are only
> > fill in at the begining of the report. All the other Agencies reports ty
> > to
> > use the first agency adjustmets numbers.
> >
> > Question: How do I get the parameters ClearanceID and MagzineID to update
> > for every new Agency''
> >
> > Thank you
> > Scott Burke
> >
>
>|||Don't know why you are losing subreports. I have lots and lots and lots of
subreports. Not only have I never seen this but I have not seen any posting
about losing subreports. Makes me wonder what is unusual about your
environment.
Read up on drill-down (not drill through). Basically you have a single query
that contains all your information. The master information is repeated for
every row. Then you design your report to drill-down (click on a + sign to
see more) OR if you don't want to use drill down UI then you just hide the
master information when it is in the above row.
What you are trying to do will not work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:D302FB3A-8351-4C5E-AD1E-0011FCFC6634@.microsoft.com...
> Hi Bruce, This report org used a subreport to produce the totals.
> HOWEVER;
> For some reason unknow to everyone I talked to the Report server tends to
> lose/ can't find the subreport!!!!!
> The last time it happed I had to redeploy the main report. That did not
> work this time.
> We only had the report server for four months! I dont look forward to
> redeploying the CommisionSummaryReport every two months!
> my solution is to replace the subreport. Besides running faster the only
> problem I am having is getting the right values to the dsAdjustment query.
> My only problem is getting the right ClearanceID and MagazineID value to
> the
> query.
> Is it possible to load a value into the parameters?
> I am trying this in the textbox:
> =Parameters!MagazineID.Value = Fields!MagazineID.Value
> =(Fields!Adjustment.Value, "dsAdjustment")
>
> The problem here is that "=Parameters!MagazineID.Value => Fields!MagazineID.Value" is being treaded like a comparason. The textbox
> displays "True" or "False".
> Any Ideals?
> Scott Burke
>
>
> "Bruce L-C [MVP]" wrote:
>> Read up on subreports. Subreports are exactly for this sort of this.
>> Master-detail type reports pretty much must be done using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
>> news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
>> >I have a report "Clearance Summary Report:
>> > Each page summarized the total sales for one agency.
>> >
>> > Everything works great except for one total "Adjustments:"
>> >
>> > An agency can have one order but have 50 adjustments to that order.
>> > That is why the Adjustments can not be included in the "dsReport"
>> > query.
>> >
>> > For that reason the Adjustments number is calculate with its own query
>> > (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
>> >
>> > NOW the problem.
>> > The help file says to create two parameters. ClearanceID and
>> > MagazineID.
>> > Declear them internal.
>> > Link them to the fields: ClearanceID and MagazineID.
>> > Then the dsAdjustments query can refreance the paramters ClearanceID
>> > and
>> > MagazineID.
>> >
>> > When the report is run the dsAjustments query gets it value from the
>> > parameters ClearanceID and MagazineID and then returns a number. That
>> > is
>> > how
>> > I get the adjusments.
>> >
>> > This works if I lookup only one Agency. But if there are two or more
>> > agencies in the report the parameters ClearanceID and MagazineID are
>> > only
>> > fill in at the begining of the report. All the other Agencies reports
>> > ty
>> > to
>> > use the first agency adjustmets numbers.
>> >
>> > Question: How do I get the parameters ClearanceID and MagzineID to
>> > update
>> > for every new Agency''
>> >
>> > Thank you
>> > Scott Burke
>> >
>>

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'.

change null row of roll up query

hi all

i m using rollup statement in my query. it gives me total field with null caption.

is it possible that i can change this null field with caption "total"

thanks & regards

ganesh

If I understand you question you could use following statement:

<your total field expression> as "Total"

Please, show your query

|||

Yes you can.. use the reference as the bellow query

Code Snippet

Declare @.data Table
(
Region Varchar(100),
Country Varchar(100),
Sales float
);

Insert Into @.data Values ('EMEA', 'France', 100);
Insert Into @.data Values ('EMEA', 'France', 200);
Insert Into @.data Values ('EMEA', 'Germany', 56);
Insert Into @.data Values ('EMEA', 'Germany', 12);
Insert Into @.data Values ('EMEA', 'UK', 1256);
Insert Into @.data Values ('EMEA', 'UK', 1212);
Insert Into @.data Values ('APJ', 'Japan', 130);
Insert Into @.data Values ('APJ', 'Japan', 32);
Insert Into @.data Values ('APJ', 'China', 256);
Insert Into @.data Values ('APJ', 'China', 212);
Insert Into @.data Values ('APJ', 'India', 23);
Insert Into @.data Values ('APJ', 'India', 232);
Insert Into @.data Values ('America', 'US', 23);
Insert Into @.data Values ('America', 'US', 23432);
Insert Into @.data Values ('America', 'Canada', 223256);
Insert Into @.data Values ('America', 'Canada', 2122);
Insert Into @.data Values ('America', 'Brazil', 23232);
Insert Into @.data Values ('America', 'Brazil', 223232);

Select
isnull(Case When Country is NULL Then Region + ' Total' Else Region End,'Grand Total') Region,
isnull(Country,'') Country,
Sum(Sales)
From
@.Data
Group By
Region,Country With Rollup

change null field of rollup summary

hi all

i m using sql server 2005. i m using rollup query to get total sum and sum with group within single query. it shows me total amount but with null field . i want to change this null field with "total " caption.

pls suggest how this thing is possible.

thanks and regards

Ganesh

Moved to t-sql forum...|||

That is the way a Rollup query works, and, unfortunately, as far as I know, there is no way to get around it.

Of course, your client side application could easily do that. (After all, presentation is the point of client applications, data storage and retrieval is the point of SQL Server.)

|||

Use the GROUPING function in the SELECT list like:

CASE GROUPING(col1) WHEN 1 THEN 'Total' ELSE Col1 END

See Books Online for more details. Or post your sample query here to get the corrected one.

|||Good Idea Umachardar, I consider it, but discarded the idea since it didn't quite seem to fit the OP's request. But perhaps it will.