Need Help with COUNT

• viernes, 22 de junio de 2012 12:08

   

   
  

  0

  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
  •  
  • Responder
  • Citar