SQLTeam.com | Weblogs | Forums

Re-writing sql code using another column to get same result


#1

Supposed the following scenario

CREATE TABLE card (date_field, numcard, month);

INSERT INTO card (date_filed, numcard, month) 
VALUES
    (2018-06-01, 12531, June-2018),
    (2018-06-02, 29182, June-2018),
    (2018-05-01, 12781, May-2018),
    (2018-05-29, 56171, May-2018),
    (2018-05-10, 27191, May-2108),
    (2018-04-10, 83231, April-2018),
    (2018-03-01, 31131, March-2018),
    (2018-03-02, 47131, March-2018),
    (2018-02-15, 34617, February-2018);

Using the column date_field in the following SQL code (MySQL)...

SELECT count(CASE
               WHEN date_field >= current_date - interval 2 month - interval dayofmonth(current_date) - 1 day
                    AND date_field < current_date - interval 1 month - interval dayofmonth(current_date) - 1 day THEN
                 1
             END) `M`,
       count(CASE
               WHEN date_field >= current_date - interval 3 month - interval dayofmonth(current_date) - 1 day
                    AND date_field < current_date - interval 2 month - interval dayofmonth(current_date) - 1 day THEN
                 1
             END) `M-1`,
       count(CASE
               WHEN date_field >= current_date - interval 4 month - interval dayofmonth(current_date) - 1 day
                    AND date_field < current_date - interval 3 month - interval dayofmonth(current_date) - 1 day THEN
                 1
             END) `M-3`,
       FROM my_table
       WHERE date_field >= current_date - interval 4 month - interval dayofmonth(current_date) - 1 day
             AND date_field < current_date - interval 1 month - interval dayofmonth(current_date) - 1 day;

I have this result...

Result

How can I have the same result (same format) using the column: month
P.S: the datatype of the column month is varchar


#2

This site is dedicated to Microsoft SQL Server so you might be better of, finding a forum for MySQL.

Anyway, lookup these terms:

  • prepare
  • execute
  • deallocate