CODEWITHSUNDEEP
phpmysqlsqlreport
user1826306
user1826306

Reputation: 35

Product Statistic

I do the statistic function for my project. I have the order table with some fields as:

The customer ask me to do the auto scale statistic as: I get the MIN(order_date) of a produc_id and the current date to calculate the days:

I hope that can receive the ideas, examples from all of you about making the statistic as above.

Upvotes: 1

Views: 149

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You didn't specify what RDBMS is this. So let me assume that it is SQL Server, and you can do this:

;WITH MinDates AS
(
    SELECT 
      produc_id,
      MIN(order_date) order_date
    FROM products
    GROUP BY produc_id
), DatesWithIntervals AS
(
    SELECT 
      product_id,
      order_date,
      CASE 
        WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 31      THEN 1
        WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365     THEN 2
        WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365 * 2 THEN 3 
        ELSE 4
      END "Interval"
    FROM MinDates
)
SELECT
  product_id,
  order_date
  ...
FROM DatesWithIntervals 
--Do your statics here

You didn't specify what statistics do you want to compute. But you should be able to do whatever statistics you want to do using the last cte: DatesWithIntervals depending on the field Interval and I will leave it as a practice for you.

Edit: For MySQL, just replace all these CTEs with subqueries like so:

SELECT
  product_id,
  order_date
      ...
FROM
(
    SELECT 
      product_id,
      order_date,
      CASE 
        WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 31      THEN 1
        WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365     THEN 2
        WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365 * 2 THEN 3 
        ELSE 4
      END "Interval"
    FROM
    (
         SELECT 
          produc_id,
          MIN(order_date) order_date
         FROM products
         GROUP BY produc_id  
    ) MinDates
) DatesWithIntervals 
--Do your statics here

Upvotes: 1

Related Questions