Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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