Re: Help with SQL Statement & Subtraction/Minus

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Tue, 16 May 2006 19:40:16 +0200
Message-ID: <446a0fb6$0$4510$9b4e6d93@newsread2.arcor-online.net>

ct witter schrieb:
> Looking for help coding this SQL statement
>
> Table1
>
> has
> ID, ID_DESCRIPTION, SERVICE_DATE, SUB_CODE
>
> sample data
>
> ID, ID_DESCRIPTION, SERVICE_DATE, SUB_CODE
> 1, 1234, 4/1/2005,
> 2, 1234, 4/1/2005, X
> 3, 1234, 4/1/2005,
> 4, 1111, 7/1/2005,
> 5, 1111, 7/1/2005, X
>
> What I want to do is get a count of Unique items per day.
>
> However, if there is a SUB_CODE (X) then that should be subtracted.
>
> So the final table should be
>
> ID_DESCRIPTION, SERVICE_DATE
> 1234, 4/1/2005,
>
>
> Thanks!
>

SELECT *
FROM (SELECT Service_Date,

                COUNT(Id_Description) -
                2 * COUNT(Decode(Sub_Code, 'X', 'X', NULL)) Cnt
         FROM   Table1 t
         GROUP  BY Service_Date)

WHERE Cnt != 0

If sub_code may contain only 'X' and NULL, you can leave out decode expression.

Best regards

Maxim Received on Tue May 16 2006 - 12:40:16 CDT