Query Question Again - 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

Query Question Again

Author

Topic

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 03:52:13

Hello,

I have to run a query that will give me the names of employees who have one of their invoices done before a certain date and at least one invoice after the x date. Can someone point me in the right direction as to where to start from?

So far I did this: SELECT I.FK_EmpID, I.InvoiceNbr FROM INVOICE AS I
WHERE InvoiceDt<='2002-02-15'

I know, I have to link this to the employee table to get their name, but I'm taking it one step at a time. I don't know how to show only the employees ids that have only one invoice before that date. With that query i'm seeing all the invoices before or equal to that date. If I can do that, then I can try to go from there to add the extra stuff to the query.

Thank you very much.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-12 : 05:15:06

first confirm whether you are using datetime datatype for the
invoice date or varchar it would require to convert the invoice date in proper format for comparison

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

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

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-12 : 06:38:38

try this

select distinct(t.empid) from
(select empid from tablename where date < 'date to be compared wih' ) t
inner join
(select empid from empid where date>'date to be compared wih') f
on t.empid=f.empid

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

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

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 08:28:14

Thank you, I will try that.
Alina

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 09:07:14

Or try this simpler

SELECT		FK_EmpID,
		CASE WHEN InvoiceDt < '2002-02-16' THEN 1 ELSE 0 END) AS [Orders made 20020215 and earlier],
		CASE WHEN InvoiceDt >= '2002-02-16' THEN 1 ELSE 0 END) AS [Orders made after 20020215]
FROM		INVOICE AS I
GROUP BY	FK_EmpID

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 09:08:52

[code]SELECT e.*
FROM Employees AS e
INNER JOIN (
SELECT FK_EmpID
FROM INVOICE
WHERE InvoiceDt < '2002-02-16'
GROUP BY FK_EmpID
HAVING COUNT(*) = 1
) AS f ON f.FK_EmpID = e.EmpID[/code]

E 12°55'05.25"
N 56°04'39.16"

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 09:28:24

that query works, thank you, but what does f stand for?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 09:30:07

"f" is only a table alias for the derived table I made.

E 12°55'05.25"
N 56°04'39.16"

Alina
Starting Member

20 Posts

Posted - 2007-11-12 : 09:47:12

thank you

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-12 : 14:00:09

Here are teo samples, one builds on Peso's example:

-- Setup
DECLARE @T TABLE (EmpID INT, InvoiceDate DATETIME)

INSERT @T
SELECT 1, '20020101'
UNION ALL SELECT 1, '20020115'
UNION ALL SELECT 1, '20020219'
UNION ALL SELECT 2, '20020101'
UNION ALL SELECT 2, '20020219'
UNION ALL SELECT 3, '20020101'
UNION ALL SELECT 4, '20020218'

-- Using Peso's example
SELECT 
	EmpID
FROM
	(
		SELECT
			EmpID,
			SUM(CASE WHEN InvoiceDate < '2002-02-16' THEN 1 ELSE 0 END) AS OrdersBefore,
			SUM(CASE WHEN InvoiceDate >= '2002-02-16' THEN 1 ELSE 0 END) AS OrdersAfter
		FROM
			@T
		GROUP BY
			EmpID
	) AS Temp
WHERE 
	Temp.OrdersBefore = 1
	AND OrdersAfter > 0

-- Another way to do it
SELECT 
	Before.EmpID
FROM
	(
		SELECT EmpID
		FROM @T
		WHERE InvoiceDate < '2002-02-16'
		GROUP BY EmpID
		HAVING COUNT(*) = 1
	) AS Before 
INNER JOIN
	(
		SELECT EmpID
		FROM @T
		WHERE InvoiceDate >= '2002-02-16'
		GROUP BY EmpID
		-- Don't need HAVING clause, just for illustration
		HAVING COUNT(*) > 0
	) AS After 
	ON Before.EmpID = After.EmpID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 14:51:41

Or move the aggregation down to the HAVING clause?

SELECT		FK_EmpID		
FROM		INVOICE AS I
GROUP BY	FK_EmpID
HAVING		SUM(CASE WHEN InvoiceDt < '2002-02-16' THEN 1 ELSE 0 END) = 1
		AND SUM(CASE WHEN InvoiceDt >= '2002-02-16' THEN 1 ELSE 0 END) > 0

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources