Rank Over Partition

[LeonelSanchezJr]

I need to get the TOP 10 Cars NOT Made per week.
This is the code I am using, but not getting the correct results.

SELECT
*
FROM
(
SELECT
MODEL,
SUM(CASE WHEN CAR_CREATE_DATE >= (TRUNC(SYSDATE) -7)
THEN 1
ELSE 0
END) TTL_CARS_MADE,
SUM(CASE WHEN CAR_CREATE_DATE IS NULL
THEN 1
ELSE 0
END
END) CARS_NOT_BUILT,

RANK() OVER (PARTITION BY MODEL ORDER BY SUM(CASE WHEN CREATE_DT IS NULL
THEN 1
ELSE 0
END) DESC) EXPRANK,
ROW_NUMBER() OVER (PARTITION BY MODEL ORDER BY SUM(CASE WHEN CREATE_DT IS NULL
THEN 1
ELSE 0
END) DESC) RN

FROM CAR_TABLE
WHERE CAR_DATE >= TRUNC(SYSDATE) -7

GROUP BY MODEL
)
WHERE RN <= 10



Thanks,

Leo ;-)

 

[SantaMufasa]

Leo,

It would be extremely helpful to have you post the results that you are getting, along with a description of what you don't like about the results (rather than making us guess what you don't like about them).

Also, what, precisely, does it mean (from a data perspective) when you say, "I need to get the TOP 10 Cars NOT Made per week."...What column of data tells you how many cars were NOT made in a given week?

(Example: I did NOT drive over 20 million miles this week. <grin>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LeonelSanchezJr]

Sorry about that Mufasa.
Let me rephrase it then.
Let's say I want the top 3
I am getting 90 records instead of the Top 3 for the CARS not being manufactured.

Example:
We manufacture the following cars:
ACURA
BMW,
CHEVROLET,
FORD,
JAGUAR
MAZARRATI
PONTIAC
SATURN
VOLKSWAGEN

I need to see a list of all cars manufactured in the last week and those NOT shipped. So I first do a count of total cars manufactured within the last 7 days, then I do a SUM CASE statement of those NOT SHIPPED within this time frame.

So, I want to see the following:

CAR TOTAL_BUILT TOTAL_NOT_SHIPPED
VOLKSWAGEN 400 395
MAZARRATI 250 240
JAGUAR 125 90




Thanks,

Leo ;-)

 

[SantaMufasa]

Leo,

Just one more clarification:

How do "CAR_CREATE_DATE", "CREATE_DT", and "CAR_DATE" compare to one another? What is the name of the column that stores the "SHIPPED_DATE"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LeonelSanchezJr]

Let me rephrase the code:
CAR is the name of the car being built/shipped.
BUILT_DATE is the date a CAR is built.
SHIP_DATE is the date a CAR is Shipped.

Again, what I am trying to achieve is a RANKED version of the TOP 10 Cars Built, BUT NOT shipped.

I have been able to do this for other reports, but have had no luck on this one.

SELECT /*+RULE*/
CAR,
COUNT(BUILT_DATE) TOTAL_BUILT,
SUM(CASE WHEN SHIP_DATE IS NULL
THEN 1
ELSE 0
END) CARS_NOT_SHIPPED,
RANK() OVER (PARTITION BY CAR ORDER BY
SUM(CASE WHEN SHIP_DATE IS NULL
THEN 1
ELSE 0
END) DESC) EXPRANK,
ROW_NUMBER() OVER (PARTITION BY CAR ORDER BY
SUM(CASE WHEN SHIP_DATE IS NULL
THEN 1
ELSE 0
END)
DESC) RN
FROM CAR_TABLE

WHERE BUILT_DATE >= TRUNC(SYSDATE) - 7
BUILT_DATE < = TRUNC(SYSDATE)

GROUP BY CAR



Thanks,

Leo ;-)

 

[Dagon]

It sounds as if you want something more like this:

select car,
       car_count
from
(select car,
       car_count,
       rank() over (partition by car order by car_count desc) as rnk
from
(select car,
        count(case when car_built_date >= sysdate-7
              and ship_date is null then 1 end) over (partition by car) as car_count
from car_table))
where rnk <= 5

 

[LeonelSanchezJr]

Thank you, don't I need a GROUP BY though?


Thanks,

Leo ;-)

 

[Dagon]

No, you only need a group by with simple sum and count functions. Windowed functions don't need it.