August 13, 2008 at 1:54 pm
I have the following derived table (from a much larger SELECT statement):
SELECT t.HPROPERTY AS HPROP,
CASE WHEN t.dtleasefrom BETWEEN s.begdate AND s.enddate AND t.dtrenewdate IS NULL
AND ISNULL(p.dtapply, t.dtsigndate) IN (SELECT *
FROM dbo.GetCalendarTable(@Season,553))
AND ISNULL(l.renewal_transfer, 'No') <> 'Yes'
THEN 'New Leases'
WHEN t.dtleaseto > s.enddate AND t.dtleasefrom <= s.enddate AND t.dtrenewdate
IS NOT NULL AND t.dtrenewdate IN (SELECT *
FROM dbo.GetCalendarTable(@Season,553))
THEN 'Renewals'
.
.
.
You can see I'm calling a function called GetCalendarTable. I have to pass in the season we are projecting for as well as the property identifier. The Season is easy. I have it defined before I even start into the select. The property is the hard part. For testing purposes, I'm using it as follows (hard coding the property code):
SELECT *
FROM dbo.GetCalendarTable(@Season,553)
What I need to do when I call the function is:
SELECT *
FROM dbo.GetCalendarTable(@Season,@Property)
Each time this derived table runs, it needs to run the function for the particular property it is working on at the time. The only way I know of to set the value of the @Property variable in a SELECT statement is by doing something that apparently doesn't work in a derived table:
SELECT @Property = t.HPROPERTY AS HPROP,
.
.
.
Any other ideas would be greatly appreciated.
August 13, 2008 at 2:26 pm
Look up Apply, specifically Cross Apply, in Books Online. That should do what you need.
With that, you can use a value in the main query/table as an input parameter for a function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 6:47 am
Well, I have looked at the documentation but I can't make heads or tails of how a cross apply would work in this particular case. If you look at the first code snippet, you'll see how I'm using the results from the function - in the WHEN part of the CASE statement. Incidentally, the first field of my SELECT statement (t.HPROPERTY) is what I need to pass to the function, replacing the 553 hard coded in the function call.
August 14, 2008 at 7:56 am
Well, this isn't exactly what I wanted to do since it doesn't seem to be as clean as I would like but...
I took the statement from the function and put it directly in my SELECT as a Correlated Subquery:
SELECT t.HPROPERTY AS HPROP,
CASE WHEN t.dtleasefrom BETWEEN s.begdate AND s.enddate AND t.dtrenewdate IS NULL
AND ISNULL(p.dtapply, t.dtsigndate) IN
(SELECT CalendarDate
FROM (SELECT CONVERT(SMALLDATETIME, CONVERT(VARCHAR(4), YEAR(DATEADD(yy, -1, begdate)))
+ '0901') +
N3.NUM * 100 +
N2.NUM * 10 +
N1.NUM AS CalendarDate,
begdate
FROM (SELECT 0 AS NUM UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) AS N1,
(SELECT 0 AS NUM UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) AS N2,
(SELECT 0 AS NUM UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS N3,
SEASONS
WHERE (hprop = t.HPROPERTY AND shortdesc = @season)) GenCalendar
WHERE CalendarDate < CONVERT(SMALLDATETIME, CONVERT(VARCHAR(4), YEAR(begdate)) + '0901'))
AND ISNULL(l.renewal_transfer, 'No') <> 'Yes'
THEN 'New Leases'
WHEN t.dtleaseto > s.enddate AND t.dtleasefrom <= s.enddate AND t.dtrenewdate
IS NOT NULL AND t.dtrenewdate IN (SELECT *
FROM dbo.GetCalendarTable(@Season,553))
THEN 'Renewals'
This allows me to use the outer table's t.HPROPERTY value as I need.
Of course, if someone knows how to make the other way work, I'm all eyes.
August 17, 2008 at 11:43 pm
I think that one of the methods you could use to make this code a lot easier to work with is to write out just exactly what it is doing in plain English statements. For example:
CASE WHEN t.dtleasefrom BETWEEN s.begdate AND s.enddate AND t.dtrenewdate IS NULL
AND ISNULL(p.dtapply, t.dtsigndate) IN ...
could be written "when the lease date lies within the time period specified by the begin and end date, the renewal date is null and the apply date (or the sign date if apply date is null) is within the same fiscal year as the begin date..."
One of the advantages of doing that is you should see immediately that to check if a date is within the same year as another date, there is no reason to generate a list of 365 sequential dates and use the IN operator. Just generate the date of the first day of the target fiscal year and the date of the first day of the next fiscal year and use BETWEEN.
Provide an English explanation of every parameter and column used in your query and I'm sure you will be able to very much simplify the query. Post it here and we will help, if you wish, but just the effort of doing it will allow you to answer many of your own questions.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply