Last 3 Dates
-
The following query selects all company information.
Each company has many CallStart and CallEnd dates.
The query below has roughly 25 CallStart/ CallEnd dates for each Company.
How would I retrieve just the Last 3 call dates for each company?
SELECT SecUser.lastN, SecUser.firstN, Customer.cName,
LTRIM(RTRIM(Customer.address1) + ' ' + RTRIM(Customer.address2) + ' ' + Customer.city)as Address,
Calllog.callStart, Calllog.callEnd, Calllog.direction,
Customer.customerId, campbase.BaseID, campbase.Description, campbase.NumAccts,
Customer.custType, Customer.BPCSCustID, Customer.custSegment, Customer.campaignId,
Customer.IsActive
FROM Customer
INNER JOIN campbase ON Customer.cbid = campbase.cbid
INNER JOIN CampBaseRep INNER JOIN CampRep ON CampBaseRep.RepID = CampRep.RepID
AND CampBaseRep.RepID = CampRep.RepID
INNER JOIN SecUser ON CampRep.userID = SecUser.userId AND CampRep.userID = SecUser.userId
ON campbase.cbid = CampBaseRep.cbId
INNER JOIN Calllog ON Customer.customerId = Calllog.customerId
WHERE Customer.custSegment='Retail'
AND Customer.campaignId='43'
AND Customer.IsActive='1'
AND CampBaseRep.Owner='1'
Thanks
-
...
From Customer c1
...
WHERE Customer.custSegment='Retail'
AND Customer.campaignId='43'
AND Customer.IsActive='1'
AND CampBaseRep.Owner='1'
And Customer.customerId IN
(Select top 3 customerId
From Customer c2
WHERE Customer.custSegment='Retail'
AND Customer.campaignId='43'
AND Customer.IsActive='1'
And c2.customerId = c1.customerId
Order By YourDate_of_choice)
I think that works?? There is sure to be a neater solution using grouping.
-
I receive the same output as before.
Is there a way just to select the last 3 times a company was called and ignore the rest?
As in:
Campany Start Call End Call
X 2005-03-02 11:27:43.000 2005-03-02 11:31:00.000
X 2005-03-04 09:59:54.000 2005-03-04 10:03:13.000
X 2005-03-07 15:23:58.000 2005-03-07 15:24:59.000
Y 2005-04-01 17:58:18.000 2005-04-01 17:58:51.000
Y2005-04-02 11:35:22.000 2005-04-02 11:35:57.000
Y2005-04-04 11:23:28.000 2005-04-04 11:24:30.000
-
I think he meant something like this :
...
From Customer c1
...
WHERE Customer.custSegment='Retail'
AND Customer.campaignId='43'
AND Customer.IsActive='1'
AND CampBaseRep.Owner='1'
And Customer.StartCall IN
(Select top 3 StartCall
From Customer c2
WHERE Customer.custSegment='Retail'
AND Customer.campaignId='43'
AND Customer.IsActive='1'
And c2.customerId = c1.customerId
Order By StartCall desc)
-
Got it..Works great! Thanks for your help.
Marty
-
The only problem with that query is that you might have a tie... or multiple calls at the same miliseconds so once in a while you might actually get the top 4-5 calls, but unless you have thousands of calls per hours I don't see that happening very often (you're using datetime right > not smalldatetime).
-
Re: smalldatetime (just relooked at the date
). Anyways with that little calls I wouldn't expect it to be a real big problem. -
A tie is no real problem when SELECTing the TOP 3 dates for processing in the IN clause anyway.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Are you sure??
if the dates are like this :
X 2005-03-02 11:27:43.000 2005-03-02 11:31:00.000
X 2005-03-04 09:59:54.000 2005-03-04 10:03:13.000
X 2005-03-07 15:23:58.000 2005-03-07 15:24:59.000
X 2005-03-07 15:23:58.000 2005-03-07 15:24:59.000
the in clause will be like this : ('2005-03-02 11:27:43.000' ,'2005-03-04 09:59:54.000', '2005-03-07 15:23:58.000')
and as far as I can tell there are four rows that match this condition
. -
Oops, let's try it like this
(order by date DESC).X 2005-03-02 11:27:43.000 2005-03-02 11:31:00.000
X 2005-03-02 11:27:43.000 2005-03-02 11:31:00.000
X 2005-03-04 09:59:54.000 2005-03-04 10:03:13.000
X 2005-03-07 15:23:58.000 2005-03-07 15:24:59.000
-
err yes...???
SELECT something FROM somewhere WHERE
somedate IN ( ('2005-03-02 11:27:43.000' ,'2005-03-04 09:59:54.000', '2005-03-07 15:23:58.000')
Who needs the forth row here?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Nobody... that's why I warned him it could happen but I don't see that happening really often. It takes a tie at the third rank to cause this behavior... and with the sample data I don't see that hapenning too often.
-
Remi,
Thanks for cleaning up my error. I don't think I was quite awake when I made that post
-
... must have been a typo
. -
Remi, a tie at any rank in the IN clause doesn't affect the resultset. Try this
set nocount on
use northwind
select
t1.CustomerID
, t1.OrderDate
from
orders t1
where
t1.OrderDate in
(
select top 2 with ties
t2.OrderDate
from
orders t2
where
t2.CustomerID = t1.CustomerID
order by
t2.OrderDate desc
)
order by
t1.CustomerID
, t1.OrderDate desc
set nocount off
with and without the tie in TOP 2. You notice that there is no difference in the resultset. You should also find that in both cases the customerID SAVEA appears 3x in the resultset, because he ordered two times at one day.
There's really no advantage having an IN clause like WHERE...IN(1,1,2,3) compared to WHERE ... IN (1,2,3). Or WHERE ...IN(1,2,3,3) compared to WHERE...IN (1,2,3).
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply