Conditional CASE WHEN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Conditional CASE WHEN

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-07-09 : 10:40:42

Somehow can't figure this out:

I have a table with a NVARCHAR (6) colomn DeliveryWeek. Most of the time this has a value like '200356' or '200210', but sometimes 'None'.

Now I want I group by with some conditions

 
SELECT DeliveryWeek, COUNT (*) 
FROM Oresys.dbo.tblAttribuut a
WHERE DeliveryWeek IS NOT NULL AND ISNUMERIC (AFleverweek) = 1
AND CAST (DeliveryWeek AS INT) BETWEEN DatePart (ww, GetDate()-7) AND DatePart (ww,GetDate() + 105)
GROUP BY DeliveryWeek

This produces an error:
Syntax error converting the nvarchar value 'None' to a column of data type int.

Even if I use this in my where clause


WHERE (CASE WHEN 
 (a.DeliveryWeek IS NOT NULL AND ISNUMERIC (a.DeliveryWeek) = 1) 
THEN CAST (a.DeliveryWeek AS INT) ELSE 0 END 
BETWEEN DatePart (ww,GETDATE() - 14) AND DatePart (ww,GETDATE() + 105) )

What should I do?

Henri

~~~
SQL is nothing, writing it everything.

Edited by - henrikop on 07/09/2003 10:43:17

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-09 : 10:57:48

personally I would do this

UPDATE
Oresys.dbo.tblAttribuut
SET
deliveryweek = null
WHERE deliveryweek = 'none'

Then this will cease to be a problem. You can then change your other output procedures to cope with nulls instead of 'none'.

-------
Moo. :)

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-07-09 : 11:14:46

I noticed a fault in my code.. The where condition in the second part needs to be completed with year e.g. '200345' will never be between '40' and '50'.

Mr_mist: I cannot change the data, and the data besides yyyyww has a specific meaning ('None' was an example of what other data DeliveryWeek could contain). Nonetheless thanx voor taking the time to read this :-)

Henri

~~~
SQL is nothing, writing it everything.

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-10 : 03:25:40

quote:

I noticed a fault in my code.. The where condition in the second part needs to be completed with year e.g. '200345' will never be between '40' and '50'.

Mr_mist: I cannot change the data, and the data besides yyyyww has a specific meaning ('None' was an example of what other data DeliveryWeek could contain). Nonetheless thanx voor taking the time to read this :-)

Yes, I had a feeling that might be the case. It looks like a bit of a design issue, because you're trying to do too much with that deliveryweek column.

You'll always get issues like this if your column contains a mix of datatypes and you're trying to convert some of them.

Maybe you could do a two-pass sweep. First pass collect all the valid dates, second pass does the counting. You may need to use text comparison on the first pass, or length comparison.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources