September 16, 2011 at 11:42 am
These are two working wueries that return the correct results. I need to combine these two queries so that I can use them in a report. Any help would be appreciated. THank you!!!
select a.accountnumber, a.name, b.numberofemployees
from organizations as a
join organizationdetails as b
on a.accountnumber=b.accountnumber join
where a.accountnumber=1111 and b.year=2010 and b.detailtype='NumberOfEmployees'
select distinct count(accountnumber) as [Donors]
from transactions
where orgaccountnumber=1111 and campaignyear=2010
September 16, 2011 at 12:05 pm
i didn't see any relationship between the two queries, so i could only assume a CROSS JOIN would be right.
does this do what you are looking for?
SELECT
a.accountnumber,
a.name,
b.numberofemployees,
MYTOTALS.[Donors]
FROM
organizations AS a
INNER JOIN organizationdetails AS b
ON a.accountnumber = b.accountnumber
CROSS JOIN (SELECT
DISTINCT
COUNT(accountnumber)AS [Donors]
FROM
transactions
WHERE
orgaccountnumber = 1111
AND campaignyear = 2010) MYTOTALS
WHERE
a.accountnumber = 1111
AND b.year = 2010
AND b.detailtype = 'NumberOfEmployees'
Lowell
September 16, 2011 at 12:09 pm
I am sorry, the two queries have the same accountnumber (accountnumber=orgaccoutnnumber).
I have never used a cross join, I will give it a shot. Is there a simpler way to write the query since they are related on the accountnumber?
Thank you very much for your help.
September 16, 2011 at 12:17 pm
here's my best guess featuring a left outer join, but it seems wierd for my inner query alaised as MYTOTALS: i'm getting the accoutn number, and the distinct count...not sure if that DISTINCT is even needed;
SELECT
a.accountnumber,
a.name,
b.numberofemployees,
MYTOTALS.[Donors]
FROM
organizations AS a
INNER JOIN organizationdetails AS b
ON a.accountnumber = b.accountnumber
LEFT OUTER JOIN (SELECT
accountnumber,
COUNT(DISTINCT accountnumber)AS [Donors]
FROM
transactions
WHERE
orgaccountnumber = 1111
AND campaignyear = 2010
GROUP BY accountnumber) MYTOTALS
ON a.accountnumber = MYTOTALS.accountnumber
WHERE
a.accountnumber = 1111
AND b.year = 2010
AND b.detailtype = 'NumberOfEmployees'
SELECT
DISTINCT
COUNT(accountnumber)AS [Donors]
FROM
transactions
WHERE
orgaccountnumber = 1111
AND campaignyear = 2010
Lowell
September 16, 2011 at 12:37 pm
Thank you Lowell, that is perfect.
I always make small mistakes when I change all the column names before posting to the web. There is an employeeaccountnumber and an orgaccountnumber. So I need the distinct for the employeeaccountnumber because employees have multiple transactions.
Thank you again for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply