another query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

another query question

Author

Topic

Alina
Starting Member

20 Posts

Posted - 2007-11-20 : 05:03:18

Hello. I have the following query:

SELECT EMPLOYEE.Name FROM EMPLOYEE, invoice
WHERE EMPLOYEE.EmpID=INVOICE.FK_EmpID
and employee.empid in (SELECT FK_EmpID FROM INVOICE WHERE InvoiceDt <='2002-02-15' GROUP BY FK_EmpID)
and employee.empid in (SELECT FK_EmpID FROM INVOICE WHERE InvoiceDt >='2002-02-16' GROUP BY FK_EmpID)
GROUP BY Name

my problem with it is that i want it to disiplay only the projections that have one date only on or before 2/15/2002 and currently it displays all the employees that have invoices before and after that date. Can someone please tell me where and what i would need to insert so the count of the invoices on or before 2/15/02 is only equal to 1?

thank you
alina

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 06:59:13

[CODE]
SELECT e.name
FROM employee e
INNER
JOIN invoice i
ON i.fk_empID = e.empID
WHERE invoiceDt <= '2002-02-15'
GROUP
BY e.name
[/CODE]
??

George
<3Engaged!

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-21 : 08:03:02

quote:
Originally posted by Alina

Hello. I have the following query:

SELECT EMPLOYEE.Name FROM EMPLOYEE, invoice
WHERE EMPLOYEE.EmpID=INVOICE.FK_EmpID
and employee.empid in (SELECT FK_EmpID FROM INVOICE WHERE InvoiceDt <='2002-02-15' GROUP BY FK_EmpID)
and employee.empid in (SELECT FK_EmpID FROM INVOICE WHERE InvoiceDt >='2002-02-16' GROUP BY FK_EmpID)
GROUP BY Name

my problem with it is that i want it to disiplay only the projections that have one date only on or before 2/15/2002 and currently it displays all the employees that have invoices before and after that date. Can someone please tell me where and what i would need to insert so the count of the invoices on or before 2/15/02 is only equal to 1?

thank you
alina

try this
SELECT EMPLOYEE.Name FROM EMPLOYEE, invoice
WHERE EMPLOYEE.EmpID=INVOICE.FK_EmpID
and employee.empid in (SELECT FK_EmpID FROM INVOICE WHERE InvoiceDt <='2002-02-15' GROUP BY FK_EmpID having invoice.fk_empid =1)

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources