Sql Query question - is it possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Sql Query question - is it possible?

Author

Topic

dmlockwood
Starting Member

2 Posts

Posted - 2007-12-19 : 13:04:45

Hi guys,

I'm new to the board and new to SQL queries, so I was wondering if you can help me out with this newbie question.

Say I have a table that consists of the following fields, cust#, custname, item#, salesamt, transdate.

Given this information, I want to extract all customers that bought a particular item in a particular date range...say item K01 from November 1st, 2007 to November 30th, 2007. So far so good!

Here is the hard part (for me)...now based on this criteria, I want to show all transactions for those customers that fulfilled the specific requirements that I mentioned.

Just to be clear, I'm not saying that I only want to see
John Smith K01, November 1st
John Smith K01, November 5th
Bill White K01, November 6th
etc

If John Smith bought K01 in November I'd like to see all his transactions ie...

John Smith A01, October 6th
John Smith K01, November 1st
John Smith B02, December 2nd
etc

Thanks in advance!

Don

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 13:07:25

[code]SELECT s.*
FROM SourceTable AS s
INNER JOIN (
SELECT DISTINCT CustomerID
FROM SourceTable
WHERE Item = 'K01'
AND PurchaseDate >= '20071101'
AND PurchaseDate < '20071201'
) AS d ON d.CustomerID = s.CustomerID[/code]

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

dmlockwood
Starting Member

2 Posts

Posted - 2007-12-19 : 13:32:41

Thank you so much, it worked!

Now if you could help me with the syntax on one final thing I wont bug you again...haha.

If I were to now join this on Customer# with an additional table...how would I incorperate this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:08:58

[code]SELECT s.*,
q.*
FROM SourceTable AS s
INNER JOIN (
SELECT DISTINCT CustomerID
FROM SourceTable
WHERE Item = 'K01'
AND PurchaseDate >= '20071101'
AND PurchaseDate < '20071201'
) AS d ON d.CustomerID = s.CustomerID
INNER JOIN SomeTable AS q ON q.SomeCol = s.SomeCol[/code]

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