Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help in complex query/subquery


Need help in complex query/subquery

Author
Message
Bru Medishetty
Bru Medishetty
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 1950
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
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24261 Visits: 37981
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.

Cool
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)
mak101
mak101
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 218
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.
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
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.
yakantr
yakantr
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 125
It's been a quiet long time this question posted but I just saw it. 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search