Help with limiting what is being selected from database

[luckydexte]

Hello all,

I am trying to write a query to select data from a database but do not know how to limit what is being selected. I need to use the SUM function to get the total from all of the records that are returned in the where clause but only want the DATE when records have a TRANS_TYPE of 0. Here is my SQL query (Note that TRANS_TYPE is only there for display purposes and will not be included in my query):

SELECT SITE_NO, FEE_TYPE, DATE, ACCT_NBR, TRANS_TYPE,
SUM(TRANS_AMT * CASE WHEN TRANS_TYPE IN('2','5','7') THEN -1 ELSE 1 END) AS TOTAL
FROM ROW_FEE_TRANSACTION
WHERE PERMIT_NO = '2'
And DATE >= '03/01/2008'
And DATE <= '03/31/2008'
Group By SITE_NO, FEE_TYPE, ACCT_NBR, DATE, TRANS_TYPE
Order By SITE_NO

Here is a result set:

SITE_NO FEE_TYPE DATE ACCT_NBR TRANS_TYPE TOTAL
57132 MF01 31-MAR-08 1315.5901 0 100
57132 MI01 30-MAR-08 1315.6032.INSP 2 -4
57132 MI01 31-MAR-08 1315.6032.INSP 0 8
57132 MP01 31-MAR-08 1315.6032.APPL 0 11

What I really want is to only select an DATE when the TRANS_TYPE is 0. This would give me a unique SITE_NO, FEE_TYPE, ACCT_NBR and TOTAL. I would not include TRANS_TYPE in the query and would get the following result set:

SITE_NO FEE_TYPE DATE ACCT_NBR TOTAL
57132 MF01 31-MAR-08 1315.5901 100
57132 MI01 31-MAR-08 1315.6032.INSP 4
57132 MP01 31-MAR-08 1315.6032.APPL 11

I can get the above result set if I remove the DATE column but I do need it. Any help would be greatly appreciated.

Thanks in advance,

Brandon

 

[SQLSister]

I'd look at the Case keyword in Books on Line if I were you.

"NOTHING is more important in a database than integrity." ESquared

 

[foxdev]

If I properly understand what you're after:

case when trans_type=0 then date else null end

--------------

http://www.liquidsql.com

SQLS metasearch

 

[luckydexte]

Thanks. That is what I came up with but I still get a row with a null date. The database I am using is terrible so there is really no work around. I talked to the user and got the requirements changed so the date is not a problem.

Thanks for looking at it.

Cheers,

Brandon

 

[SQLSister]

you don't want the rows with the null dates? Then you need a where clause
where trans_type =0

"NOTHING is more important in a database than integrity." ESquared

 

[mikeraut]

By the way, you can use the BETWEEN command for date ranges

And DATE >= '03/01/2008'
And DATE <= '03/31/2008'

can be as modified as follows:

and DATE BETWEEN '03/01/2008 AND '03/31/2008'