Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need help in complex query/subquery Expand / Collapse
Author
Message
Posted Tuesday, December 15, 2009 3:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 6, 2014 3:45 PM
Points: 52, Visits: 199
May be its not complex for many experts, but I don't know how to write it in SQL 2K.

Here is my tables
emplyees: EmpId, EmpName,
EmpTours: EmpId, Region, TourStartDate, TourEndDate, etc...
orders: orderdate, EmpId, ordertype, ordertotal etc...

What I want is on a given date for each employee I need to know their first order of the tour, ordertype, ordertotal, lastorder, ordertype and ordertotal.

Example:

Employees:
EmpId ImpName
1 John
2 Mike

EmpTours:
EmpId Region TourStartDate TourEndDate
1 North 5/1/09 6/30/09 --igonore for the query tour ended for given date
1 West 8/15/09 - (on going)
2 South 9/1/09 11/15/09

Orders:
orderdate EmpId ordertype ordertotal
5/5/09 1 A 15,000.00
5/7/09 1 B 75,000.00
5/21/09 1 A 18,000.00
8/17/09 1 B 57,000.00 --first order of John for the tour
8/20/09 1 B 120,000.00
9/4/09 2 A 32,000.00 --first order of Mike for the tour
9/10/09 2 B 81,000.00
9/17/09 1 A 78,000.00 -- last order of John before report date
10/15/09 2 A 7,000.00
10/31/09 2 A 13,000.00 --last order of Mike before report date
11/10/09 2 B 64,500.00
11/14/09 1 A 14,500.00
12/05/09 1 B 77,000.00

Now I choose my reportdate is 10/31/09, my results would be

EmpName Reg TourStart TourEnd FOrdDate FOrdType FtOrdAmt LOrdDate LOrdType LOrdAmt
John West 8/15/09 - 8/17 B 57K 9/17 A 78K
Mike South 9/1/09 11/15/09 9/4 A 32K 10/31 A 13K


Is this possible with sql query? I did that creating temp table and cursor and looping each record, but I am wondering if someone can help to write single query with subquery.

Post #834780
Posted Tuesday, December 15, 2009 4:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
Probably, but then how would you learn how to do it if we do it for you? Give it a try first, and as you have problems, ask for help.

If you really want help, though, I suggest that you read the first article I reference below in my signature block regarding asking for assistance. Please follow the instructions in that article with posting code and sample data. You did nice job as it is, but anyone wanting to help you would have to do some editting of your code and such be fore it is usable. The more you do up front, the better assistance you will get.

I'd also post the code you have written so far as well. It can be the starting point for you moving from cursor-based code to set-based code.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #834789
Posted Tuesday, December 15, 2009 4:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
I second Lynn's reply. Read the referred article and post all what you have tried, that gives a head start for some who wants to solve.

Help us to help you.



Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #834790
Posted Wednesday, December 16, 2009 12:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 6, 2014 3:45 PM
Points: 52, Visits: 199
Thanks Lynn and Bru for pointing me the direction. Here is my script to create tables and sample data, can you please guide me to get expected results?

--===== If the employees table already exists, drop it
IF OBJECT_ID('TempDB..#emplyees','U') IS NOT NULL
DROP TABLE #emplyees

--===== Create the employees table with
CREATE TABLE #employees(EmpID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmpName VARCHAR(30))

--===== If the EmpTours table already exists, drop it
IF OBJECT_ID('TempDB..#EmpTours','U') IS NOT NULL
DROP TABLE #EmpTours
--===== Create the EmpTours table with
CREATE TABLE #EmpTours(TourId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, EmpId INT,
Region VARCHAR(15), TourStartDate DateTime, TourEndDate DateTime)

--===== If the Orders table already exists, drop it
IF OBJECT_ID('TempDB..#Orders','U') IS NOT NULL
DROP TABLE #Orders
--===== Create the Orders table with
CREATE TABLE #Orders(OrderId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
OrderDate DateTime, EmpId Int, OrderType char(1), OrderTotal Money)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Employees ON

--===== Insert the test data into the employees table
INSERT INTO #Employees (EmpID, EmpName)
SELECT '1','John'
UNION ALL
SELECT '2','Mike'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #employees OFF

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #EmpTours ON
--===== Insert the test data into the EmpTours
INSERT INTO #EmpTours (TourId, EmpID, Region, TourStartDate, TourEndDate)
select '1','1','North','5/1/09','6/30/09' --igonore for the query result since tour ended for given date
union all
select '2','1','West','8/15/09','' -- (on going)
union all
select '3','2','South','9/1/09','11/15/09'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #EmpTours OFF


--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Orders ON
--===== Insert the test data into the Orders
INSERT INTO #Orders (OrderId, OrderDate, EmpID, OrderType, OrderTotal)
select '1','5/5/09','1','A',15000.00
union all
select '2','5/7/09','1','B',75000.00
union all
select '3','5/21/09','1','A',18000.00
union all
select '4','8/17/09','1','B',57000.00 --first order of John for the tour
union all
select '5','8/20/09','1','B',120000.00
union all
select '6','9/4/09','2','A', 32000.00 --first order of Mike for the tour
union all
select '7','9/10/09','2','B',81000.00
union all
select '8','9/17/09','1','A', 78000.00 -- last order of John before report date
union all
select '9','10/15/09','2','A',7000.00
union all
select '10','10/31/09','2','A', 13000.00 --last order of Mike before report date
union all
select '11','11/10/09','2','B',64500.00
union all
select '12','11/14/09','1','A',14500.00
union all
select '13','12/05/09','1','B', 77000.00
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Orders OFF
Post #835387
Posted Wednesday, December 16, 2009 12:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
Creating the expected results is really a manual process. Look at your sample data you provided, what should the result set returned look like, what is the correct answer? You may also need to explai why if it doesn't appear intuative, ie what rules did you use to come to the answer.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #835393
Posted Wednesday, December 16, 2009 2:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 6, 2014 3:45 PM
Points: 52, Visits: 199
What I need is, on a given date which employees were on tour and which region? Along with it, I need their tour start date, tour end date, first order of the tour and last order of ther tour with order date and order totals.

So based on my rules and sample data for given date 10/31/09 I should get following results:

EmpName Reg TourStart TourEnd FOrdDate FOrdType FtOrdAmt LOrdDate LOrdType LOrdAmt
John West 8/15/09 - 8/17 B 57K 9/17 A 78K
Mike South 9/1/09 11/15/09 9/4 A 32K 10/31 A 13K
Post #835449
Posted Thursday, December 17, 2009 9:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
mak101, while I agree with Lynn's assessment that you need to try it yourself first, I also can see where to someone new to SQL, this problem would be difficult to even conceptualize, and since you took the time to type up your sample data... So, I have a solution that works with the data you provided, and returns your desired results for this data only. I can think of many ways this will break depending on your data. However, I think this will whet your appetite for SQL. Sorry for the lack of comments, I'm in a rush now, so post back if you have questions about any of it, and you will probably see other solutions, any of which may be very different from mine.

DECLARE @date DATETIME
SELECT @date = '10/31/09'

SELECT
t1.EmpName,
t1.Region,
t1.TourStartDate,
t1.TourEndDate,
t1.FOrdDate,
FOrdType = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType ELSE NULL END),
FOrdTotal = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END),
t1.LOrdDate,
LOrdType = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType ELSE NULL END),
LOrdTotal = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderTotal ELSE NULL END)
FROM
(--Derived table t1 returns the tourdates, and the order dates
SELECT
e.EmpId,
e.EmpName,
et.Region,
et.TourStartDate,
et.TourEndDate,
FOrdDate = MIN(o.OrderDate),
LOrdDate = MAX(o.OrderDate)
FROM #Employees e INNER JOIN #EmpTours et
ON e.EmpId = et.EmpId INNER JOIN #Orders o
ON e.EmpId = o.EmpId
WHERE et.TourStartDate <= @date
AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)
AND o.OrderDate BETWEEN et.TourStartDate AND @date
GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate
) t1 INNER JOIN #Orders o
ON t1.EmpId = o.EmpId
AND (t1.FOrdDate = o.OrderDate OR t1.LOrdDate = o.OrderDate)
GROUP BY t1.EmpName,t1.Region,t1.TourStartDate,t1.TourEndDate,t1.FOrdDate,t1.LOrdDate



Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #835761
Posted Thursday, December 17, 2009 10:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
Actually Greg, now that mak101 put some effort into setting things up, I'm more than willing to help, just haven't had a lot of time to work on it. You put something together, and if I get time between SQL Server 2008 installs, I may see what I can do as well.

I'd still like to see mak101 try something as well, instead of just using what we may put together. Would definately help with his understanding of what is happening in the code. For example, does he understand the use of the derived table in your code and what it is doing?




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #835812
Posted Thursday, December 17, 2009 10:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
Good work Greg !

I spent sometime last evening in solving the issue, but could not move forward after a certain point.



Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #835817
Posted Thursday, December 17, 2009 11:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
Regarding the expected results:


EmpName Reg TourStart TourEnd FOrdDate FOrdType FtOrdAmt LOrdDate LOrdType LOrdAmt
John West 8/15/09 - 8/17 B 57K 9/17 A 78K
Mike South 9/1/09 11/15/09 9/4 A 32K 10/31 A 13K


Since John went on two tours, one in the North Region and one in the West, why is only the West tour reported? Didn't see anything in your criteria for that and just wanted to confirm that yo are only interested in each employee's most recent tour.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #835842
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse