CODEWITHSUNDEEP
phpmysqlsql
user2757801
user2757801

Reputation:

mysql month wise report

I need an output like this:

Month -->| Pre_Mnth2 | Pre_Mnth1 | current_Month_ Name
Product A | 3387 | 87985 | 2338
Product B | 6386 | 67983 | 6374
Product C | 3880 | 76988 | 9378

...

All the required data is in single table only.

Column looks like this:

id | companycode | merchantcode| pdtname | qty | value | invdate |

Can anybody help me with php mysql query code?

My current code returns the data for current month only

SELECT `pdtname`, `qty`, `value`, sum(`qty`), sum(`value`)
FROM `ist` 
WHERE merchantcode = $q AND companycode = $companycode 
    AND MONTH(invdate) = $currentdate[mon]
GROUP BY `pdtname`

Here variable $q and $companycode is taken from user session.

Upvotes: 1

Views: 1822

Answers (1)

peterm
peterm

Reputation: 92785

Are you looking for something like this?

SELECT pdtname,
       SUM(CASE WHEN invdate 
           BETWEEN LAST_DAY(CURDATE()) - INTERVAL 3 MONTH + INTERVAL 1 DAY 
               AND LAST_DAY(CURDATE()) - INTERVAL 2 MONTH THEN qty END) qty_2m,
       SUM(CASE WHEN invdate 
           BETWEEN LAST_DAY(CURDATE()) - INTERVAL 3 MONTH + INTERVAL 1 DAY 
               AND LAST_DAY(CURDATE()) - INTERVAL 2 MONTH THEN value END) value_2m,
       SUM(CASE WHEN invdate 
           BETWEEN LAST_DAY(CURDATE()) - INTERVAL 2 MONTH + INTERVAL 1 DAY 
               AND LAST_DAY(CURDATE()) - INTERVAL 1 MONTH THEN qty END) qty_1m,
       SUM(CASE WHEN invdate 
           BETWEEN LAST_DAY(CURDATE()) - INTERVAL 2 MONTH + INTERVAL 1 DAY 
               AND LAST_DAY(CURDATE()) - INTERVAL 1 MONTH THEN value END) value_1m,
       SUM(CASE WHEN invdate 
           BETWEEN LAST_DAY(CURDATE()) - INTERVAL 1 MONTH + INTERVAL 1 DAY 
               AND LAST_DAY(CURDATE()) THEN qty END) qty_m,
       SUM(CASE WHEN invdate 
           BETWEEN LAST_DAY(CURDATE()) - INTERVAL 1 MONTH + INTERVAL 1 DAY 
               AND LAST_DAY(CURDATE()) THEN value END) value_m
  FROM ist
 WHERE invdate BETWEEN LAST_DAY(CURDATE()) - INTERVAL 3 MONTH + INTERVAL 1 DAY 
                   AND LAST_DAY(CURDATE())
   AND companycode  = 1
   AND merchantcode = 1
 GROUP BY pdtname

Note: Make sure that you have an index on invdate.

Output:

|  PDTNAME | QTY_2M | VALUE_2M | QTY_1M | VALUE_1M | QTY_M | VALUE_M |
|----------|--------|----------|--------|----------|-------|---------|
| ProductA |     30 |     3000 |     30 |     3000 |    30 |    3000 |
| ProductB |    100 |     1000 |    100 |     1000 |   100 |    1000 |
| ProductC |    320 |     3200 |    320 |     3200 |   320 |    3200 |

Here is SQLFiddle demo

Upvotes: 2

Related Questions