Showing posts with label subquery. Show all posts
Showing posts with label subquery. Show all posts

Monday, March 26, 2012

Help with an WHERE NOT EXISTS subquery please.

Hi, I have 2 tables:

tblStations StationID
Station

tblStationUser RecordID
UserName
Station

I'm trying to come up with a dataset that contains the
tblStations.Station
EXCEPT for where that Station exists in tblStationUser where the
UserName = @.varUserName.

I've tried this but get 0 rows (I should get about 40):

SELECT tblStations.Station
FROM tblStations
WHERE NOT EXISTS
(SELECT tblStationUser.Station FROM tblStationUser WHERE
tblStationUser.UserName=@.varUserName)
ORDER BY Station

I tried the subquery separately which returns the correct number of
rows.

Any clues as to where I'm going wrong?

Thanks!
KathyKathy,

You forgot to join tables. Try one of these queries. I haven't tested them.

SELECT Station
FROM tblStations
WHERE NOT EXISTS (SELECT *
FROM tblStationUser
WHERE tblStationUser.Station = tblStations.Station
AND tblStationUser.UserName = @.varUserName)
ORDER BY Station

OR

SELECT Station
FROM tblStations
WHERE Station NOT IN (SELECT Station
FROM tblStationUser
WHERE UserName = @.varUserName)
ORDER BY Station

Good luck,
Shervin

"KathyB" <KathyBurke40@.attbi.com> wrote in message
news:75e8d381.0310160945.2b2df7e4@.posting.google.c om...
> Hi, I have 2 tables:
> tblStations StationID
> Station
> tblStationUser RecordID
> UserName
> Station
> I'm trying to come up with a dataset that contains the
> tblStations.Station
> EXCEPT for where that Station exists in tblStationUser where the
> UserName = @.varUserName.
> I've tried this but get 0 rows (I should get about 40):
> SELECT tblStations.Station
> FROM tblStations
> WHERE NOT EXISTS
> (SELECT tblStationUser.Station FROM tblStationUser WHERE
> tblStationUser.UserName=@.varUserName)
> ORDER BY Station
> I tried the subquery separately which returns the correct number of
> rows.
> Any clues as to where I'm going wrong?
> Thanks!
> Kathy|||Thanks again Shervin. I wish I knew I fraction of what you do!

Thanks, Kat

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You are welcome Kathy, I'm hanging around here to learn more about SQL
Server. :-)

Shervin

"Kathy Burke" <kathyburke40@.comcast.net> wrote in message
news:3f8fd569$0$193$75868355@.news.frii.net...
> Thanks again Shervin. I wish I knew I fraction of what you do!
>
> Thanks, Kat
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Friday, March 23, 2012

Help with a subquery join

I have a query that produces the following results:

wbs2 wbs3 Name AmtBud BTD BillExt
100 100 Traffic Impact 10000 13563.75 0
140 Highway/Signal 0 0 0
140 100 Highway/Signal 0 0 0

and another query that produces the follwing results:

wbs2 FeeType
100 SC
140 HM&SC
150 HM
160 HM&SC
180 SC
190 SC

So, I want to join both queries to produce the ending result:

wbs2 wbs3 Name AmtBud BTD BillExt FeeType
100 100 Traffic Impact 10000 13563.75 0 SC
140 Highway/Signal 0 0 0 HM&SC
140 100 Highway/Signal 0 0 0 HM&SC

Here are my queries:
first query:

SELECT CASE WHEN WM_Template.WBS2 = '500' THEN '1750.00' + WM_Template.WBS2 ELSE '2000.20' + WM_Template.WBS2 END AS WBS2,
WM_Template.WBS3 AS WBS3,
CASE WHEN WM_Template.WBS2 = '260' THEN 'Pre Con Mtg / Inspections/ Punch List' WHEN WM_Template.WBS2 = '250' THEN 'All Environmental Permits & Approvals'
WHEN WM_Template.WBS2 = '150' THEN 'Project Admin / Clerical / Status Reports ' ELSE WM_Template.LongName END AS LongName,
(ISNULL(LB.AmtBud, 0) + ISNULL(EB.AmtBud, 0)) AS AmtBud, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND
LedgerAR.Period <= '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BTD, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND
LedgerAR.Period = '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BillExt
FROM PR LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '')
AND WM_Template.WBS2 <> '210' AND pr.wbs1 = '001-298'
GROUP BY WM_Template.WBS2, WM_Template.WBS3, WM_Template.LongName, lb.amtbud, eb.amtbud

second query

SELECT WM_Template.WBS2, CASE WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0')
THEN 'HM & SC' WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN 'HM' WHEN (SUM(isnull(LB.AmtBud, 0))
= '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0') THEN 'SC' WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0')
THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') THEN 'SC' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0))
= '0') THEN (CASE WHEN (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0') THEN 'HM' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND
(SUM(isnull(EB.AmtBud, 0)) = '0') THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') AND (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0')
THEN 'HM & SC' END) ELSE 'N/A' END AS FeeType
FROM PR LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '')
AND pr.wbs1 = '001-333'
GROUP BY WM_Template.WBS2

Any suggestions would be helpful.

Thank You.Generically, you could do this:

SELECT <columns>
FROM
<Subquery1> q1
JOIN
<Subquery2> q2
on q1.Key = q2.Key

It may be more efficient to combine the logic of the two queries but I haven't looked to see how practical that would be.|||Thanks for your suggestion it helped me tremendously.

-Laura

Monday, March 12, 2012

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.

Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT

Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date

Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?

Hi Dwight,

Yes, I think so. But you'll have to provide more info first:

* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.

* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.

* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!

* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.

* Explain the business problem behind your question.

The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?

Hi Dwight,

Yes, I think so. But you'll have to provide more info first:

* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.

* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.

* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!

* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.

* Explain the business problem behind your question.

The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||If I had to do it, I'd probably start with the following:

SELECT DISTINCT employee,
(SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
= maintable.employee GROUP BY employee) as ActiveProjectCount,
(SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
TotalProjectCountPerEmployee
FROM projectdb AS maintable

which would yield something like:

Joe510
Mary78
BrianNull1

I would then look at that Null, say Naaaah... and go about doing it right :)

FN

dwight0 wrote:

> I am having a problem with a query,
> I am not sure if i would use a join or a subquery to complete this
> problem.
> I have two queries, and i need to divide one by the other, but i cant
> seem to get any
> type of join to work with them.
> Here is the situation.
> I have a projectDB table that has a list of different projects for
> each employee to work on.
> Each project has an employee assigned to it.
> The start date is null until the employee starts to work on it.
> I want to find how many percent of all their projects that each
> employee is working on.
> In other words:
> I want to divide query A by query B to see how many percent of
> projects each employee is working on.
>
> Query A count of projects that are being worked because they have a
> date per employee:
> SELECT employee, COUNT(employee) AS cnt
> FROM projectDB
> GROUP BY employee, project_start_date
> HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
> Query B: Total amount of project per employee:
> SELECT employee, COUNT(employee) AS cnt
> FROM projectDB
> GROUP BY employee, project_start_date
> Any ideas?|||Oh, I may also look at that first subquery and wonder why the heck I put
a GROUP BY clause when I didn't need one.

FN

fn wrote:

> If I had to do it, I'd probably start with the following:
> SELECT DISTINCT employee,
> (SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
> = maintable.employee GROUP BY employee) as ActiveProjectCount,
> (SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
> TotalProjectCountPerEmployee
> FROM projectdb AS maintable
> which would yield something like:
> Joe 5 10
> Mary 7 8
> Brian Null 1
> I would then look at that Null, say Naaaah... and go about doing it
> right :)
> FN
>
> dwight0 wrote:
>> I am having a problem with a query, I am not sure if i would use a
>> join or a subquery to complete this
>> problem.
>> I have two queries, and i need to divide one by the other, but i cant
>> seem to get any
>> type of join to work with them.
>> Here is the situation.
>> I have a projectDB table that has a list of different projects for
>> each employee to work on.
>> Each project has an employee assigned to it.
>> The start date is null until the employee starts to work on it.
>> I want to find how many percent of all their projects that each
>> employee is working on.
>> In other words:
>> I want to divide query A by query B to see how many percent of
>> projects each employee is working on.
>>
>>
>> Query A count of projects that are being worked because they have a
>> date per employee:
>> SELECT employee, COUNT(employee) AS cnt
>> FROM projectDB
>> GROUP BY employee, project_start_date
>> HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>>
>>
>> Query B: Total amount of project per employee:
>> SELECT employee, COUNT(employee) AS cnt
>> FROM projectDB
>> GROUP BY employee, project_start_date
>>
>> Any ideas?

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?
On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)