May 22, 2006 at 3:35 pm
I know what I need to accomplish can be taken care of with a subquery, but have thus far been wildly unsuccessful and getting it right. Was wondcering if one of you kynd folks could push me in the right direction.
Here's what I need to do: list brokers that have closed a loan in a specific date range (I'll use parameters) but have not closed any loans in the last 90 days. Naturally, the parameter date range won't include the last 90 days.
So in psuedocode:
SELECT broker
FROM (all brokers who closed in the date range)
WHERE (count of closed loans in last 90 days = 0)
Any help would be much appreciated.
Thanks...Ooogy
May 22, 2006 at 3:37 pm
May 22, 2006 at 3:43 pm
I am not clear with the requirement
Do you need list of broker who has closed a loan in a data range but has not closed in last 90 days.
In other words if you pass the date range within last 90 dates it show return 0 records. If this is the case I think you can achieve this in just a select query
SELECT Distinct Name FROM <TABLENAME>
WHERE
<DateField> BETWEEN (@StartDate,@EndDate)
AND
DATEDIFF(dd,<DateField>,getdate()) > 90
Amit Lohia
May 22, 2006 at 3:45 pm
Yes Amit, your first question is correct; I just need to see which brokers did close in the range but not in the last 90 days.
May 22, 2006 at 3:49 pm
SELECT
Distinct Name
FROM
<TABLENAME>
WHERE
<DateField> BETWEEN (@StartDate,@EndDate)
AND
DATEDIFF(dd,<DateField>,getdate()) > 90
Amit Lohia
May 22, 2006 at 4:03 pm
SELECT T1.Broker
FROM (select Broker from loans
where close_date between <your range>
group by Broker) T1
LEFT JOIN (select Broker from loans
where close_date between <last 90 days>
group by Broker) T2 on T1.Broker = T2.Broker
WHERE T2.Broker IS NULL
_____________
Code for TallyGenerator
May 22, 2006 at 4:09 pm
oops. My query would just ignore the last 90 days
Amit Lohia
May 23, 2006 at 2:53 pm
Sergiy - you're response was perfect. Thanks so much. The query now reads:
SELECT T1.referral_25
FROM (SELECT referral_25
FROM application
WHERE cls_dt BETWEEN '01-01-06' AND '01-05-06'
GROUP BY referral_25) T1 LEFT OUTER JOIN
(SELECT referral_25
FROM application
WHERE cls_dt BETWEEN dateadd(d, - 90, getdate()) AND getdate()
GROUP BY referral_25) T2 ON T1.referral_25 = T2.referral_25
WHERE (T2.referral_25 IS NULL)
Except now I can't replace the dates with "@Start" and "@End". Any ideas why??
May 23, 2006 at 2:57 pm
>>Except now I can't replace the dates with "@Start" and "@End".
It would help if you expanded on "can't".
Are you getting errors ? If so, what are they ?
Or incorrect results ? If so explain ?
What are the datatypes of @Start and @End ? What is the datatype of cls_dt ?
May 23, 2006 at 3:06 pm
Thanks anyhoo. Got it working!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply