Create a Condition for a DTS Task

I simply want an email to go out if any records in a table exist, else no email. I have other steps completed in this DTS job but this is the last step. Any ideas?
ddaveNever mind. I decided it be best to drop it into a stored procedure.

ddave

CR with Left Joint and where condition

I have two tables like this

Table 1

tid
...

Table 2

tid
type
...

the relation is on the column tid of both the tables. We can have multiple records for a tid in Table2 or we may not have any record too.

I am able to get results from Table1 and Table2 by making left outer join in crystal reports (linking). But i have one problem.
I need to get only records in Table2 whose type (field) is "A". For this is achive I am going to select expert in Crystal reports and did type="A" but the problem is if Table2 is not having any records for a tid that tid record is not displaying. How to solve this issue.
I know if we do some thing like this type (+)="A" it will work but How can I do this in Crystal Reports.

Any help is apperciated.

Thanks
KalikiYou can use the add command property in the database expert and write manually your database query

select t.field1, r.field1, t.field2, r.field2
from tab1 t, tab2 r
where tab1.field3 = tab2.field3 (+)
and tab2.field4 (+) = 'A'

This works fine using CR version 10, if you are using an older version (8.5) you can type the 'and tab2.field4 (+) = 'A'' part of the condition to the SQL-query (DATABASE, EDIT SQL-query).

- Jukka|||Thanks for your response.
I am using 8.5 so i am editing the sql via (DATABASE, EDIT SQL-query).

Counting Rows

I have a simple report, listing patient names, and then columns depending on
certain condition. If they have the condition the column is True, and if
they don't the column is false.
The problem I have is trying to count how many have each condition. I
inserted the following expression in the footer:
=Count(cint(field!.condtion1.value)=-1)
If the column is true it does resolve to a -1, a 0 if it is false.
The expression just counts every row. It doesn't seem to evaluate the
expression.
Any help would be appreciated.
John HartJohn,
Try having the expression evaluate like this:
Count(iif(cint(field!.condtion1.value)=-1, 1, nothing))|||or try
SUM(iif(field!.condtion1.value = true, 1, nothing))

counting number of specific rows in SQL

hi,

I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.

Thanks,

Dror.

I don't think a SQL Server function is the best option for you. If you are returning this to SSRS, you'll probably want to leverage a stored procedure. Still, here is a generic representation of what you're asking for:

create function dbo.MyFunction( @.MyParam varchar(10) )

returns @.ret table (MyCount int, MyAvg float)

begin

insert into @.ret (MyCount, MyAvg)

select count(*), avg(MyField) from MyTable where MyOtherField=@.MyParam

return

end

This function returns a table. To retrieve data, you will call it as follows:

select *

from dbo.MyFunction( @.MyParam)

Again, I don't think this is the best approach to this problem. There are more easily read and consumed approaches to this problem.

Thanks,
Bryan

|||I am aware of the fact I can perform the same in VB - even eassier, but there is a reason for using function on the server. Supposed I want to set the number of rows to a variable, how do I do it ?|||

A VB function is not a good choice either. Are you using this data in SSRS?

Thanks,
Bryan

|||

Hi Bryan,

To tell the truth - I do not even know what SSRS is...I am new to this field, still learning. I tried to minimize my application for security (I'm affraid of paople will restore my code and see my logic creating the code), so some of the important stuff will perform as stored procedures on the SQL (which I'm also learning now...) Now I managed to calculate the average, and count the rows. But my function needs to return the result in a variable: the first variable - numbers of rows, the second - the average of a column, and the third - I need to find the fifth biggest value in a specific column. The prolem is assigning the results into variables.

Thanks again for your help,

Dror.

|||

Try something like this with appropriate substitutions. Please note, the data types I'm using for the parameters does not imply that these would be the approapriate data types for your application.

Code Snippet

create proc MyProc @.MyParam varchar(10), @.MyCount int OUTPUT, @.MyAvg float OUTPUT

as

select @.MyCount = count(*), @.MyAvg = avg(MyField)

from MyTable

where MyOtherField = @.MyParam

return 0

end

You can look up more info on using output parameters by reading the Books Online entry for the CREATE PROCEDURE statement.

Thanks,
Bryan Smith

|||thank you very much for your great help Bryan.

Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber. What I'd like is a select query which will generate a new field which counts back the consecutive number of days that ErrorNumber \= 0. I'd like the record set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days in a row.
I've been pulling my hair out about this.
Thanks!
Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist of what its doin. But I'm no database dynamo, so I'm gonna have to work with this for a bit to see what it'll actually return.
Thanks again!
|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!
|||Thats exactly it! I gotta be able to pass a parameter for each servicedate, but I'll be able to figure that out.
Thanks - I appreciate it!

Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber
. What I'd like is a select query which will generate a new field which coun
ts back the consecutive number of days that ErrorNumber \= 0. I'd like the r
ecord set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days
in a row.
I've been pulling my hair out about this.
Thanks!Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist
of what its doin. But I'm no database dynamo, so I'm gonna have to work with
this for a bit to see what it'll actually return.
Thanks again!|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!|||Thats exactly it! I gotta be able to pass a parameter for each servicedate,
but I'll be able to figure that out.
Thanks - I appreciate it!