none
Need Help with COUNT

    Pregunta

  • Good morning,

    I have a sql that loads the FACT Table.

    ;WITH cte
    AS (SELECT T1.[issued],
                    T1.[renewal license accepted],
                    T1.[application accepted],
                    T1.[b1_alt_id],
                    captypename,
                    Datediff(dd, T1.[application accepted], T1.[issued])     AS
                    Days_till_application_accepted,
                    Datediff(dd, T1.[renewal license accepted], T1.[issued]) AS
                    Days_till_renew_application_accepted,
                    Count(casenumber)                                        AS cnt
             FROM   [gprocess_stg] T1
                    LEFT OUTER JOIN [g6action_stg] T2
                                 ON T1.b1_alt_id = T2.b1_alt_id
                    LEFT OUTER JOIN [fact_casedetails] T3
                                 ON T1.b1_alt_id = T3.casenumber
             WHERE  t3.casestatus = 'issued'
             GROUP  BY T1.[issued],
                       T1.[renewal license accepted],
                       T1.[application accepted],
                       T1.[b1_alt_id],
                       captypename,
                       Datediff(dd, T1.[application accepted], T1.[issued]),
                       Datediff(dd, T1.[renewal license accepted], T1.[issued]))
    SELECT [issued],
           [renewal license accepted],
           [application accepted],
           [b1_alt_id],
           captypename,
           days_till_application_accepted,
           days_till_renew_application_accepted,
           avg_days_issued = Avg(Cast(days_till_application_accepted AS
                                      DECIMAL(9, 2)))
                               OVER(),
           avg_days_renew_issued = Avg(Cast(days_till_renew_application_accepted AS
                                            DECIMAL
                                            (9, 2)))
                                     OVER(),
           cnt
    FROM   cte

    WheN i created the CUBE i have CNT as one of the measures. what i wanted in the excel report that i'm creating from the CUBE is Number of Case Number's per CAPTypeName per month based on the ISSUE DATE, but what i'm getting was the SUM. I Tried using the COUNT(CASENUMBER) OVER(PARTITION BY....) but in the CUBE am still gettinng the SUM values and not the detail count per captype name.

    What i'm seeing is

    FACT License Details Count Column   Labels
    Row Labels October 2011
    ADDS
    Licenses 56
    Company 20
    Alarm Company 20
    Application 20
    Licenses\Company\Alarm   Company\Application 20
    Historical 20
    Licenses\Company\Alarm   Company\Historical 20
    Amusement Location 20
    Application 20
    Historical 20
    Carriage Business Owner 20
    Cart Vendor Cart 20
    Cart Vendor Owner 20
    Dance Hall Annual License 20
    Dealers In Salvage-Scrap Metal 20
    Fire Extinguisher Service 20
    Hotel 20
    Kennel 20
    Non Consensual Tow Business 20
    Parking Facility 20
    Pawn Broker 20
    Pedal Cab Owner 20
    Pet Shop 20
    Public Pay Phone 20
    Public Vehicle For Hire Owner 20
    Public Vehicle For Hire Vehicl 20
    Scav Truck Refuse Hauler Owner 20
    Scav Truck Refuse Hauler Vehic 20
    Stable 20
    Company Adult Enterprises
    Individual 36
    Fire Extinguisher Serv Person 36
    Taxi Driver 36
    Ticket Broker 36
    Transient Merchant 36
    Individual Adult Enterprises

    Special Event Civic

    What i want to see is as :

    CAPTypeName COUNT
    Licenses\Company\Alarm   Company\Application 1
    Licenses\Company\Hotel\Application 12
    Licenses\Company\Kennel\Application 2
    Licenses\Company\Parking   Facility\Application 2
    Licenses\Company\Pet   Shop\Application 1
    Licenses\Company\Scav Truck   Refuse Hauler Owner\Application 2
    Licenses\Individual\Taxi   Driver\Application 36
    Thanks
                                                                                                  
     
     
     
     
     
     
     
     
     
     


    SV


    • Editado saivenkat77 viernes, 22 de junio de 2012 13:46
    viernes, 22 de junio de 2012 12:08

Respuestas

  • Hi,

    I'm not sure if fully understand the design of your fact and dimensions - sorry for that.

    Your fact table (the measures) should reflect the granularity of the process you want to measure. It seems that your process measures cases

    ... count(casenumber) ...

    But I can't identify a case dimension in your fact table so I would call this kind of fact table "aggregated fact table", personally I try to avoid this because often there comes trouble with this if i try to do some average calculations ...

    Normally I build the count Measure just in my MeasureGroup using the AggregateFunction COUNT. You can also define a column in your FactTable or a NamedQuery within the DataSourceView, in both cases this column is always empty. During the PROCESS of the MeasureGroup the corresponding Measure (AggregationFunction COUNT) gets to life and you use it to divide some other Measures (AggregateFunction SUM) to get your averages.

    Hope this helps somehow

    Tom

    • Marcado como respuesta saivenkat77 miércoles, 11 de julio de 2012 18:27
    sábado, 23 de junio de 2012 19:04

Todas las respuestas

  • Hi,

    I'm not sure if fully understand the design of your fact and dimensions - sorry for that.

    Your fact table (the measures) should reflect the granularity of the process you want to measure. It seems that your process measures cases

    ... count(casenumber) ...

    But I can't identify a case dimension in your fact table so I would call this kind of fact table "aggregated fact table", personally I try to avoid this because often there comes trouble with this if i try to do some average calculations ...

    Normally I build the count Measure just in my MeasureGroup using the AggregateFunction COUNT. You can also define a column in your FactTable or a NamedQuery within the DataSourceView, in both cases this column is always empty. During the PROCESS of the MeasureGroup the corresponding Measure (AggregationFunction COUNT) gets to life and you use it to divide some other Measures (AggregateFunction SUM) to get your averages.

    Hope this helps somehow

    Tom

    • Marcado como respuesta saivenkat77 miércoles, 11 de julio de 2012 18:27
    sábado, 23 de junio de 2012 19:04
  • Thanks Tom

    SV

    miércoles, 11 de julio de 2012 18:28