January 11, 2007 at 8:23 am
I am having trouble nesting a case statement. We have a request to put 12 months worth of data in one column based on the Year. I don't have a problem getting the first Case to evaluate, I just have a problem getting it to evaluate the whole statement. (I only included the first month here).
What can I do to get it to evaluate the whole criteria?
Thanks
SUM(CASE WHEN 2006 between Convert(char(4),DatePart(yyyy,dtFrom)) AND
Convert(char(4), DatePart(yyyy,dtTo)) THEN
CASE WHEN (dtFrom <= Convert(char(4),iYear) + '-01-01' AND
dtTo >= Convert(char(4),iYear) + '-01-31') THEN
(dRent * (Datediff(Month,Convert(char(4),iYear) + '-01-01',dtTo)))
WHEN (dtTo > Convert(char(4),iYear) + '-01-01' AND
dtTo < Convert(char(4),iYear) + '-01-31') THEN
((dRent / 31) * (Datediff(d,Convert(char(4),iYear) + '-01-01',dtTo) + 1)+
(dRent * (DatePart(month,dtFrom) - DatePart(month,'2100-12-01'))))
WHEN (dtFrom > Convert(char(4),iYear) + '-01-01' AND
dtFrom < Convert(char(4),iYear) + '-01-31') THEN
((dRent / 31) * (Datediff(d,dtFrom,Convert(char(4),iYear) + '-01-31') + 1) +
dRent * (DatePart(month,'2100-12-01') - DatePart(month,dtFrom)))
WHEN (dtFrom > Convert(char(4),iYear) + '-01-01' AND
dtTo < Convert(char(4),iYear) + '-12-31') THEN
(dRent / 30) * (DatePart(day,dtTo) - DatePart(day,'2100-12-01')+1)
ELSE 0 END ELSE 0 END)
January 11, 2007 at 9:12 am
Can you post some sample data and the required results so we can know what you need exactly (and point you in the right direction). Table DDL could be very usefull too so we can post the final solution right away.
January 11, 2007 at 9:57 am
Sure, sorry about that.
Sample Data would be:
Tenant abc has lease charges of 4571.81 from 01/01/2006 - 11/21/06.
This same tenant has a new lease charge of 4652.01 beginning on 11/22/06 and Ending on 12/31/06. This rate of 4652.01 runs thru 11/21/07 and then begins a new rate on 11/22/07, and so on.
This data is pulled in from a SQL view that actually pulls the information from the tenant table and a lease charge table in this format.
The report would look like this:
Tenant Actual 2006 Actual 2007 etc
ABC 54388.19 56008.00
The Excel calculation for 2006 column is (21/30 * 4517.81)+ (4517.81*10)+(4652.01*1) + (9/30 * 4652.01). The 21 is for 21 days out of 30 (Nov) at the rent charge of 4517.81, the 10 is for 10 months at the 4517.81, the 1 is 1 complete month at 4652.01 and the 9 is for 9 days out of 30(Nov) at the new rate of 4652.01. Basically it is prorating for the split month.
Hope this makes some sense.
January 12, 2007 at 9:11 am
I don't see anything obviously wrong that you're doing. It's a complicated calculation. It's possible that one or more implicit date conversions aren't yielding the correct date or even data type. That may be specific to the date settings on your sever. I'd suggest breaking this statement into smaller parts (for testing) and trying each part. Test each condition like this:
SELECT CASE WHEN test_condition THEN 1 ELSE 0 END FROM ...
Test each possible result of the case expression:
SELECT possible_result_calculation FROM ...
Make sure that each subset of your CASE expression works by itself. You will likely find the problem this way.
January 12, 2007 at 10:33 am
Thanks for your help. I am pretty sure my problem lies within the fact that the tenant I am looking at has a different rent amount in the 11th month instead of the 1st month. It still however, pulls the data from the 1st month and doubles it up.
I will keep testing. Thanks
January 12, 2007 at 2:49 pm
Your query is too complicated to read and debug.
My first suggestion is to add a derived table subquery to define the common expressions so you don't have to repeat the same CONVERTS all over the place. If you choose meaningful variable names the main query becomes much more readable.
SELECT
SUM(CASE WHEN 2006 between YEAR(dtFrom) AND YEAR(dtTo) THEN
CASE WHEN (dtFrom <= iYear0101 AND dtTo >= iYear0131) THEN (dRent * (DATEDIFF(Month,iYear0101,dtTo)))
WHEN (dtTo > iYear0101 AND dtTo < iYear0131) THEN ((dRent / 31) * (DATEDIFF(d,iYear0101,dtTo) + 1)+(dRent * (DATEPART(month,dtFrom) - 12)))
WHEN (dtFrom > iYear0101 AND dtFrom < iYear0131) THEN ((dRent / 31) * (DATEDIFF(d,dtFrom,iYear0131) + 1) + dRent * (12 - DATEPART(month,dtFrom)))
WHEN (dtFrom > iYear0101 AND dtTo < iYear1231) THEN (dRent / 30) * DATEPART(day,dtTo)
ELSE 0
END
ELSE 0
END)
FROM (
SELECT dtFrom, dtTo, dRent,
DATEADD(year,iYear-2000,'2000-01-01') AS iYear0101,
DATEADD(year,iYear-2000,'2000-01-31') AS iYear0131,
DATEADD(year,iYear-2000,'2000-12-31') AS iYear1231
FROM (
...
At this point I thought it was more readable and I think the problem is that each clause of the inner CASE only computes part of the answer. It computes the sum for every rent record for each tenant, but it doesn't add the pieces (whole months plus prorated) that you can get from one record.
Being unable to figure out how you use your query, I tried starting from scratch. It sounds like you need three calculations: one for the whole months, one for a partial month at the beginning of the range, and one for a partial month at the end. The extra fields you need in the derived table subquery are the number of days in the first and last months, and flags that indicate whether the first or last month must be prorated. It's way too complicated to debug if you lump it all together, so I wrote it to split these out as separate columns and not group the rows. It seems to match your Excel calculation. If you can convince yourself it works, then add the three columns together and sum by year and tenant. Once that works, you can wrap it in a query that pivots the sums for each year into one row per tenant.
SELECT
iYear, RentID, dtFrom, dtTo, dRent,
CASE WHEN FromPartialMonth=1 THEN dRent * (FromMonthLength + 1 - DAY(dtFrom)) / FromMonthLength ELSE 0 END AS FirstPartialMonth,
dRent * (CASE WHEN iYear < YEAR(dtTo) THEN 12 ELSE MONTH(dtTo) END - ToPartialMonth -
(CASE WHEN iYear > YEAR(dtFrom) THEN 0 ELSE MONTH(dtFrom) - 1 + FromPartialMonth END)) AS FullMonths,
CASE WHEN ToPartialMonth=1 THEN dRent * DAY(dtTo) / ToMonthLength ELSE 0 END AS LastPartialMonth
FROM (
SELECT iYear, RentID, dtFrom, dtTo, dRent,
CASE WHEN YEAR(dtFrom) < iYear OR MONTH(dtFrom) <> MONTH(dtFrom-1) THEN 0 ELSE 1 END as FromPartialMonth,
DATEPART(day, cast(convert(char(8), DATEADD(month,1,dtFrom), 111) + '01' as smalldatetime)-1) AS FromMonthLength,
CASE WHEN YEAR(dtTo) > iYear OR MONTH(dtTo) <> MONTH(dtTo+1) THEN 0 ELSE 1 END as ToPartialMonth,
DATEPART(day, cast((convert(char(8), DATEADD(month,1,dtTo), 111) + '01') as smalldatetime)-1) AS ToMonthLength
FROM (
SELECT 1 AS RentID, CAST('2006-01-01' AS smalldatetime) AS dtFrom, CAST('2006-11-21' AS smalldatetime) AS dtTo, CAST(4517.81 AS money) as dRent
UNION ALL SELECT 2 AS RentID, CAST('2006-11-22' AS smalldatetime) AS dtFrom, CAST('2007-11-21' AS smalldatetime) AS dtTo, CAST(4652.01 AS money) as dRent
UNION ALL SELECT 3 AS RentID, CAST('2007-11-22' AS smalldatetime) AS dtFrom, CAST('2008-11-21' AS smalldatetime) AS dtTo, CAST(4734.61 AS money) as dRent
) AS rents
CROSS JOIN (
SELECT 2006 AS iYear
UNION ALL SELECT 2007 AS iYear
UNION ALL SELECT 2008 AS iYear
) years
) mess
WHERE iYear BETWEEN year(dtFrom) AND year(dtTo)
January 15, 2007 at 7:20 am
Thanks for your suggestions. I understand what you mean about breaking it down, however, I am confused by the following section.
SELECT 1 AS RentID, CAST('2006-01-01' AS smalldatetime) AS dtFrom, CAST('2006-11-21' AS smalldatetime) AS dtTo, CAST(4517.81 AS money) as dRent
UNION ALL SELECT 2 AS RentID, CAST('2006-11-22' AS smalldatetime) AS dtFrom, CAST('2007-11-21' AS smalldatetime) AS dtTo, CAST(4652.01 AS money) as dRent
UNION ALL SELECT 3 AS RentID, CAST('2007-11-22' AS smalldatetime) AS dtFrom, CAST('2008-11-21' AS smalldatetime) AS dtTo, CAST(4734.61 AS money) as dRent
) AS rents
CROSS JOIN (
SELECT 2006 AS iYear
UNION ALL SELECT 2007 AS iYear
UNION ALL SELECT 2008 AS iYear
) years
) mess
It looks to me like you are hardcoding the rent amounts and the dates. These will not be constant, there can be many variations.
Am I not understanding this correctly?
Thanks
January 15, 2007 at 7:30 am
He is just using your sample data. He is not hardcoding anything.
PS Sorry but I haven't had time to resink myself into this one. I can solve it but it takes time and concentration which I'm a bit short on at this point.
January 15, 2007 at 7:56 am
O.K. So where he has hardcoded the dates and amounts, I will put the same field name?
It would look like this
CAST(dtFrom AS smalldatetime) AS dtFrom, CAST(dtTo AS smalldatetime) AS dtTo, CAST(dRent AS money) as dRent
UNION ALL SELECT 2 AS RentID, CAST(dtFrom AS smalldatetime) AS dtFrom, CAST(dtTo AS smalldatetime) AS dtTo, CAST(dRent AS money) as dRent
UNION ALL SELECT 3 AS RentID, CAST(dtFrom AS smalldatetime) AS dtFrom, CAST(dtTo AS smalldatetime) AS dtTo, CAST(dRent AS money) as dRent
I guess I just don't understand how the query knows where the new record starts.
January 15, 2007 at 9:05 am
FROM (
SELECT 1 AS RentID, CAST('2006-01-01' AS smalldatetime) AS dtFrom, CAST('2006-11-21' AS smalldatetime) AS dtTo, CAST(4517.81 AS money) as dRent
UNION ALL SELECT 2 AS RentID, CAST('2006-11-22' AS smalldatetime) AS dtFrom, CAST('2007-11-21' AS smalldatetime) AS dtTo, CAST(4652.01 AS money) as dRent
UNION ALL SELECT 3 AS RentID, CAST('2007-11-22' AS smalldatetime) AS dtFrom, CAST('2008-11-21' AS smalldatetime) AS dtTo, CAST(4734.61 AS money) as dRent
) AS rents
All that is where you would use your actual data from the base table. That part is used as sample data to show how the rest of the query should look like and work.
January 15, 2007 at 9:18 am
That whole subquery is just to generate test data. Each of the UNIONed SELECT statements generates one record.
You would replace FROM (...) as rents with FROM <renttable>, where <renttable> is the table containing dtFrom, dtTo, and dRent. I threw in RentID just for debugging, so you can verify exactly what amounts are coming from each record.
The other subquery is generating iYear values, because it wasn't clear to me in the original post where that came from. If you wanted a fixed number of years in your report, it could be something like this:
FROM (SELECT DISTINCT TOP 5 YEAR(dtTo) as iYear FROM <renttable> ORDER BY iYear DESC) years
January 15, 2007 at 9:26 am
Thanks so much! It makes sense now.
January 16, 2007 at 8:40 am
I have tested the statement you suggested. I am getting a syntax error at the line ) years ).
It looks o.k. to me. Am I missing something?
Thanks
SELECT SUM(CASE WHEN 2006 between Year(dtFrom) and Year(dtTo) THEN
CASE WHEN (dtFrom <= iYear0101 and dtTo >= iYear0131) THEN (dRent * (DateDiff(Month,iYear0101, dtTo)))
WHEN (dtTo > iYear0101 and dtTo < iYear0131) THEN ((dRent/31) * (DateDiff(d,iYear0101,dtTo) + 1) +
(dRent * (DATEPART(month,dtFrom)-12)))
WHEN (dtFrom > iYear0101 and dtFrom < iYear0131) THEN ((dRent/31) * (DATEDIFF(d, dtFrom,iYear0131) + 1) + dRent *
(12 - DATEPART(month,dtFrom)))
WHEN (dtFrom > iYear0101 AND dtTo < iYear1231) THEN (dRent/30) * DATEPART(day,dtTo)
ELSE 0
END
ELSE 0
END)
FROM(SELECT dtFrom, dtTo, dRent,
DATEADD(year, iYear-2000, '2000-01-01') as iYear0101,
DATEADD(year, iYear-2000, '2000-01-31') as iYear0131,
DATEADD(year, iYear-2000, '2000-12-31') as iYear1231
FROM(
SELECT iYear, RentID, dtFrom, dtTo, dRent,
CASE WHEN FromPartialMonth=1 then dRent * (FromMonthLength + 1 - Day(dtFrom))/FromMonthLength ELSE 0 END as FirstPartialMonth,
dRent * (CASE WHEN iYear < Year(dtTo) Then 12 Else Month(dtTo) END - ToPartialMonth - (CASE WHEN iYear > Year(dtFrom) THEN 0 ELSE
Month(dtFrom) - 1 + FromPartialMonth END)) AS FullMonths,
CASE WHEN ToPartialMonth = 1 THEN dRent * Day(dtTo)/ToMonthLength ELSE 0 END as LastPartialMonth
FROM(SELECT iYear, RentID, DtFrom, dtTo, dRent,
CASE WHEN Year(dtFrom) < iYear OR MONTH (dtFrom) <> Month(dtFrom-1) THEN 0 ELSE 1 END as FromPartialMonth,
DATEPART(day,cast(convert(char(8), DATEADD(month,1,dtFrom),111) + '01' as smalldatetime)-1) AS FromMonthLength,
CASE WHEN Year(dtTo) > iYear OR MONTH(dtTo) <> MONTH(dtTo+1) THEN 0 ELSE 1 END AS ToPartialMonth,
DATEPART(day,cast((convert(char(8), DATEADD(MONTH,1,dtTo),111) + '01') as smalldatetime)-1) AS ToMonthLength
FROM(SELECT 1 as RentID, CAST('2006-01-01' as smalldatetime) AS dtFrom, CAST('2006-11-21' as smalldatetime)as dtTo,
CAST(4517.81 as Money) as dRent
UNION ALL SELECT 2 as RentID,CAST('2006-11-22' as Smalldatetime) as dtFrom, CAST('2007-11-21' as smalldatetime) as dtTo,
CAST(4652.01 as Money) as dRent
UNION ALL SELECT 3 as RentId, CAST('2007-11-22' as smalldatetime) as dtFrom, CAST('2008-11-21' as smalldatetime) as dtTo,
CAST(4734.61 as Money) as dRent
) as Rents
CROSS JOIN (
SELECT 2006 as iYear
UNION ALL SELECT 2007 as iyear
UNION ALL SELECT 2008 as iYear
) years
)
FROM NTS_Comm_ProjRent (nolock)
WHERE iYear >= 2006
and chsCode in ('rent: Base Rent','conc: Rent Concession')
and pscode = 'Lakeshore Business Center III (lakbus3)'
and uscode = '100'
GROUP BY psCode, iYear, chsCode, usCode, tsCode, lsedate, amtdate, months, dRent, dtFrom, dtTo
January 16, 2007 at 11:46 am
The quick answer is that you left out an alias. Any time you use a derived table subquery you have to name it, so it should look like:
)
years
) DerivedTableAlias
Then you start another FROM clause, which is going to be your next syntax error.
Reread my first response. The first query was an attempt to make your query more readable (at least to me). Once I could read it, I realized that your nested CASE statements would only use one value from each rent record when you need to sum all three (prorated first month, whole months, and prorated last month). So I scrapped the first approach and did something different in the second query, but you weren't supposed to try to merge them.
The first question is whether the query I gave you with your sample data produces the correct results.
iYear RentID dtFrom dtTo dRent FirstMonth FullMonths LastMonth
----- ------ ---------- ---------- ------ ---------- ---------- ---------
2006 1 2006-01-01 2006-11-21 4517.81 0.00 45178.10 3162.467
2006 2 2006-11-22 2007-11-21 4652.01 1395.603 4652.01 0.00
2007 2 2006-11-22 2007-11-21 4652.01 0.00 46520.10 3256.407
2007 3 2007-11-22 2008-11-21 4734.61 1420.383 4734.61 0.00
2008 3 2007-11-22 2008-11-21 4734.61 0.00 47346.10 3314.227
You can compare $45178.10 + $3162.47 + $1395.60 + $4652.01 = $54388.18 to your Excel calculation to see if it is correct. Convince yourself that each partial result is correct before you start summing them.
Then the next step is to add the three partial results together to get one subtotal for each rent record. After that you can group by year and use SUM on the subtotals. Then take out all the test data queries when you put in the real table. I am pretty certain you don't want dRent, dtFrom, or dtTo in the GROUP BY clause, I don't know about the other fields. This query should be close to what you need:
SELECT
psCode, chsCode, tsCode, iYear,
SUM(CASE WHEN FromPartialMonth=1 THEN dRent * (FromMonthLength + 1 - DAY(dtFrom)) / FromMonthLength ELSE 0 END +
dRent * (CASE WHEN iYear < YEAR(dtTo) THEN 12 ELSE MONTH(dtTo) END - ToPartialMonth -
(CASE WHEN iYear > YEAR(dtFrom) THEN 0 ELSE MONTH(dtFrom) - 1 + FromPartialMonth END)) +
CASE WHEN ToPartialMonth=1 THEN dRent * DAY(dtTo) / ToMonthLength ELSE 0 END) AS TotalRent
FROM (
SELECT psCode, chsCode, tsCode, iYear, dtFrom, dtTo, dRent,
CASE WHEN YEAR(dtFrom) < iYear OR MONTH(dtFrom) <> MONTH(dtFrom-1) THEN 0 ELSE 1 END as FromPartialMonth,
DATEPART(day, cast(convert(char(8), DATEADD(month,1,dtFrom), 111) + '01' as smalldatetime)-1) AS FromMonthLength,
CASE WHEN YEAR(dtTo) > iYear OR MONTH(dtTo) <> MONTH(dtTo+1) THEN 0 ELSE 1 END as ToPartialMonth,
DATEPART(day, cast((convert(char(8), DATEADD(month,1,dtTo), 111) + '01') as smalldatetime)-1) AS ToMonthLength
FROM NTS_Comm_ProjRent (nolock)
WHERE iYear >= 2006
and iYear BETWEEN year(dtFrom) AND year(dtTo)
and chsCode in ('rent: Base Rent','conc: Rent Concession')
and pscode = 'Lakeshore Business Center III (lakbus3)'
and uscode = '100'
) PayAttentionTammy
GROUP BY psCode, chsCode, tsCode, iYear
January 16, 2007 at 12:54 pm
Thanks for your assistance, but you don't have to be smart.
The way you worded it in your breakdown looked to me like you were merging them.
Sorry if I misunderstood you.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply