Email reports based on a condition

Hi,

Data is fed to our database from 10 different places. We massage the data and then send out reports via email with rs subscriptions.

Everything works fine except when at least one of the data feeds does not load properly on time. The reports go out but with uncomplete data generating undesired effect in management.

I can create a "Loaded" flag on the database. Is there a way to use this or other method to send out the reports based on a condition?

Thank you.

One more thing, we only have the standard edition of rs, not the enterprise edition where you can use data-driven subscription.

In the meantime, we thought of a possible solution: Create a status table and new dataset in rs. Based on the status of the day, hide or make visible detail and header rows of the report. The same idea apply to a text box stating the status of the loads.

|||

I did some research on that subject for SQL2000 RPS.

Peter Blackburn had a solution, here is his message as well as my comments.

It should still work with 2005

For the condition, you can set a step to test your "Status" table ad success only when your data is ready and fire the report job.

Have fun,

Philippe

-

Hi,

Here the trick is to use SP_Start_Job to start the report from a sp or a dts.

You can also use something like exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription', @.EventData='6273f0b2-3899-416c-ada7-29c801a2c16f'

in your sp or DTS.

As Peter says, this breaks when you edit the subscription schedule from ReportServer but still, you can do it and you can also set a test like

DECLARE @.JobID BINARY(16)

SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'EF3028CF-D22A-4EA7-B197-D9018D6BA262')
IF (@.JobID IS NULL)
BEGIN

-- send some sort of alert to the developer so he recreates the correct calls....

EXEC msdb.dbo.sp_send_dbmail

@.recipients = 'someone@.someplace.com',

@.body = 'The report BlahBlah EF3028CF-D22A-4EA7-B197-D9018D6BA262 could not run, it may have been updated on the report server, sorry.',

@.subject = 'Report could not run';

else

-- run the report

exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription', @.EventData='6273f0b2-3899-416c-ada7-29c801a2c16f'

end

This is not optimal however this may be very handful for reports where you absolutely need it.

When you set your initial schedule in the past, set a time easy to find in the job list.

Phil

Subject: Re: Scheduling a Report based on an event

11/9/2004 10:38 PM PST

By:

Peter Blackburn (www.sqlreportingservice

In:

microsoft.public.sqlserver.reportingsvcs

Was this post helpful to you?

Sure this is real easy to do.Create a schedule that has completed in the past - so effectively it will never fire. Associate this schedule with a Report.Now what happens is that a SQL Agent Job is created - that maps to the schedule. You can run SQL Agent Jobs from the SQL Agent Management interface by hand - or you can cause that job to run through T-SQL.All that the SQL Agent Job does is create an entry in the Report Server's Event table at the scheduled time. The Report Server Windows Service is polling the Event table every 10 seconds or so - and if there are any events to process it gets on and processes them.So what you do is either include in your long running stored procedure a call that will create the required entry in the Event table directly - or a call that fires the SQL Agent Job.- One word of warning though if you start editing the schedule in the Report Manager, then the Report Manager can end up re-creating the SQL Agent Jobs - and you lose reference to the actual Job.However if you are disciplined enough then this approach works fine - (Schedule in the past, have your own process force the SQL Agent Job to run)Peter BlackburnHitchhiker's Guide to SQL Server 2000 Reporting Serviceshttp://www.sqlreportingservices.net"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...> Is it possible to schedule a report based on a flag or stored procedure > completing. Currently have an overnight load process which must complete > before the report starts. Any suggestions would be most appreciated>> > Posted using Wimdows.net NntpNews Component -

|||

Thank you for your response.

We got the results we wanted by creating a load_status table. In my report I created a new dataset that looks up for the flag value on this table.

Then we condition the visibility of each row of the table based on this flag. We added another header row with the "sorry, not all data was loaded, report will be sent later" with the opposite condition and it works great.

Good luck!

ELSEIF in Stored Procedures?

I have found some documentation regarding the use of IF...ELSE statements in stored procedures, but what about multiple condition statements? For example, say I need 3 unique fields in my table. If my application passes a value that is a duplicate in one of the columns, the stored proc will fail, but it is difficult to know which item caused the failure and therefore difficult for the user to get a meaningful error message in order to correct their input. I am thinking I could just make a conditional statement that applies a code to an OUTPUT parameter in order to clarify the error:

(pseudo code)

if @.field1 already exists then @.output = '1';terminate stored procedure

elseif @.field2 already exists then @.output = '2';terminate stored procedure

elseif @.field3 already exists then @.output = '3';terminate stored procedure

else finish the insert

(end pseudo code)

Are 'elseif' statements allowed in SQL Server? Am I going about this in the wrong way?

Jungalist wrote:

Are 'elseif' statements allowed in SQL Server?

Sort of. You can impletemt the logic as follows :

IF @.field1 already exists

BEGIN

SET @.OUTPUT = 1

END

ELSE

IF @.field2 already exists

BEGIN

SET @.OUTPUT=2

END

ELSE

IF @.field3 already exists

BEGIN

SET @.OUTPUT=3

END

check out books on line for "IF ELSE"

|||

Thank-you. I was searching for the wrong terms. I appreciate the help.

ELSE CONDITION IN MDX

Hi, I'll appreciate your help to solve my develop problem.
I need to use a conditional expresion to assign a calculated member
value to cube members. I can't use the IIF function because I have 3
possible values (eg A,B,C). How can I implement the "ELSE" condition? In
pseudocode y say:
if x<=10 'A'
else if (x>10 and x<= 50) 'B'
else 'C'
Thankyou, regards,
Luis.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!You can nest the IIF statements
pseudo-code
IIF(x<10, 'A',(IIF(x<=50,'B', 'C'))
Sean
Sean Boon
SQL Server BI Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Luis Chiappara" <chappa@.montevideo.com.uy> wrote in message
news:%2393AGkVKEHA.808@.tk2msftngp13.phx.gbl...
> Hi, I'll appreciate your help to solve my develop problem.
> I need to use a conditional expresion to assign a calculated member
> value to cube members. I can't use the IIF function because I have 3
> possible values (eg A,B,C). How can I implement the "ELSE" condition? In
> pseudocode y say:
> if x<=10 'A'
> else if (x>10 and x<= 50) 'B'
> else 'C'
> Thankyou, regards,
> Luis.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

ELSE CONDITION IN MDX

Hi, I'll appreciate your help to solve my develop problem.
I need to use a conditional expresion to assign a calculated member
value to cube members. I can't use the IIF function because I have 3
possible values (eg A,B,C). How can I implement the "ELSE" condition? In
pseudocode y say:
if x<=10 'A'
else if (x>10 and x<= 50) 'B'
else 'C'
Thankyou, regards,
Luis.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
You can nest the IIF statements
pseudo-code
IIF(x<10, 'A',(IIF(x<=50,'B', 'C'))
Sean
Sean Boon
SQL Server BI Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Luis Chiappara" <chappa@.montevideo.com.uy> wrote in message
news:%2393AGkVKEHA.808@.tk2msftngp13.phx.gbl...
> Hi, I'll appreciate your help to solve my develop problem.
> I need to use a conditional expresion to assign a calculated member
> value to cube members. I can't use the IIF function because I have 3
> possible values (eg A,B,C). How can I implement the "ELSE" condition? In
> pseudocode y say:
> if x<=10 'A'
> else if (x>10 and x<= 50) 'B'
> else 'C'
> Thankyou, regards,
> Luis.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Efficient Coding

I have a quite lengthy Update query that is working in
its current condition BUT I don't feel that I have coded
this query as effectively as I should to minimize the
transactions because of the many update statements that
I'm doing. The Updates all occur within a single table
but I don't see how I can combine the various WHERE
clauses in 1 of fewer UPDATES
Any help or suggestions would be appreciated
The statement follows:
-- Procedure to Update DR Critical, Essential and Vital
Servers
DECLARE @.today DATETIME
SET @.today = GETDATE()
DECLARE @.criticalrows INT
SET @.criticalrows = (SELECT COUNT(*)
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(DRServiceLevel = 'None'))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET DRCompliance = 'NA', RACompliance = 'NA',
TestCompliance = 'NA', TotalCompliance = 'NA'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(DRServiceLevel = 'None')))
END
ELSE
BEGIN
-- Check DR Compliance
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
DRPlanEffectDate)) AND
(@.today <= DATEADD(mm, 12,
DRSignEffectDate)) AND
(DRPlanName <> '') AND
(DRSignName <> ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET DRCompliance = 'Yes'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
DRPlanEffectDate)) AND
(@.today <= DATEADD(mm, 12,
DRSignEffectDate)) AND
(DRPlanName <> '') AND
(DRSignName <> '')))
END
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
DRPlanEffectDate)) OR
(@.today > DATEADD(mm, 12,
DRSignEffectDate)) OR
(DRPlanName = '') OR
(DRSignName = ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET DRCompliance = 'No'
WHERE (VARID IN (SELECT VARID AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
DRPlanEffectDate)) OR
(@.today > DATEADD(mm, 12,
DRSignEffectDate)) OR
(DRPlanName = '') OR
(DRSignName = '')))
-- End of DR Compliance
END
-- Check RA Compliance
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
RAEffectDate)) AND
(RAName <> ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET RACompliance = 'Yes'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
RAEffectDate)) AND
(RAName <> '')))
END
SET @.criticalrows = (SELECT COUNT (*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
RAEffectDate)) OR
(RAName = ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET RACompliance = 'No'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
RAEffectDate)) OR
(RAName = '')))
-- End of RA Compliance
END
-- Check Test Compliance
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
TestEffectDate)) AND
(TestName <> ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET TestCompliance = 'Yes'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
TestEffectDate)) AND
(TestName <> '')))
END
SET @.criticalrows = (SELECT COUNT (*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
TestEffectDate)) OR
(TestName = ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET TestCompliance = 'No'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
TestEffectDate)) OR
(TestName = '')))
-- End of Test Compliance
END
-- Check Total Compliance
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
DRPlanEffectDate)) AND
(@.today <= DATEADD(mm, 12,
DRSignEffectDate)) AND
(@.today <= DATEADD(mm, 12,
RAEffectDate)) AND
(@.today <= DATEADD(mm, 12,
TestEffectDate)) AND
(DRPlanName <> '') AND
(DRSignName <> '') AND
(RAName <> '') AND
(TestName <> ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET DRCompliance = 'Yes', RACompliance = 'Yes',
TestCompliance = 'Yes', TotalCompliance = 'Yes'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
DRPlanEffectDate)) AND
(@.today <= DATEADD(mm, 12,
DRSignEffectDate)) AND
(@.today <= DATEADD(mm, 12,
RAEffectDate)) AND
(@.today <= DATEADD(mm, 12,
TestEffectDate)) AND
(DRPlanName <> '') AND
(DRSignName <> '') AND
(RAName <> '') AND
(TestName <> '')))
END
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
DRPlanEffectDate)) OR
(@.today > DATEADD(mm, 12,
DRSignEffectDate)) OR
(@.today > DATEADD(mm, 12,
RAEffectDate)) OR
(@.today > DATEADD(mm, 12,
TestEffectDate)) OR
(DRPlanName = '') OR
(DRSignName = '') OR
(RAName = '') OR
(TestName = ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET TotalCompliance = 'No'
WHERE (VARID IN (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
DRPlanEffectDate)) OR
(@.today > DATEADD(mm, 12,
DRSignEffectDate)) OR
(@.today > DATEADD(mm, 12,
RAEffectDate)) OR
(@.today > DATEADD(mm, 12,
TestEffectDate)) OR
(DRPlanName = '') OR
(DRSignName = '') OR
(RAName = '') OR
(TestName = '')))
-- End of Total Compliance
END
END--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can get rid of the @.criticalRows check & run the UPDATEs as you have
them. If there are rows that satisfy the criteria the updates will
occur. Checking before-hand is redundant & inefficient. Also, the
procedure will not be compiled w/ an execution plan 'cuz of the IFs;
this slows down execution when the procedure runs.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQlWsH4echKqOuFEgEQL2agCggOJoyFX0Ct9c
LnY/5Vb50aH4wZUAoOWR
gaksMFOohIaLxP5oT1AjNi0S
=tApC
--END PGP SIGNATURE--
Jazz wrote:
> I have a quite lengthy Update query that is working in
> its current condition BUT I don't feel that I have coded
> this query as effectively as I should to minimize the
> transactions because of the many update statements that
> I'm doing. The Updates all occur within a single table
> but I don't see how I can combine the various WHERE
> clauses in 1 of fewer UPDATES
> Any help or suggestions would be appreciated
> The statement follows:
> -- Procedure to Update DR Critical, Essential and Vital
> Servers
> DECLARE @.today DATETIME
> SET @.today = GETDATE()
> DECLARE @.criticalrows INT
> SET @.criticalrows = (SELECT COUNT(*)
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (DRServiceLevel = 'None'))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET DRCompliance = 'NA', RACompliance = 'NA',
> TestCompliance = 'NA', TotalCompliance = 'NA'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (DRServiceLevel = 'None')))
> END
> ELSE
> BEGIN
> -- Check DR Compliance
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> DRPlanEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> DRSignEffectDate)) AND
> (DRPlanName <> '') AND
> (DRSignName <> ''))
>
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET DRCompliance = 'Yes'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> DRPlanEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> DRSignEffectDate)) AND
> (DRPlanName <> '') AND
> (DRSignName <> '')))
> END
>
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> DRPlanEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> DRSignEffectDate)) OR
> (DRPlanName = '') OR
> (DRSignName = ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET DRCompliance = 'No'
> WHERE (VARID IN (SELECT VARID AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> DRPlanEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> DRSignEffectDate)) OR
> (DRPlanName = '') OR
> (DRSignName = '')))
> -- End of DR Compliance
> END
>
> -- Check RA Compliance
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> RAEffectDate)) AND
> (RAName <> ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET RACompliance = 'Yes'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> RAEffectDate)) AND
> (RAName <> '')))
> END
> SET @.criticalrows = (SELECT COUNT (*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> RAEffectDate)) OR
> (RAName = ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET RACompliance = 'No'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> RAEffectDate)) OR
> (RAName = '')))
> -- End of RA Compliance
> END
> -- Check Test Compliance
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> TestEffectDate)) AND
> (TestName <> ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET TestCompliance = 'Yes'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> TestEffectDate)) AND
> (TestName <> '')))
> END
> SET @.criticalrows = (SELECT COUNT (*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> TestEffectDate)) OR
> (TestName = ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET TestCompliance = 'No'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> TestEffectDate)) OR
> (TestName = '')))
> -- End of Test Compliance
> END
> -- Check Total Compliance
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> DRPlanEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> DRSignEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> RAEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> TestEffectDate)) AND
> (DRPlanName <> '') AND
> (DRSignName <> '') AND
> (RAName <> '') AND
> (TestName <> ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET DRCompliance = 'Yes', RACompliance = 'Yes',
> TestCompliance = 'Yes', TotalCompliance = 'Yes'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> DRPlanEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> DRSignEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> RAEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> TestEffectDate)) AND
> (DRPlanName <> '') AND
> (DRSignName <> '') AND
> (RAName <> '') AND
> (TestName <> '')))
> END
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> DRPlanEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> DRSignEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> RAEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> TestEffectDate)) OR
> (DRPlanName = '') OR
> (DRSignName = '') OR
> (RAName = '') OR
> (TestName = ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET TotalCompliance = 'No'
> WHERE (VARID IN (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> DRPlanEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> DRSignEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> RAEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> TestEffectDate)) OR
> (DRPlanName = '') OR
> (DRSignName = '') OR
> (RAName = '') OR
> (TestName = '')))
> -- End of Total Compliance
> END
> END
>|||What you want to do is put all of this procedural logic into CASE
expressions and do the whole thign in *one* UPDATE statement. You do
not need the the @.today variable and the correct Standard syntax is
CURRENT TIMESTAMP. Stop adding aliases that are never references; the
guy maintaining the code will have to take time to look for them.
UPDATE AudServerDR
SET DRCompliance
= CASE WHEN <pred_1>
THEN 'Yes'
ELSE 'No' END,
RACompliance
= CASE WHEN <pred_2>
THEN 'Yes'
ELSE 'No' END,
TestCompliance
= CASE WHEN <pred_3>
THEN 'Yes'
ELSE 'No' END,
TotalCompliance
= CASE WHEN <pred_4>
THEN 'Yes'
ELSE 'No' END,
WHERE varid
IN (SELECT varid
FROM AudServerDR
WHERE RACriticality IN ('Essential', 'Critical', 'Vital')
AND DRServiceLevel = 'None');
END;
Since I do not have specs or know your application, I am guessing that
these flags are set to either 'Yes' or 'No', but if you have a 'N/A'
value, just add another WHEN..THEN to each assignment expression.
Something like this:
TotalCompliance
= WHEN RACriticality IN ('Essential', 'Critical', 'Vital')
AND CURRENT_TIMESTAMP > DATEADD(mm, 12,
DRPlanEffectDate)
OR CURRENT_TIMESTAMP > DATEADD(mm, 12,
DRSignEffectDate)
OR CURRENT_TIMESTAMP > DATEADD(mm, 12, RAEffectDate)
OR CURRENT_TIMESTAMP > DATEADD(mm, 12, TestEffectDate)
OR '' IN (DRPlanName, DRSignName, RAName, TestName =
'')
THEN 'No' ELSE 'Yes' END|||Thank you very much. This makes a lot of sense and
achieves what I though should be done didn't see haw to
do. I appreciate your help it's really helping me to
learn how to write better queries.
>--Original Message--
>What you want to do is put all of this procedural logic
into CASE
>expressions and do the whole thign in *one* UPDATE
statement. You do
>not need the the @.today variable and the correct
Standard syntax is
>CURRENT TIMESTAMP. Stop adding aliases that are never
references; the
>guy maintaining the code will have to take time to look
for them.
>UPDATE AudServerDR
>SET DRCompliance
> = CASE WHEN <pred_1>
> THEN 'Yes'
> ELSE 'No' END,
> RACompliance
> = CASE WHEN <pred_2>
> THEN 'Yes'
> ELSE 'No' END,
> TestCompliance
> = CASE WHEN <pred_3>
> THEN 'Yes'
> ELSE 'No' END,
> TotalCompliance
> = CASE WHEN <pred_4>
> THEN 'Yes'
> ELSE 'No' END,
>WHERE varid
> IN (SELECT varid
> FROM AudServerDR
> WHERE RACriticality IN
('Essential', 'Critical', 'Vital')
> AND DRServiceLevel = 'None');
>END;
>Since I do not have specs or know your application, I am
guessing that
>these flags are set to either 'Yes' or 'No', but if you
have a 'N/A'
>value, just add another WHEN..THEN to each assignment
expression.
>Something like this:
>TotalCompliance
> = WHEN RACriticality IN
('Essential', 'Critical', 'Vital')
> AND CURRENT_TIMESTAMP > DATEADD(mm, 12,
>DRPlanEffectDate)
> OR CURRENT_TIMESTAMP > DATEADD(mm, 12,
>DRSignEffectDate)
> OR CURRENT_TIMESTAMP > DATEADD(mm, 12,
RAEffectDate)
> OR CURRENT_TIMESTAMP > DATEADD(mm, 12,
TestEffectDate)
> OR '' IN (DRPlanName, DRSignName,
RAName, TestName =
>'')
> THEN 'No' ELSE 'Yes' END
>.
>