Combine SUMs

How can I combine the 2 Sum amounts below. Basically teh 2 queries are exactly the same, just hitting 2 different tables (pdc and pdcdeleted) with the same structure:

SELECT SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdc pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery

SELECT SUM(PQuery2.Amount) as PDCs_IL_deleted
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdcdeleted pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery2

Since there is no group by, I will assume that you just working with one value, then you can simply do something like:

declare @.PDCs_IL int
select @.PDCs_IL = SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,

declare @.PDCs_IL_deleted int
select @.PDCs_IL_deleted = SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,

select @.PDCs_IL + @.PDCs_IL_deleted

or

select ( first query ) + ( second query )

as in

create table #test
(
value int
)
insert into #test values (1)
insert into #test values (1)
insert into #test values (1)

select ( select sum(value) from #test ) + ( select sum(value) from #test )

--note that you might need to check for a NULL value :)

You could probably just add another left join to the deleted table and include the where clause, but it is really messy to contemplate all that you are trying to do without more information. Either of these other methods should work fine if the two queries already work fine.

sqlsql

Combine 3 huge queryies to produce another SUM

I have to combine 3 huge queries to get a GT for ProjGross (see calcs.xls on formulas). Please check out ProjGross here
http://www.photopizzaz.biz/projgross.txt
and see this for the logic for projgross
http://www.photopizzaz.biz/calcs.xls
I am trying to combine (Daily Run Rate * Days Remaining )+ InHouse1 as (stated in the xls) using the previous select statements respectively to get my final ProjGross

All queries so far in my projgross.txt work great...now I just need to combine what's needed to move forward in my calcs.xls to create ProjGross...at the Company level.

You'll see that I attempted to start the ProjGross Query in my .txt file

I have rewritten INHOUSE1 (START) as follows:

SELECT sub.CustomerNumber, sub.PostedAmount + sub.CCsOld + sub.CCsNew + sub.PDCsNew + sub.PDCsOld
FROM
( Select d0.CustomerNumber AS CustomerNumber,
CASE WHEN d0.Type = 'In-House'
THEN SUM(ISNULL(d0.PostedAmount,0))
ELSE 0
END AS PostedAmount,
CASE WHEN d0.Type = 'CC'
AND d0.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHoldDate IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS CCsOld,
CASE WHEN d0.Type = 'CC'
AND d0.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHoldDate IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS CCsNew,
CASE WHEN d0.Type = 'PDC'
AND d0.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHold IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS PDCsNew,
CASE WHEN d0.Type = 'PDC'
AND d0.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHold IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS PDCsOld
FROM DCR d0 With(NOLOCK)
JOIN CustomerNumber cno
ON cno.customernumber = d0.customernumber
AND (d0.branch = '00002' AND cno.CheckBranch = 1)
OR (cno.CheckBranch = 0)
WHERE d0.Type = 'In-House'
GROUP BY d0.CustomerNumber
) AS sub

You should be able to get the rest using the same principle. You are using the same table each time, so all this can be done in one query, albeit a *** of a query, and you can do away with the ...customernumber in... by joining to that table, which should boost peformance a lot. Let me know if you have any more problems. That should work, but you may get a compilation errors, as I do not have the db here in front of me to test.

|||yea but is it going to go through each Case? each case statement will evaluate to true because there are records for each instance that you're checking so I don't think CASE is good here..only one can evaluate true in a CASE|||

I've actually already solved it on my own just earlier today. Thanks for your input though!

Select @.ProjFee = SUM(ProjFee)

FROM

(

Select d1.CustomerNumber, ((((ISNULL(PostedTable.PostedAmount,0) + ISNULL(CCsNewTable.NewCCs,0) + ISNULL(PDCsNewTable.NewPDCs,0)) / (select CurrentPostingDay from v_CurrentPostingDay))) + ((ISNULL(PostedTable.PostedAmount,0) + ISNULL(CCsOldTable.OldCCs,0) + ISNULL(CCsNewTable.NewCCs,0) + ISNULL(PDCsNewTable.NewPDCs,0) + ISNULL(PDCsOldTable.OldPDCs,0)))) * d1.FeeSchedule / 100 as ProjFee

FROM (Select distinct CustomerNumber, FeeSchedule From DCR) d1

- $Posted

LEFT JOIN

(Select d0.CustomerNumber, SUM(ISNULL(d0.PostedAmount,0)) AS PostedAmount

FROM DCR d0 (NOLOCK)

WHERE d0.Type = 'In-House'

AND ((d0.branch = '00002' and d0.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d0.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

GROUP BY d0.CustomerNumber) as PostedTable ON PostedTable.CustomerNumber = d1.CustomerNumber

- OldCCs

LEFTJOIN

(Select d3.CustomerNumber, SUM(ISNULL(d3.CC,0)) AS OldCCs

FROM DCR d3 (NOLOCK)

WHERE d3.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d3.Type = 'CC'

AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d3.dc_OnHoldDate IS NULL

GROUP BY d3.CustomerNumber) as CCsOldTable ON CCsOldTable.CustomerNumber = d1.CustomerNumber

- NewCCs

LEFTJOIN

(Select d4.CustomerNumber, SUM(ISNULL(d4.CC,0)) AS NewCCs

FROM DCR d4 (NOLOCK)

WHERE d4.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d4.Type = 'CC'

AND ((d4.branch = '00002' and d4.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d4.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d4.dc_OnHoldDate IS NULL

GROUP BY d4.CustomerNumber) as CCsNewTable ON CCsNewTable.CustomerNumber = d1.CustomerNumber

- NewPDCs

LEFTJOIN

(Select d5.CustomerNumber, SUM(ISNULL(d5.PDC,0)) AS NewPDCs

FROM DCR d5 (NOLOCK)

WHERE d5.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d5.Type = 'PDC'

AND ((d5.branch = '00002' and d5.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d5.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d5.pdc_OnHold IS NULL

GROUP BY d5.CustomerNumber) as PDCsNewTable ON PDCsNewTable.CustomerNumber = d1.CustomerNumber

- OldPDCs

LEFTJOIN

(Select d6.CustomerNumber, SUM(ISNULL(d6.PDC,0)) AS OldPDCs

FROM DCR d6 (NOLOCK)

WHERE d6.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d6.Type = 'PDC'

AND ((d6.branch = '00002' and d6.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d6.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d6.pdc_OnHold IS NULL

GROUP BY d6.CustomerNumber) as PDCsOldTable ON PDCsOldTable.CustomerNumber = d1.CustomerNumber

GROUP BY d1.CustomerNumber,

PostedTable.PostedAmount,

CCsOldTable.OldCCs,

CCsNewTable.NewCCs,

PDCsNewTable.NewPDCs,

PDCsOldTable.OldPDCs,

d1.FeeSchedule

) as z

SELECT ProjFee = @.ProjFee

|||Ok, but my query is much simpler, and there are only two cases for each case statement, true, so add value, or false so add 0. Try it and see. It will only add the to the approriate totals for the relevant records, the rest it will ignore by adding 0. The query you have written is way too complicated and thus probably very slow. I would try and simplify it, hopefully my example will help.

columns containing percantage values

Hi,
I am a newbie in reporting services with a pretty easy question.
How can display percentage values of a column depending of the column
sum?
Example:
_______________________________________________
row1 3 30%
row2 5 50%
row3 2 20%
_______________
sum 10 100%
_______________________________________________
How do I create the last column?
ThanxLast week I did this for the first time.
Created a sum total in a report footer for your 2nd col and the10 would show
for your sample. Give it a label like Total_Count. Then for the 3rd col
which is to be the percentage, use the expression =Sum(Fields!Total_Count.
Value)/(ReportItems!TotalCount.Value)
HTH!
James Ski
mickmack wrote:
>Hi,
>I am a newbie in reporting services with a pretty easy question.
>How can display percentage values of a column depending of the column
>sum?
>Example:
>_______________________________________________
>row1 3 30%
>row2 5 50%
>row3 2 20%
>_______________
>sum 10 100%
>_______________________________________________
>How do I create the last column?
>Thanx
--
Message posted via http://www.sqlmonster.com|||Thank you James for your prompt reply,
that works with one column.
But how would I create the following matrix?
_____________________________________
row1 3 30% 8 40%
row2 5 50% 8 40%
row3 2 20% 4 20%
_________________________
sum 10 100% 20 100%
_____________________________________
I dont know how to call the 10 and the 20 seperatly. Which names do
they have?
Thanx|||You could use the sum aggregate , but shouldn't the group % always be 100%..
You could also refer to the column with 10 in it using
ReportItems!textboxname.Value
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"mickmack" wrote:
> Thank you James for your prompt reply,
> that works with one column.
> But how would I create the following matrix?
> _____________________________________
> row1 3 30% 8 40%
> row2 5 50% 8 40%
> row3 2 20% 4 20%
> _________________________
> sum 10 100% 20 100%
> _____________________________________
> I dont know how to call the 10 and the 20 seperatly. Which names do
> they have?
> Thanx
>|||here is how I did:
drag a list into your report, in the list, drag in two subreports, one is
on the left and another one is on the right. then you write stored procudures
(name it sptest for now) with one parameter. In your subreport one, you call
sptest 0, and it will display the left 3 columns, subreport two, you call
sptest 1, and it will display the right 2 columns. sptest 0, and sptest 1 is
the dataset that you create when you design your subreport. The hard part for
this is the store procedure. you need to decide how many rows/columns that
you want to display. For my example, sptest 0, I display:
01/2004 5
02/2004 6
03/2004 7
.
.
.
sptest 1, then display on the second subreport
01/2005 1
02/2005 2
03/2005 3
.
.
.
finally, it ends up like this
01/2004 5 01/2005 1
02/2004 6 02/2005 2
03/2004 7 03/2005 3
. .
. .
. .
I hope this will give you some idea.
Henry
"mickmack" wrote:
> Thank you James for your prompt reply,
> that works with one column.
> But how would I create the following matrix?
> _____________________________________
> row1 3 30% 8 40%
> row2 5 50% 8 40%
> row3 2 20% 4 20%
> _________________________
> sum 10 100% 20 100%
> _____________________________________
> I dont know how to call the 10 and the 20 seperatly. Which names do
> they have?
> Thanx
>|||today I found a page at microsoft, where the problem is solved:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/semiadd2.asp

Column Totals / Sum by Date

HI,
I am new to RS and I am running into some problems create reports. What I
would like to do is create a report that will count all distinct rows for a
"Users" column for every single date. I am able to get the total users from
the "Users" column but the problem is getting a running list of totals by
date. There is no date field in the database. There is a date field for
enrollment and unenroll but these are not the dates I am looking for. I
would like every single date to be totaled.
Do you know of any examples of this on the web or an example that you could
send me?
Any help would be great!
ThanksYou can't report on anything that's not in your data source. It sounds like
you're trying to get a count of users for each date. You'll have to solve
that in your source query first, then you can report on it. My suggestion
is to create a new reference date table with a record for each date in the
range you want to report on. That's only 365 records per year, so make as
many years as you want. Then join to the Users table on ReferenceDate
between EnrollDate and UnenrollDate.
A nice benefit of having a reference date table is that you can put other
data in each record as well, such as week number, quarter, fiscal year and
calendar year, for easy grouping. Yeah, it's denormalized, but makes
reporting a snap. It's easy to do this is in Excel, then import the data
into SQL.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"ACD" <ACD@.discussions.microsoft.com> wrote in message
news:66C00F58-9E89-456B-9931-5E2E5E964645@.microsoft.com...
> HI,
> I am new to RS and I am running into some problems create reports. What
> I
> would like to do is create a report that will count all distinct rows for
> a
> "Users" column for every single date. I am able to get the total users
> from
> the "Users" column but the problem is getting a running list of totals by
> date. There is no date field in the database. There is a date field for
> enrollment and unenroll but these are not the dates I am looking for. I
> would like every single date to be totaled.
> Do you know of any examples of this on the web or an example that you
> could
> send me?
> Any help would be great!
> Thanks

Column summing other columns

Hi there
I've got this really basic question:
ocasionally I have to do something like this
select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
e.g. to sum up columns in another column.
it seems extremely unelegant, unfortunately using aliases in last
column ( SA+SB+SC) is impossible.
does it make sense performance-wise to leave such an operation to sql
server? will he optimize the query to prevent multiple calculation of
the same column sums, or should I rather sum the columns up on the
client side?
alos, isn't it possible to get rid of duplicating complicated
expressions by using some kind of alias if I want to use them in other
columns ?
thanks
Hp.
The following example is one way to simplify the calculation:
CREATE TABLE foo
(
a INT,
b INT,
c INT
)
INSERT foo SELECT 1, 1, 1
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 3, 4
INSERT foo SELECT 1, 3, 5
SELECT SA, SB, SC, SA + SB + SC
FROM (SELECT SUM(a) AS SA, SUM(b) AS SB, SUM(c) AS SC
FROMfoo) AS T1
HTH
- Peter Ward
WARDY IT Solutions
"H5N1" wrote:

> Hi there
> I've got this really basic question:
> ocasionally I have to do something like this
> select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
> e.g. to sum up columns in another column.
> it seems extremely unelegant, unfortunately using aliases in last
> column ( SA+SB+SC) is impossible.
>
> does it make sense performance-wise to leave such an operation to sql
> server? will he optimize the query to prevent multiple calculation of
> the same column sums, or should I rather sum the columns up on the
> client side?
> alos, isn't it possible to get rid of duplicating complicated
> expressions by using some kind of alias if I want to use them in other
> columns ?
> thanks
> Hp.
>

Column summing other columns

Hi there
I've got this really basic question:
ocasionally I have to do something like this
select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
e.g. to sum up columns in another column.
it seems extremely unelegant, unfortunately using aliases in last
column ( SA+SB+SC) is impossible.
does it make sense performance-wise to leave such an operation to sql
server? will he optimize the query to prevent multiple calculation of
the same column sums, or should I rather sum the columns up on the
client side?
alos, isn't it possible to get rid of duplicating complicated
expressions by using some kind of alias if I want to use them in other
columns ?
thanks
Hp.The following example is one way to simplify the calculation:
CREATE TABLE foo
(
a INT,
b INT,
c INT
)
INSERT foo SELECT 1, 1, 1
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 3, 4
INSERT foo SELECT 1, 3, 5
SELECT SA, SB, SC, SA + SB + SC
FROM (SELECT SUM(a) AS SA, SUM(b) AS SB, SUM(c) AS SC
FROM foo) AS T1
HTH
- Peter Ward
WARDY IT Solutions
"H5N1" wrote:

> Hi there
> I've got this really basic question:
> ocasionally I have to do something like this
> select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
> e.g. to sum up columns in another column.
> it seems extremely unelegant, unfortunately using aliases in last
> column ( SA+SB+SC) is impossible.
>
> does it make sense performance-wise to leave such an operation to sql
> server? will he optimize the query to prevent multiple calculation of
> the same column sums, or should I rather sum the columns up on the
> client side?
> alos, isn't it possible to get rid of duplicating complicated
> expressions by using some kind of alias if I want to use them in other
> columns ?
> thanks
> Hp.
>

Column summing other columns

Hi there
I've got this really basic question:
ocasionally I have to do something like this
select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
e.g. to sum up columns in another column.
it seems extremely unelegant, unfortunately using aliases in last
column ( SA+SB+SC) is impossible.
does it make sense performance-wise to leave such an operation to sql
server? will he optimize the query to prevent multiple calculation of
the same column sums, or should I rather sum the columns up on the
client side?
alos, isn't it possible to get rid of duplicating complicated
expressions by using some kind of alias if I want to use them in other
columns ?
thanks
Hp.The following example is one way to simplify the calculation:
CREATE TABLE foo
(
a INT,
b INT,
c INT
)
INSERT foo SELECT 1, 1, 1
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 2, 3
INSERT foo SELECT 1, 3, 4
INSERT foo SELECT 1, 3, 5
SELECT SA, SB, SC, SA + SB + SC
FROM (SELECT SUM(a) AS SA, SUM(b) AS SB, SUM(c) AS SC
FROM foo) AS T1
HTH
- Peter Ward
WARDY IT Solutions
"H5N1" wrote:
> Hi there
> I've got this really basic question:
> ocasionally I have to do something like this
> select sum(a) as SA, sum(b) as SB , sum(c) as SC, sum(a)+sum(b)+sum(c)
> e.g. to sum up columns in another column.
> it seems extremely unelegant, unfortunately using aliases in last
> column ( SA+SB+SC) is impossible.
>
> does it make sense performance-wise to leave such an operation to sql
> server? will he optimize the query to prevent multiple calculation of
> the same column sums, or should I rather sum the columns up on the
> client side?
> alos, isn't it possible to get rid of duplicating complicated
> expressions by using some kind of alias if I want to use them in other
> columns ?
> thanks
> Hp.
>