October 31, 2014 at 11:20 am
I have 3 Views that use the Over Partition By to supply the latest date.
Date1 is the Permit Application Submit Dt, Date2 is the Permit Approved Submit Date, Date3 is the Permit Approved Expire Date, and Date4 is a Permit Returned Date (if it was rejected or returned).
Of the 3 Views - I need to look at the dates and decide what happened last.
In this case, the latest date Trumps - but we don't want to return the date itself.
We need to know what of the 3 views Trumped the others.
- One more thing. If the Date2 is the latest date, there is Date3 that has the Expire date.
So, a different return value would indicate the latest thing was an approved Permit - but the Expire Date has passed (today's date).
This has not been added to the function below.
The number of records processed would be about 1 at a time.
In a full report, the largest number ever would be 30,000. It isn't millions.
This is as far as I got. Can someone look it over and give me suggestions?
Each date(1,2,3,4) can be a date, null or empty string.
USE [MyProduction DB]
GO
/****** Object: UserDefinedFunction [dbo].[VRE_APD_Trump] Script Date: 10/31/2014 09:01:26 ******/
Use RegulatoryDB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 4 dates are entered. Any date can be a date or Null (possible empty string)
-- Date 1 - SubmitDt, Date 2 - ApprovedDt, Date 3 - Expire Dt, Date 4 - ReturnDt
-- If Date 3 (permit expired date) has expired as of today, then Date 2 returns Expired Permit (ExpAPD).
-- What Date Trumps? A query has selected the Last Date for each Date 1, 2, 3 Any or all can be null
-- What is the last Date? After the Date 2 Date 3 condition, what date is the latest?
-- Return Values: Null, Submit, Approv, ExpAPD (Expired Permit), Return indicates Permit Submitted, Permit Approved, Permit Returned (return, cancled, rejected...)
-- SQL 2008R2 (no IIF statement allowed)
-- CrossApply picks date, but can't identify what column
-- Date1 Application submitted Dt, Date2 Permit submitted Dt for Approved, Date3 Approved Permit Expired Dt, Date4 Date a Permit was rejected (returned)
CREATE FUNCTION [dbo].[VRE_APD_Trump](@Date1 DATETIME2(7), @Date2 DATETIME2(7), @Date3 DATETIME2(7), @date4 DATETIME2(7))
RETURNS VARCHAR(6)
AS
BEGIN
Declare @timeNow DATETIME2(7) ;
--DECLARE @Result1 as int;
DECLARE @Result2 as VARCHAR(6); -- Business Condition returned
--set @timeNow = = SYSDATETIME() -- gets error! why?
set @Result2 =
-- Just trying Date 1,2,4 (will evaluate Date 2,3 next)
CASE when date1 >= date2
and date1 >= coalesce(date4, date1)
or date1 >= date4
and date1 >= coalesce(date2, date1)
or date1 is not null
and date2 is null and date4 is null
then 'Submit'
when date2 >= date1
and date2 >= coalesce(date4, date2)
or date2 >= date4
and date2 >= coalesce(date1, date2)
or date2 is not null
and date1 is null and date4 is null
then 'Approv'
when date3 >= date1
and date3 >= coalesce(date2, date4)
or date3 >= date2
and date3 >= coalesce(date1, date4)
or date3 is not null
and date1 is null and date2 is null
then 'Return'
END
return @result2
END;
GO
October 31, 2014 at 12:42 pm
I don't think I'd do it in a function because I think it'll be slower, here's an example where I think I have the logic you want correct and I think you should be able to transfer it to your function:
DECLARE @dates TABLE
(
SubmitDate DATETIME,
ApprovedDate DATETIME,
ExpirationDate DATETIME,
ReturnedDate DATETIME
);
INSERT INTO @dates
(SubmitDate, ApprovedDate, ExpirationDate, ReturnedDate)
VALUES
/* Should be Submit */
(GETDATE(), -- SubmitDate - datetime
NULL, -- ApprovedDate - datetime
NULL, -- ExpirationDate - datetime
NULL -- ReturnedDate - datetime
),
(GETDATE(), -- SubmitDate - datetime
'', -- ApprovedDate - datetime
NULL, -- ExpirationDate - datetime
NULL -- ReturnedDate - datetime
),
/* Should be Returned */
(GETDATE(), -- SubmitDate - datetime
GETDATE() + 1, -- ApprovedDate - datetime
GETDATE() + 2, -- ExpirationDate - datetime
GETDATE() + 3 -- ReturnedDate - datetime
),
/* Should be Approved */
(GETDATE(), -- SubmitDate - datetime
GETDATE() + 1, -- ApprovedDate - datetime
NULL, -- ExpirationDate - datetime
NULL -- ReturnedDate - datetime
),
/* Should be Expired */
(GETDATE() - 1, -- SubmitDate - datetime
GETDATE(), -- ApprovedDate - datetime
GETDATE() + 2, -- ExpirationDate - datetime
NULL -- ReturnedDate - datetime
),
(GETDATE(), -- SubmitDate - datetime
'', -- ApprovedDate - datetime
GETDATE() + 2, -- ExpirationDate - datetime
GETDATE() + 1 -- ReturnedDate - datetime
),
/* Should be Approved but Expired */
(GETDATE() - 1, -- SubmitDate - datetime
GETDATE(), -- ApprovedDate - datetime
GETDATE() - 2, -- ExpirationDate - datetime
NULL -- ReturnedDate - datetime
),
(GETDATE() - 1, -- SubmitDate - datetime
GETDATE() + 3, -- ApprovedDate - datetime
GETDATE() + 2, -- ExpirationDate - datetime
NULL -- ReturnedDate - datetime
),
(NULL, -- SubmitDate - datetime
GETDATE() + 4, -- ApprovedDate - datetime
GETDATE() + 2, -- ExpirationDate - datetime
NULL -- ReturnedDate - datetime
)
SELECT
*,
CASE WHEN COALESCE(NULLIF(D.ApprovedDate, '19000101'), NULLIF(D.ExpirationDate, '19000101'), NULLIF(D.ReturnedDate, '19000101')) IS NULL OR
D.SubmitDate > D.ApprovedDate AND
D.SubmitDate > D.ExpirationDate AND
D.SubmitDate > D.ReturnedDate THEN 'Submit'
WHEN D.ApprovedDate > ISNULL(D.SubmitDate, '19000101') AND
D.ApprovedDate > ISNULL(D.ReturnedDate, '19000101') AND
D.ApprovedDate > D.ExpirationDate AND D.ExpirationDate IS NOT NULL THEN 'Approved but expired'
WHEN D.ApprovedDate > ISNULL(D.SubmitDate, '19000101') AND
D.ApprovedDate > ISNULL(D.ReturnedDate, '19000101') THEN 'Approved'
WHEN D.ExpirationDate > ISNULL(D.SubmitDate, '19000101') AND
D.ExpirationDate > ISNULL(D.ReturnedDate, '19000101') AND
D.ExpirationDate > ISNULL(D.ApprovedDate, '19000101') THEN 'Expired'
WHEN D.ReturnedDate > ISNULL(D.SubmitDate, '19000101') AND
D.ReturnedDate > ISNULL(D.ApprovedDate, '19000101') AND
D.ReturnedDate > ISNULL(D.ExpirationDate, '19000101') THEN 'Return'
END
FROM
@dates AS D;
If you do use the function please, please don't use @date1, @date2, etc..., use the actual names of what the dates represent.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2014 at 12:56 pm
I think your ANDs and ORs may be getting mixed up somewhere, the logic in each WHEN looks confusing, maybe parenthesis could help? Also, you say there are 3 sources and 4 dates, but I don't see what will happen in the CASEs when date4 is the most recent?
If all you are doing is trying to get the source of the most recent date of the 4 dates, maybe something like:
CREATE FUNCTION dbo.MostRecent(@AppSubmitDate DATETIME2(7), @ApprovedDate DATETIME2(7), @ExpireDate DATETIME2(7), @ReturnedDate DATETIME2(7))
RETURNS TABLE AS
RETURN (
SELECT TOP 1 source
FROM (VALUES ('Submit Date', @AppSubmitDate), ('Approved Date', @ApprovedDate), ('Expire Date', @ExpireDate), ('Returned Date', @ReturnedDate)) AS value(source, val)
ORDER BY val DESC
)
GO
** edit
OK, I just saw Jack Corbet's post, maybe I'm way underestimating what the OP is trying to accomplish :unsure:
October 31, 2014 at 1:21 pm
Chris Harshman (10/31/2014)
I think your ANDs and ORs may be getting mixed up somewhere, the logic in each WHEN looks confusing, maybe parenthesis could help? Also, you say there are 3 sources and 4 dates, but I don't see what will happen in the CASEs when date4 is the most recent?If all you are doing is trying to get the source of the most recent date of the 4 dates, maybe something like:
CREATE FUNCTION dbo.MostRecent(@AppSubmitDate DATETIME2(7), @ApprovedDate DATETIME2(7), @ExpireDate DATETIME2(7), @ReturnedDate DATETIME2(7))RETURNS TABLE AS
RETURN (
SELECT TOP 1 source
FROM (VALUES ('Submit Date', @AppSubmitDate), ('Approved Date', @ApprovedDate), ('Expire Date', @ExpireDate), ('Returned Date', @ReturnedDate)) AS value(source, val)
ORDER BY val DESC
)
GO
** edit
OK, I just saw Jack Corbet's post, maybe I'm way underestimating what the OP is trying to accomplish :unsure:
Chris,
Your post is almost identical to what I originally was going to post, but then I re-read the requirements and a simple max date isn't the desired outcome. There is one instance where the max date is not correct and that is when the ApprovedDate > ExpireDate and ExpireDate is not null, then the OP wants to show Approved, but Expired or something along those lines.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 3, 2014 at 9:48 am
All of this really helped. Thanks!
Thanks so much. I am overwhelmed trying to look at the very useful code including the COALESCE(NullIF(...
The example of the GETDATE() +3 (and the rest) was great. I am in the process of adding a test for all Nulls (No Permit)
Yes you are correct, I don't care about the MaxDate.
This is converting a working Rule Engine in Access VBA (cursive) over to a more efficient method on SQL Server.
The return values of Submit, Approved, Return, Approved but Expired, plus an all Null (no permit has started yet) is the objective.
The way the @Dates creates the test set is wonderful and useful! I would not have though of that.
At first glance, the '19000101' confused me. Now, I see that it doesn't affect the outcome.
It will take me a while to make some modifications. It is my intent to post the results.
My intent is to post the final code if today's schedule allows.
November 3, 2014 at 10:38 am
Mile Higher Than Sea Level (11/3/2014)
All of this really helped. Thanks!Thanks so much. I am overwhelmed trying to look at the very useful code including the COALESCE(NullIF(...
The example of the GETDATE() +3 (and the rest) was great. I am in the process of adding a test for all Nulls (No Permit)
Yes you are correct, I don't care about the MaxDate.
This is converting a working Rule Engine in Access VBA (cursive) over to a more efficient method on SQL Server.
The return values of Submit, Approved, Return, Approved but Expired, plus an all Null (no permit has started yet) is the objective.
The way the @Dates creates the test set is wonderful and useful! I would not have though of that.
At first glance, the '19000101' confused me. Now, I see that it doesn't affect the outcome.
It will take me a while to make some modifications. It is my intent to post the results.
My intent is to post the final code if today's schedule allows.
Seeing the final code would be great.
Just a comment, you really shouldn't use GETDATE() + 1, etc... as it isn't really clear. It happens to add a day, but that could change. You really should use DATEADD(DAY, 1, GETDATE()). I normally don't post using Date + 1, but was lazy when I posted that code snippet.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 3, 2014 at 3:37 pm
Well, it took a while but I fully understand it all now. Will take the Date into consideration.
After running 10,000 records for the Federal Permits, I have a consistent error (null returned) in the function. But, it shouldn't be too hard to identify and fix. There is a line added to check for all Nulls in 4 columns. In reality, I only had to check for 3 columns because there will never be an "expiration date" by itself. The forms on the front-end application enforce many rules.
CREATE FUNCTION [dbo].[VRE_APD_Trump](
@SubmitDate DATETIME,
@ApprovedDate DATETIME,
@ExpirationDate DATETIME,
@ReturnedDate DATETIME
)
RETURNS VARCHAR(22)
AS
BEGIN
DECLARE @FunctionResult as VARCHAR(22); -- Business Condition returned
--set @timeNow = = SYSDATETIME() -- gets error! why?
set @FunctionResult =
-- Just trying Date 1,2,4 (will evaluate Date 2,3 next)
CASE WHEN @SubmitDate IS Null AND
@ReturnedDate IS Null AND
@ApprovedDate IS NULL THEN 'No Permit'
WHEN COALESCE(NULLIF(@ApprovedDate, '19000101'), NULLIF(@ExpirationDate, '19000101'), NULLIF(@ReturnedDate, '19000101')) IS NULL OR
@SubmitDate > @ApprovedDate AND
@SubmitDate > @ReturnedDate THEN 'Submit'
WHEN @ApprovedDate > ISNULL(@SubmitDate, '19000101') AND
@ApprovedDate > isnull (@ReturnedDate, '19000101') AND
@ExpirationDate > GETDATE() THEN 'Approved'
WHEN @ApprovedDate > ISNULL(@SubmitDate, '19000101') AND
@ExpirationDate < GETDATE() AND
@ApprovedDate >ISNULL(@ReturnedDate, '19000101') THEN 'Approved but Expired'
WHEN @ReturnedDate > ISNULL(@SubmitDate, '19000101') AND
@ReturnedDate > ISNULL(@ApprovedDate, '19000101') THEN 'Return'
END
return @FunctionResult
END;
Just the Fed Permits have three tables. The latest record for each Federal Permit Submitted, Approved, Expired, Returnd show the top (last date for each) using a view:
SELECT *
FROM (SELECT *, RowID = ROW_NUMBER() OVER (PARTITION BY ID_Wells
ORDER BY RE_24DTSub DESC)
FROM vre_apdfedreturn) AS MyDateOrder
WHERE RowID = 1
There are views that each join 3 tables (Submit, Approved, Returned) to obtain the 4 fields.
Each view is the same structure / field name. They are named for Federal Permits, State Permits, and Local Permits.
Each table has the same field name so this function can be used on each type of permit.
The query for the view for a linked table to Access is:
SELECT ID_Wells, Well_Name, WellTypeID, ClassificationID, ID_State, R_OverRideRuleCheck, SubmitDate, ApprovedDate, ExpirationDate, ReturnedDate,
(SELECT dbo.VRE_APD_Trump(dbo.VRE_APD_FED_TOP.SubmitDate, dbo.VRE_APD_FED_TOP.ApprovedDate, dbo.VRE_APD_FED_TOP.ExpirationDate,
dbo.VRE_APD_FED_TOP.ReturnedDate) AS RE_APD_FedResult) AS APD_FedResult
FROM dbo.VRE_APD_FED_TOP
There is a lot of processing going on a dedicated SQL Server. The 10,000 records now takes around 1 second (or less) for Federal.
The Access record-set process for this part was about 43 seconds. Now, I can delete a lot of code and temp tables.
In normal use, the users only call 1 specific record at a time, not 10,000.
This is a structured Metadata system for a Rule Engine to manage Regulatory Compliance.
The help is a huge break-through for me.
Thanks very much.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply