December 11, 2015 at 5:50 am
Hi below is the requirement
Main Filter condition in WHERE clause
If Entity,
Where Effective Payment Date = current date
Else
Where Effective Payment Date = N days + current date
To calculate Effective Payment Date used in WHERE CLAUSE above
If Non Entity,
Effective Payment Date = CashDate
If Entity,
Effective Payment Date = CashDate - DelayDays
To identify Entity/Non Entity use below mapping
Combination of GROUP and TYPE will identify the Entity/Non Entity
ARM XXX Entity
CMO JJJ Entity
MBS XXX Entity
ABS JJJ Entity
Not in Above Not in Above Non Entity
CASE statement for calculating Effective Payment Date
CASE
WHEN (Group = 'ARM' AND Type = 'XXX')
OR (Group = 'CMO' AND Type = 'JJJ')
OR (Group = 'MBS' AND Type = 'XXX')
OR (Group = 'ABS' AND Type = 'JJJ')
THEN DATEADD(DAY,-DelayDays,CashDate)
ELSE CashDate
END
after getting this effective date i will again write a Outer CASE statement in the WHERE clause to implement the logic. the code become complex
Is there any better way to implement this
Thanks
December 11, 2015 at 6:15 am
A test harness with DDL etc would be useful.
As this is a database why not create:
CREATE TABLE Entities
(
[Group] char(3) NOT NULL
,[Type] char(3) NOT NULL
CONSTRAINT PK_Entities PRIMARY KEY ([Group], [Type])
);
INSERT INTO Entities
VALUES ('ARM', 'XXX')
,('CMO', 'JJJ')
,('MBS', 'XXX')
,('ABS', 'JJJ');
your code then becomes:
SELECT ...
,CASE
WHEN E.[Group] IS NULL
THEN CashDate
ELSE DATEADD(day, -DelayDays, CashDate)
END AS EPD
FROM YourTable Y
LEFT JOIN Entities E
ON Y.[Group] = E.[Group]
AND Y.[Type] = E.[Type];
or to save repeating the logic in the WHERE clause:
SELECT A.EPD
FROM YourTable Y
LEFT JOIN #Entities E
ON Y.[Group] = E.[Group]
AND Y.[Type] = E.[Type]
CROSS APPLY (VALUES (CASE WHEN E.[Group] IS NULL THEN CashDate ELSE DATEADD(day, -DelayDays, CashDate) END)) A (EPD);
December 13, 2015 at 11:31 pm
Ken McKelvey (12/11/2015)
A test harness with DDL etc would be useful.As this is a database why not create:
SELECT A.EPD
FROM YourTable Y
LEFT JOIN #Entities E
ON Y.[Group] = E.[Group]
AND Y.[Type] = E.[Type]
CROSS APPLY (VALUES (CASE WHEN E.[Group] IS NULL THEN CashDate ELSE DATEADD(day, -DelayDays, CashDate) END)) A (EPD);
Thanks for the reply...but the effective payment date is calculated for the where clause not in select statement. Group and type cannot be null(u declared them as PK so that fails IS NULL checking) they may contain value other than those combination mentioned in the post. I need to find the WHERE logic which improve the performance.
December 14, 2015 at 5:19 am
tried this logic will it work
AND (
(
(
(Group = 'ARM' AND Type = 'XXX')
OR (Group = 'CMO' AND Type = 'JJJ')
OR (Group = 'MBS' AND Type = 'XXX')
OR (Group = 'ABS' AND Type = 'JJJ')
)
AND (DATEADD(DAY,-DelayDays,CashDate ) = @currentdate)
)
OR
(
CashDate = (DATEADD(DAY,N,@currentdate)
)
)
December 15, 2015 at 10:37 pm
Hi experts Any help on this!!!
December 16, 2015 at 1:08 am
In fact this boils down to something like
WHERE...
CashDate = dateadd(d, case when <entity> then -DelayDays else "N days" end, currdate)
Check signs, not tested.
Depending on indexes available you may alternatively prefer to have currdate at the left side and CashDate in the dateadd() arguments list.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply