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
No comments:
Post a Comment