2 Replies Latest reply: Mar 26, 2013 2:51 AM by Navneet Singh RSS

    Return Max of a Count in QVD

    Bruce Tedder

      Hi Guys

       

      Here is a query I tried to do in SQL but my IT Team say its too intensive on the db, and suggested if find a way in the QVD to find a solution.

      I am trying to first count the quotes by Cust & Region then return the Cust and Region on the Max Number of Quotes.

      Is there a way to use my QM.QVD to get the same?

       

      Here is what I ran in SQL:

      WITH recordsList

      AS

      (

          SELECT  QM_CUST_CODE, QM_XX_REGION, COUNT(QM_QUOTE_NUM) as Quotes,

                  DENSE_RANK() OVER  (PARTITION BY QM_CUST_CODE

                                      ORDER BY COUNT(QM_QUOTE_NUM) DESC) rn

          FROM    QM

          WHERE   QM_QUOTE_JOB = 0 --signifies that its a quote not a job

          GROUP   BY QM_CUST_CODE, QM_XX_REGION

      )

      SELECT  QM_CUST_CODE, QM_XX_REGION, Quotes

      FROM    recordsList

      WHERE   rn = 1

        • Re: Return Max of a Count in QVD
          Nirav Bhimani

          Hi,

           

          I think you need to pull all the field from the base table then in the resident table pull the few field on which you want to do group by clause, then do the order by Quotes.

           

          Regards,

          Nirav Bhimani

          • Re: Return Max of a Count in QVD
            Navneet Singh

            Hi

             

            Did you tried without count in order by clause? Try once, i think this will help you to reduce cost.

             

            WITH recordsList

            AS

            (

                SELECT  QM_CUST_CODE, QM_XX_REGION, COUNT(QM_QUOTE_NUM) as Quotes,

                        DENSE_RANK() OVER  (PARTITION BY QM_CUST_CODE

                                            ORDER BY QM_QUOTE_NUM DESC) rn

                FROM    QM

                WHERE   QM_QUOTE_JOB = 0 --signifies that its a quote not a job

                GROUP   BY QM_CUST_CODE, QM_XX_REGION

            )

            SELECT  QM_CUST_CODE, QM_XX_REGION, Quotes

            FROM    recordsList

            WHERE   rn = 1