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 Thursday, December 17, 2009 12:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,735, Visits: 1,947
Yeah Lynn, he wants the data for the tours that are going on, so tour end date can be NULL or some future date when compared to a particular day, here it is "10/31/09".


Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #835895
Posted Thursday, December 17, 2009 12:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,566, Visits: 2,392
Lynn Pettis (12/17/2009)
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.


Edit: This is why...select '1','1','North','5/1/09','6/30/09' --igonore for the query result since tour ended for given date

That's a good point. I inferred from mak101's comment to ignore the first tour since it was closed that the desire was to include information only for tours that were in progress at the desired target date. That is the reason for...

AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)

I also made the assumption that it is okay for the tour to have ended on the target date.


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #835897
Posted Thursday, December 17, 2009 12:35 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 23,341, Visits: 32,074
We can make all the assumations in the world and still be wrong. I am just asking for clarification of the rules as they aren't clear IMHO.




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 #835917
Posted Thursday, December 17, 2009 1:54 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 all of you working on it.

I was working hard on it and I finished it but not in one query, I dumped all data into #temp table and loop through cursor to find first and last order with other details, I know its long way but it worked. I have not tested Greg's code yet, but I wanted it something like that in one query.

As I mentioned earlier in my post

''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 I need tours details only if on given date tour is on. Sorry for any confusion.
Post #835986
Posted Friday, December 18, 2009 8:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,566, Visits: 2,392
mak101 (12/17/2009)
I wanted it something like that in one query.


Ok, here is where a little explanation may come in handy. If you look at my solution, the one comment I added, since I thought that would be the most confusing aspect, was the derived table. The solution is actually two queries, or a query within a query. You should google for "derived table". It is simply a query that acts like an object, and you can select from it, do calculations on it, join to it, etc. In SQL Server 2005 and above, a CTE does the same thing, and is much simpler to use. Anyhow, the derived table first gets the start and end dates, then you can get the other stuff by joining it back to #Orders. It may be confusing at first, but it will become second nature. To see what is happening, you can run the derived table by itself, like this...


DECLARE @date DATETIME
SELECT @date = '10/31/09'
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

Once you have the dates, the rest is simply a matter of putting the order data in the right place. You may have wondered why I did this..


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)


To best understand it, it would be easiest to run the whole thing without using MAX() for the order data...

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

SELECT
t1.EmpName,
t1.Region,
t1.TourStartDate,
t1.TourEndDate,
t1.FOrdDate,
FOrdType = CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType ELSE NULL END,
FOrdTotal = CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END,
t1.LOrdDate,
LOrdType = CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType ELSE NULL END,
LOrdTotal = 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

Now you can see that in the results, you really have two sets of data. There are two rows with the FOrd information where the LOrd information is null, and there are two rows with the LOrd information where the FOrd information is null. The only way to put the FOrd and LOrd information on the same row is to use a GROUP BY, which means you are going to have to use an aggregate function on at least one of the columns. The MAX() serves this function, and it is really not doing anything other than making the GROUP BY happy. If you want to verify that it is doing nothing, change them to MIN(), and you will see that the results do not change. Now, I'm a trial and error kind of guy, so there are probably many better ways to explain this, but I don't know the terminology for a lot of this stuff. Maybe Lynn or Bru would be able to explain it better. Anyhow, if you have any questions, please let us know.


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #836377
Posted Tuesday, January 28, 2014 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:25 AM
Points: 1, Visits: 123
It's been a quiet long time this question posted but I just saw it. I am not an expert about tsql however I tried to write something about this. I hope it is looking good.

declare @datex datetime
set @datex= '10/31/2009'
--print @datex


;with cte as(
select distinct
c.empid,
c.empname,
a.region,
a.TourStartDate,
a.TourEndDate,
o.orderDate FOrderDate,
o.OrderType FOrderType,
sum(o.OrderTotal) FOrderTotal

from #employees c
join #EmpTours a
on(c.EmpID=a.EmpID)
join #Orders o
on(c.EmpID=o.EmpId)

group by

c.empid,
c.empname,
a.region,
a.TourStartDate,
a.TourEndDate,
o.orderDate,
o.OrderType,
o.orderDate,
o.OrderType),

cte1 as(

select * from (

select empid,empname,region,TourStartDate,TourEndDate,FOrderDate,FOrderType,FOrderTotal, ROW_NUMBER() Over(Partition By tourstartdate
Order by [forderDate] asc) rn from cte
where forderdate> TourStartDate
) a
where rn=1)


select EmpName,region,TourStartDate,TourEndDate,FOrderDate,FOrderType,FOrderTotal,OrderDate LOrderDate,OrderType LOrderType,OrderTotal LOrderTotal from (

select
c.EmpID,c.EmpName,region,TourStartDate,TourEndDate,FOrderDate,FOrderType,FOrderTotal,o.OrderDate,o.OrderType,sum(o.OrderTotal) OrderTotal, ROW_NUMBER() Over(Partition By c.EmpID
Order by o.orderdate desc) rn
from cte1 c
join #Orders o
on c.empid=o.empid
where (@datex<c.TourEndDate or year(c.TourEndDate)=1900) and o.orderdate<=@datex


group by c.EmpID,c.EmpName,region,TourStartDate,TourEndDate,FOrderDate,FOrderType,FOrderTotal,o.OrderDate,o.OrderType) a
where rn=1









Post #1535710
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse