CODEWITHSUNDEEP
mysqlsql
Kevin T
Kevin T

Reputation: 51

Selecting data by year, month and week from table

I have the following tables:

Table: promotion_asset
promotion_id
asset_id

(a promotion can have many assets)

Table: promotion_tracking
id(PK)
asset_id
date_scanned

(logging table for every time an asset is scanned )

I want to TRY in one query to be able to identify how many times assets in a specific promotion ID, have been scanned: this year, this month, and this week.

i've tried lots of different methods but cannot find a single query that returns the correct results.

Upvotes: 1

Views: 221

Answers (1)

Adam Wenger
Adam Wenger

Reputation: 17540

I only have sql server, so I was able to test this for the Year and Month functions, looked up the WeekOfYear for mysql

SELECT pa.promotion_id
   , SUM(CASE
        WHEN YEAR(pt.date_scanned) = YEAR(GETDATE()) THEN 1
        ELSE 0
     END CASE) AS ThisYear
   , SUM(CASE
        WHEN YEAR(pt.date_scanned) = YEAR(GETDATE())
           AND MONTH(pt.date_scanned) = MONTH(GETDATE()) THEN 1
        ELSE 0
     END CASE) AS ThisMonth
   , SUM(CASE
        WHEN WEEKOFYEAR(pt.date_scanned) = WEEKOFYEAR(GETDATE()) THEN 1
        ELSE 0
     END CASE) AS ThisWeek
FROM promotion_tracking AS pt
INNER JOIN promotion_asset AS pa ON pt.asset_id = pa.asset_id
GROUP BY pa.promotion_id

Upvotes: 1

Related Questions