Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: Using a count case Distinct but specifying column name

    Hi I wonder if anybody can help.

    I am trying to use a count case statement with a distinct. I know how to use a generic one (See SQL 1 fro complete querybelow)

    e.g
    COUNT(distinct CASE
    WHEN TASKSTATUS not in ('Closed','Cancelled') THEN 1
    ELSE null
    END) AS "No. Txns"
    However, I would like do a distinct on a particalar column for the thw where clause.

    Although, the following is not the correct sytax - it is roughly what I want to acheive

    COUNT(distinct (E.TRANSRE) CASE
    WHEN TASKSTATUS not in ('Closed','Cancelled') THEN 1
    ELSE null
    END) AS "No. Txns"

    I would really appreciate some help on this.

    Kind Regards,

    Saj

    SQL 1
    =====





    SELECT
    tasktypename, username, A.extTransType,
    COUNT(case
    when TASKSTATUS not in ('Closed','Cancelled') then 1
    ELSE null
    end) "Tasks Open", -- Open Tasks ***OK****
    COUNT(case
    when CAST(v.TASKCLOSEDDATE AS DATE) = CAST(GETDATE()- 1 AS DATE) and TASKSTATUS = 'Closed' then 1
    else null
    end) "Done Yesterday",

    COUNT(distinct CASE
    WHEN TASKSTATUS not in ('Closed','Cancelled') THEN 1
    ELSE null
    END) AS "No. Txns"

    FROM
    TOCTASK A,
    TECFTRANSACTIONPRESENTATION B,
    TECFTRANSACTION E,
    VTASKEXTRACT V
    WHERE A.C_OCDOC_ACTIVITIES = B.C_OCEXDATA_EXTRAPRESENTA
    AND A.I_OCDOC_ACTIVITIES = B.I_OCEXDATA_EXTRAPRESENTA
    AND E.C = B.C_ECFTRANS_PRESENTATIONS
    AND E.I = B.I_ECFTRANS_PRESENTATIONS
    AND V.TASKID = CONVERT(VARCHAR(20),A.I)
    and V.USERNAME is not null
    and TASKTYPENAME = 'Respond To ECF Transaction'
    and USERNAME = Joe Blogs' and TASKSTATUS not in ('Cancelled')
    group by TASKTYPENAME, username,A.extTransType

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    COUNT(distinct
    CASE
    WHEN TASKSTATUS not in ('Closed','Cancelled') THEN E.TRANSRE
    ELSE null
    END) AS "No. Txns"

  3. #3
    Join Date
    Jul 2012
    Posts
    2
    Tonkuma - you are a life saver. Many many thanks for your help.

    Kind Regards,

    Saj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •