Number of entry query.

MSSQL2K
SP4

Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.

--drop table #foo
create table #foo
(empID int,
transDate datetime,
transType varchar(10))

insert into #foo values(1, '01/01/06 01:01:01','Insert')
insert into #foo values(1, '01/01/06 01:01:02','Update')
insert into #foo values(1, '01/01/06 01:01:03','Delete')
insert into #foo values(2, '01/01/06 01:01:01','Insert')
insert into #foo values(2, '01/01/06 01:01:02','Update')

select f.empID, Change =
(select count(transDate) from #foo f2
where f2.empID = f.empID
group by empID),
f.transDate, f.transType
from #foo f

Current results:

1 3 2006-01-01 01:01:01.000 Insert
1 3 2006-01-01 01:01:02.000 Update
1 3 2006-01-01 01:01:03.000 Delete
2 2 2006-01-01 01:01:01.000 Insert
2 2 2006-01-01 01:01:02.000 Update

Desired results:

1 1 2006-01-01 01:01:01.000 Insert
1 2 2006-01-01 01:01:02.000 Update
1 3 2006-01-01 01:01:03.000 Delete
2 1 2006-01-01 01:01:01.000 Insert
2 2 2006-01-01 01:01:02.000 Update

TIA, CFRwhere f2.empID = f.empID AND f2.transdate <= f.transdate

Which presumes no 2 can have the same date/time value.|||So close, yet so far. Thanks.|||Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.|||Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.
Nah, I am pretty sure he means that until your assist, CFR was so closer and yet so far from their solution.

After all, they would have asked for more help other wise, or said what they were getting wrong.

I guess we should be glad that they at least wrote back to thank you. Some folks get the answer, and then disappear.|||As Code Carpenter mentioned, I was so close yet so far. The code did exactly what I needed. As far as disappearing, Im afraid you folks are stuck with me for a while.

Thanks again!

Nulls when summing a group in an expression

Hi guys,

I know this has been discussed before, but not in a way that solves this particular problem. I have some data in a table that is grouped by rows, but when i sum the fields in the group i am getting an error because one of the grouped rows contains a null.

Now when i have a one to one relationship between a dataset field and a table cell, checking for a null is easy. But how do you do it when you have several fields that are being summed for that table cell?

In this particular case, i have a work around - there are only two data rows per row group (paid revenue, and unpaid revenue), so i can check them individually for nulls by using First(Fields!Revenue.Value) and Last(Fields!Revenue.Value). This is the expression i used:

=IIf(

Not IsNothing( Fields!Revenue.Value) and isnumeric(Fields!Revenue.Value),

FormatCurrency(

IIf(

Not IsNothing( First(Fields!Revenue.Value)),

First(Fields!Revenue.Value),

0

)

+

IIf(

Not IsNothing( Last(Fields!Revenue.Value)),

Last(Fields!Revenue.Value),

0

),

0,

true,

false,

true

),

"-"

)

The first expression in the outer IIf is worthless, because using Fields!Revenue.Value only checks the first row in the group, and in my case it is always the second row that contains the null.

What is the best way to account for the null values?* It seems i can't access the .Value field like an array (i.e. Fields!Revenue.Value(1)). Should i be passing Fields!Revenue.Value through to a piece of custom code to sum it?

Thanks!

sluggy

*The data i am working with is sourced from a cube with an mdx query. The query uses a COALESCEEMPTY(<measure>, 0) function, but it seems the data provider being used is changing the fields that were coalesced back into nulls. Which is ok - at least i am getting the rows, prior to using the coalesce the rows were being dropped. I'd rather have nulls than have the row missing from the dataset.

Not sure if I understand your question completely, but you can use IIF inside of an aggregate function if you want to skip null values. For example, =Sum(IIF(IsNothing(Fields!Revenue.Value), 0, Fields!Revenue.Value)).

|||

Hi

can you try this out and check whether it is working or not.

=IIf(

Not IsNothing( Fields!Revenue.Value) and isnumeric(Fields!Revenue.Value),

FormatCurrency(

IIf(

Not IsNothing( Cint(First(Fields!Revenue.Value))),

Cint(First(Fields!Revenue.Value)),

0

)

+

IIf(

Not IsNothing( Cint(Last(Fields!Revenue.Value))),

Cint(Last(Fields!Revenue.Value)),

0

),

0,

true,

false,

true

),

"-"

)

Thanks,

Srinivas Reddy.

|||

Srinivas, Fang,

thanks for your answers, but neither work. In this case casting the fields has no effect, as they are already being returned as ints, and you can't typecast a field that is already null.

I have noticed something further, and consequently some of my original information was incorrect. What i have noticed is that nulls are NOT being returned - zeros are. So if i code this expression:

=First(Fields!Revenue.Value) + Last(Fields!Revenue.Value)

it gets evaluated no problem - i get the revenue from the first row plus zero from the second. But if i do any sort of aggregation type function on it, like Sum or Avg etc, then i get an error.Can anyone explain what is happening here, or suggest how i can determine exactly what error is occurring?

Thanks,

sluggy

|||

If you are designing and previewing the report in the report designer, you can check the error message in the task bar.

What's the data type of the Revenue field? One possible reason could be you are summing mixed types. Aggregate functions require all the values passed in are of the same type.

|||

Thanks Fang, i will check into that.

sluggy

Null Values

I have run a report and then grouped values. However some of these values have a 'null' or empty value. How do i change this so that the group name is not blank.

I have tried the following but this does not help:

IF {Subset.SCHOOLCAT} = '' THEN 'other'

It simply blanks out all group names.

I am doing this report on a HEAT System and i know that the reason for this null field is that we have two types of customers and one customer type does not have this field in its tables so it returns the value as blank.

I just need to replace this empty value with a string for consistency value but cannot seem to do it.

Regards

PIF {Subset.SCHOOLCAT} = '' THEN 'other'

Instead of the above code, try the following code

IF IsNull({Subset.SCHOOLCAT}) THEN 'other'|||Many thanks - that seemed to work.

Cheers

T