Nested IIF Statement

R

reddy

I need to accomplish the following. I need to count the number of loans by
Lender that fall under three categories: FlatCancel, PartialCancel & Inforce.
Here is a sample of the data

Lender Loan# EffectiveDt CancelDt
BLC 12 1/1/2008 1/1/2008
BLC 45 1/1/2008 4/03/2008
BLC 65 1/1/2008 Null

If the EffectiveDt=CancelDt then "FlatCancel" (Row1)
If the EffectiveDt<>CancelDt AND CancelDt is NotNull then "PartialCancel"
(Row2)
If the EffectiveDt is Null then "Inforce" (Row3)

The query should return:
Lender, IssueMth , IssueYr , #FlatCancels ,#PartialCancels, #Inforce
 
D

Douglas J. Steele

Assuming that IssueMth and IssueYr are based on EffectiveDt:

SELECT Lender, Month([EffectiveDt]), Year([EffectiveDt]),
SUM(IIf([EffectiveDt] = [CancelDt], 1, 0) AS [#FlatCancels],
SUM(IIf([EffectiveDt] <> Nz([CancelDt], [EffectiveDt]), 1, 0) AS
[#PartialCancels],
SUM(IIf(IsNull([CancelDt]), 1, 0) AS [#Inforce]
GROUP BY Lender, Month([EffectiveDt]), Year([EffectiveDt])

(I assume you made a typo in your definition of Inforce, since your
description doesn't match row 3 of your example)
 
K

KARL DEWEY

I think you mean 'If the CancelDt is Null then "Inforce".'
Try this --
SELECT reddy.Lender, Format([EffectiveDt],"mmmm") AS IssueMth,
Format([EffectiveDt],"yyyy") AS IssueYr,
Sum(IIf([EffectiveDt]=[CancelDt],1,0)) AS FlatCancel,
Sum(IIf([EffectiveDt]<>[CancelDt] And [CancelDt] Is Not Null,1,0)) AS
PartialCancel, Sum(IIf([CancelDt] Is Null,1,0)) AS Inforce
FROM reddy
GROUP BY reddy.Lender, Format([EffectiveDt],"mmmm"),
Format([EffectiveDt],"yyyy");
 
M

MGFoster

reddy said:
I need to accomplish the following. I need to count the number of loans by
Lender that fall under three categories: FlatCancel, PartialCancel & Inforce.
Here is a sample of the data

Lender Loan# EffectiveDt CancelDt
BLC 12 1/1/2008 1/1/2008
BLC 45 1/1/2008 4/03/2008
BLC 65 1/1/2008 Null

If the EffectiveDt=CancelDt then "FlatCancel" (Row1)
If the EffectiveDt<>CancelDt AND CancelDt is NotNull then "PartialCancel"
(Row2)
If the EffectiveDt is Null then "Inforce" (Row3)

The query should return:
Lender, IssueMth , IssueYr , #FlatCancels ,#PartialCancels, #Inforce
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need nested IIf() functions, just one IIf() function per #:

SELECT Lender, IssueMth, IssueYr,
SUM(IIf(EffectiveDt=CancelDt, 1,0)) As FlatCancels,
SUM(IIf(EffectiveDt<>CancelDT AND CancelDT NOT NULL,1,0)) As
PartialCancels,
SUM(IIf(EffectiveDt IS NULL,1,0)) As Inforce
FROM table_name
WHERE <criteria>
GROUP BY Lender, IssueMth, IssueYr
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSQjgE4echKqOuFEgEQIBUwCgreJWT2DHzfV+sTOXScgR7qC6k9YAoPZt
VaCy0ZUppN7iuwXuL3uEPYa9
=PoG3
-----END PGP SIGNATURE-----
 
R

reddy

Thank you . That worked! How can I add a column that computes an age group -
categorizing the difference between 2 date fields into 0-30,31-60, >60.

I am trying the following and it does'nt work:

Iif([Date1]-[Date2] between 0 and 30,"0-30",
IIF(([Date1]-[Date2] between 31 and 60,"31-60",">60"))

Please help!
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The Age formula is:

Year(Date())-Year(birthdatefield) + (DateSerial(Year(Date()),
Month(birthdatefield), Day(birthdatefield))>Date())

To get the age ranges you can use the Partition() function to get a
"from" "to" value, or you can use the IIf() function w/ the Age formula
as you were doing in your example.

Partition(integer number, start, end, interval)

To get ages between 0 and >60 it would be:

Partition(Age formula, 0, 60, 10)

The output would be like this:
0:9
10:19
30:39 <-- there were no ages in the 20-29 range
61: <-- this means >60

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSQo+FYechKqOuFEgEQJy+wCgsXOyje//TY58uNbClrHpwDB4eT4An3dF
9jFwVnAmt3WzSB/vRrlla7iX
=Ulwa
-----END PGP SIGNATURE-----

Thank you . That worked! How can I add a column that computes an age group -
categorizing the difference between 2 date fields into 0-30,31-60, >60.

I am trying the following and it does'nt work:

Iif([Date1]-[Date2] between 0 and 30,"0-30",
IIF(([Date1]-[Date2] between 31 and 60,"31-60",">60"))

Please help!

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need nested IIf() functions, just one IIf() function per #:

SELECT Lender, IssueMth, IssueYr,
SUM(IIf(EffectiveDt=CancelDt, 1,0)) As FlatCancels,
SUM(IIf(EffectiveDt<>CancelDT AND CancelDT NOT NULL,1,0)) As
PartialCancels,
SUM(IIf(EffectiveDt IS NULL,1,0)) As Inforce
FROM table_name
WHERE <criteria>
GROUP BY Lender, IssueMth, IssueYr
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSQjgE4echKqOuFEgEQIBUwCgreJWT2DHzfV+sTOXScgR7qC6k9YAoPZt
VaCy0ZUppN7iuwXuL3uEPYa9
=PoG3
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
Top