Need help on Where clause

[LoriDurham]

Friends
I want to write a where clause in such a way that

I am getting all data from July(Current year) to July(next year) suppose by the end of year I have amount >0 then I need to get data
July(Previous year) to July(Current year).
DO i need to write a IF else statement in Where statement?. Please advice me.

Regards,
Lori

 

[blom0344]

Doubtful..

I would write a union to solve this one, like:

SELECT ............. FROM ........
INNER JOIN
<< SOME EXPRESSION FOR THE FIRST SCENARIO >>
ON
.........
UNION
SELECT ............. FROM ........
INNER JOIN
<< SOME EXPRESSION FOR THE SECOND SCENARIO >>
ON 
............

The rows that come from the 2 scenario expressions then control the output of the main data queries.

If you have more exact example , then it is easier to give example code..

Ties Blom

 

[LoriDurham]

Blom thanks for the quick reply. Below is sample data

I get data for fiscal year July 08 to june 09

then again next fiscal year start from july 09, now when i query i should get data july 09 to August 09.

Suppose if the total amount > 0 in june 09 then i need to display data in reverse
july09 to july08.
So basically I want to show data in incremental manner july09
august 09 sept 09 so on, but in other case when fiscl year eneded in june 09 and amount >0 then i want to show data in deceremental manner of months julu09 june 09 april 09 till july 08.

Can this be done?

 

[blom0344]

When you mean 'I get data', is this some single amount for the period that you query? Or do you fetch dimensions in the query?

'total amount > 0 in june 09'

Do you mean that you want to change the query conditions once any amount is registred in the new june month?

Example:

SELECT 1 AS CHECK1,SUM(AMOUNT) FROM TABLE WHERE .........
INNER JOIN
(SELECT 
CASE WHEN 
(SELECT SUM(AMOUNT) FROM TABLE WHERE YEAR(SOMEDATE)*100+MONTH(SOMEDATE) = 200906) > 0 THEN 1 ELSE 0 END AS CHECK2) TEMP
ON CHECK1 = TEMP.CHECK2
UNION
SELECT 1 AS CHECK1,SUM(AMOUNT) FROM TABLE WHERE .........
INNER JOIN
(SELECT 
CASE WHEN 
(SELECT SUM(AMOUNT) FROM TABLE WHERE YEAR(SOMEDATE)*100+MONTH(SOMEDATE) = 200906) = 0 THEN 1 ELSE 0 END AS CHECK2) TEMP
ON CHECK1 = TEMP.CHECK2

Unfortunately I have no DB2 instance available to check if this is entirely properly coded.
The idea is to perform the amount check and use it in a join.. (so either the first OR the second part of the union brings back any data)



Ties Blom

 

[LoriDurham]

Hi Blom,
How can I write where condtion that my query always fetches data from July of present year? no matter when i run the report if I run today, report needs to fetch the data for July, and when i run report next month in Sept I need to get data for July and August, so on. ANy ideas?
. Thank you so much for taking your time and helping me in this
Lori

 

[LoriDurham]

And my date format is 8/14/2009, I want to write a condtion like Datefield between Date(year(Current date),07,01) and
Current date. How to format the start date like 07/01/2009?

 

[LoriDurham]

Ok i tried I got it date( '07' || '-' || '01' || '-' || CHAR(YEAR(current date ))) as date_converted .
I will try your query logic now and will let you know

 

[blom0344]

Lori,

This may help perhaps:

http://www.tek-tips.com/viewthread.cfm?qid=1130040

Ties Blom

 

[LoriDurham]

Blom thank you I have got the where clause sucessfully. I will let you know once i get to the logic, you been a great help. Thank you
Lori