"Luuk" wrote...
On 30-5-2014 19:29, jose isaias cabrera wrote:
"Igor Tandetnik" wrote...
On 5/30/2014 12:41 PM, jose isaias cabrera wrote:
What should be returned is
the value of vEmail of the first record that has Xtra4='y'
What do you mean by "first record"? Records are processed in no
particular order.
Good point. I was, wrongly, thinking that it was top to bottom with the
id. So, the idea is that once Xtra4='y' has provided a value, that is
what I want. So, I think that because I am always getting 'noemail'
then, it is caused because the last record read had Xtra4!='y'. So,
this is not properly giving the result desired. So, how can I change
this,
CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END,
so that once I have a value from Xtra4='y', I want to keep that value as
the result. Thanks.
sqlite> create table t(x);
sqlite> insert into t values(1),(2),(3);
sqlite> select x from t;
1
2
3
sqlite> select x, case when (select 1 from t where x=2) then 'Y' else
'N' end from t;
1|Y
2|Y
3|Y
sqlite>
Not quite, as I would like to grab vEmail the first time Xtra4='y'.
Here is a sample test:
create table t
(
id integer primary key,
ProjID integer,
invoice,
ProjFund,
vEmail,
Xtra4
);
insert into t values(1,1,70,100,'a','n');
insert into t values(2,1,30,50,'b','n');
insert into t values(3,1,25,40,'c','y');
insert into t values(4,1,55,80,'d','y');
insert into t values(5,2,30,75,'e','n');
insert into t values(6,2,10,100,'f','y');
insert into t values(7,2,40,68,'g','y');
insert into t values(8,2,30,55,'h','n');
insert into t values(9,3,25,40,'i','y');
insert into t values(10,3,30,100,'j','n');
select ProjID,
case Xtra4 when 'y' then vEmail else 'noemail' end,
sum(ProjFund),
sum(ProjFund) - sum(case Xtra4 when 'y' then invoice else 0 end),
sum(case Xtra4 when 'y' then invoice else 0 end)
FROM t GROUP BY ProjID
HAVING sum(case Xtra4 when 'y' then invoice else 0 end) > 0;
which gives me this result,
1|d|270|190|80
2|noemail|298|248|50
3|noemail|140|115|25
But I want,
1|d|270|190|80
2|f|298|248|50
3|i|140|115|25
