January 11, 2015 at 3:18 pm
Read your article with the insert data format. That is a useful.
Promise to change my ways and do this from now on.
I help many people on the Access forum, needless to say it is easy to have them send data or an entire table as an attachment.
Will practice with your excellent code example and keep that handy for all future request.
I am using SQL Server 2008 R2.
Used the Script Table As Create To and sent the part before the sys.spaddextendedproperty section
Is there a preferred way to do this?
January 11, 2015 at 3:23 pm
My suggestion, prior to posting any code, run it in an empty database. Post it when everything runs without error.
January 14, 2015 at 9:56 am
Did you have something constructive to say that would help the OP? Or do you just like to put other people down to show off how much you know? I thought the OP was being polite and grateful and was trying hard to learn. What exactly did you bring to the table?
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
January 14, 2015 at 2:56 pm
Thanks for all the inputs. Seriously, they all (eventually) added up to a great solution.
My apologies for not thanking everyone sooner.
My solution is part of a Quality Assurance Rule Engine used on very small sets of data for complex regulations.
All of the dialogue for this question helped me re-evaluate many things and will help me better prepare for asking next question.
While the tone of the next to previous response made me feel a little "schooled" LOL, I took the intent with a positive consideration.
I frequently post on other forums for Access Programming and Excel Object Model Programming. I fully resembled a newbie grasping for a vague solution.
The response did lead me to look into the SELECT MAX(x). Was able to add it into a single function where my multiple business rules returned the top 1 date in 5 different variables.
The MAX then returned the top date. My next step was creating a Select statement to take the MAX and match it with the Rule the MAX came from. One of my friends on the Access Programmers site told me the MAX did not work on his SQL Server 2000 version.
The impact of using the MAX was extremely positive for the deliverable.
Thanks for all the help!
This code works.
Any suggestion on improvements would be greatly appreciated as a learning experience.
-- each @variable is populated using a business rule with a Top 1 value of the latest activity for the category (not shown).
-- The Max value returns the latest date. Note: in rare case of a tie, the variables are in business rule order too.
IF (NULLIF(@StAPDApproved, '') Is Null AND NULLIF(@StAPDExpired, '')
Is Null AND NULLIF(@StAPDWithDrawn, '') Is Null AND NULLIF(@StAPDDenied, '')
Is Null AND NULLIF(@StAPDReturned, '') Is Null AND NULLIF(@StAPDSubmitted, '') Is Null)
begin
Set @CharResult = char(10); --All dates are Null function returns empty string for Permit Status
end
ELSE
BEGIN
--set ansi_warnings off -- removes harmless warning about null -- can't use in a function
set @max-2=(Select max(X)
FROM (VALUES (@StAPDSubmitted), (@StAPDApproved), (@StAPDExpired), (@StAPDWithDrawn), (@StAPDDenied), (@StAPDReturned)) as value(X))
--print @max-2
Set @CharResult = @max-2
-- Example Case @StAPDApproved was the max - function will return string 'St Approved'
-- Example Case @stAPDExpiredDt was the max - function will return string 'St Expired'
SET @CharResult = CASE
WHEN @max-2 = @StAPDSubmitted THEN 'ST Submitted'
WHEN @max-2 = @StAPDApproved THEN 'ST Approved'
WHEN @max-2 = @StAPDExpired THEN 'St Expired'
WHEN @max-2 = @StAPDWithDrawn THEN 'St Withdrawn'
WHEN @max-2 = @StAPDDenied THEN 'St Denied'
WHEN @max-2 = @StAPDReturned THEN 'St Returned'
ELSE 'St Error'
END -- End Case
End -- end Else statement
-- Note This is for State only - Customize for each regulatory agency
Set @CharReturnValue = @CharResult
return @CharReturnValue-- Text value returned by function
END;
January 14, 2015 at 3:56 pm
You still haven't provided the information I requested.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply