problem with case in sql statement

[wysiwygger]

Hi,

I'm having some trouble with my SQL statement.
What I'm doing is: Retrieving the invoice details from my SQL Server 2005. In the example below it's looking for the job number "P-05-012-01" and some exchange rates out of a range of dates.
With the case statement I calculate my average exchange rate from the exchange rates in my range of dates.
Now....this is all working well.


PROBLEM is if there is no date for an exchange rate, e.g. if the invoice is in the June, e.g.

himsys.dbo.cscrd.ratedate BETWEEN '2007-06-01' AND '2007-06-31'

There is no exchange rate for June yet!

Is there a way I can have the exchange rate calculated as it is now but if there is no exchange rate because the date is in the future it just gives me the exchange rate as '1'

The way it is now the SQL-statement will just ignore every invoice which has no exchange rate and shows me nothing.

I hope this can be done without stored procedures because I'm not that advanced yet.


SELECT himdat.dbo.ebbudd.budamt3 AS invoice,
himdat.dbo.ebbudd.optional4 AS invoicenumber,

exchangerate = CASE himdat.dbo.ebbudd.optional2 WHEN 'AUD'
THEN '1' ELSE SUM(himsys.dbo.cscrd.rate) / COUNT(himsys.dbo.cscrd.rate) END

FROM himdat.dbo.ebbudd, himsys.dbo.cscrd
WHERE himdat.dbo.ebbudd.field1 = 'P-05-012-01' AND
himsys.dbo.cscrd.ratedate BETWEEN '2007-05-10' AND '2007-05-20'

Cheers,
Andreas

 

[zhavic]

You should try something like this:

SELECT  ebbudd.budamt3 AS invoice,
        ebbudd.optional4 AS invoicenumber,
    
    exchangerate = CASE WHEN MAX( ebbudd.optional2 ) = 'AUD' THEN 1.0 
                        WHEN COUNT(himsys.dbo.cscrd.rate) = 0 THEN 1.0
                        ELSE SUM( ISNULL( cscrd.rate, 0.0 ) ) / COUNT( cscrd.rate) END
FROM     himdat.dbo.ebbudd AS ebbudd
LEFT JOIN himsys.dbo.cscrd AS cscrd ON cscrd.ratedate BETWEEN '2007-05-10' AND '2007-05-20'
WHERE     himdat.dbo.ebbudd.field1 = 'P-05-012-01'
GROUP BY ebbudd.budamt3, ebbudd.optional4

PS: if your column ratedate of cscrd is type datetime, you should change your BETWEEN statement to

cscrd.ratedate BETWEEN '2007-05-10' AND '2007-05-20 23:59:59.997'

or

cscrd.ratedate >= '2007-05-10' AND cscrd.ratedate < [b]'2007-05-21'[/b]

or

CONVERT( char(8), cscrd.ratedate, 112 ) BETWEEN '20070510' AND '20070520'

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[wysiwygger]

Thanks Zhavic,

I did it now like this. ISNULL in the case statement was the magic command!

SELECT
himdat.dbo.ebbudd.budamt3 AS invoice,
himdat.dbo.ebbudd.optional4 AS invoicenumber,
exchangerate = CASE himdat.dbo.ebbudd.optional2 WHEN 'AUD'
THEN '1' ELSE ISNULL(SUM(himsys.dbo.cscrd.rate), 1)
/ COUNT(ISNULL(himsys.dbo.cscrd.rate, 1)) END

FROM
himdat.dbo.ebbudd LEFT JOIN
himsys.dbo.cscrd ON
himsys.dbo.cscrd.sourcecur = himdat.dbo.ebbudd.optional2 AND
himsys.dbo.cscrd.ratedate BETWEEN
(SELECT cast(CONVERT(varchar(8), dateadd(day, - 5,
cast(cast(himdat.dbo.ebbudd.date AS varchar)
AS datetime)), 112) AS decimal)) AND
(SELECT cast(CONVERT(varchar(8), dateadd(day, + 5,
cast(cast(himdat.dbo.ebbudd.date AS varchar)
AS datetime)), 112) AS decimal))

WHERE
himdat.dbo.ebbudd.field1 = 'P-05-012-01'