> select
> ( select case when (a.mdz is null) then 'A'
> when (cast(a.mdz as timestamp) > cast('2011-09-30' as
> timestamp)) then 'B'
> when (cast(a.mdz as timestamp) < cast('2011-09-30' as
> timestamp)) then 'C'
> else 'D'
> end
>
> from (
> select max(KEStazZap.stazzapdatumzaposljavanja) as mdz
> from KEStazZap
> where KEStazZap.DjelatnikID = KEDjelatnik.DjelatnikID
> and cast( '2011-09-30' as timestamp) <=
> cast(KEStazZap.stazzapdatumprekida as timestamp)
> group by djelatnikID
> ) a
>)
>
>from
>KEDjelatnik
>
>in results I get Null values where I think it should be 'A'. What am I doing
>wrong ?
Sorry Zlatko, but I'd say your SQL is quite messy.
Rather, I'd recommend you to try something like:
WITH TMP (djelatnikID, MDZ) as
(select DjelatnikID, max(cast(stazzapdatumzaposljavanja as TimeStamp))
from KEStazZap
where cast(stazzapdatumprekida as timestamp) >= '2011-09-30'
group by djelatnikID)
SELECT case
when a.mdz is null then 'A'
when a.mdz > '2011-09-30' then 'B'
when a.mdz < '2011-09-30' then 'C'
else 'D'
end
FROM KEDjelatnik K
LEFT JOIN TMP T ON K.DjelatnikID = T.DjelatnikID
I don't think the above SQL would return NULL in any situation, your original
SQL would have returned NULL if there were no matching entry in KEStazZap (the
inner query didn't return anything at all, so it wouldn't be changed to A).
Also, note that I casted stazzapdatumzaposljavanja in the subselect, I'm not
certain what type it is defined as, but your use of MAX might not have returned
what you would expect if it is not defined as a DATE, TIME or TIMESTAMP type
(if it is already some kind of timestamp, you don't need to cast it at all).
HTH,
Set
