CODEWITHSUNDEEP
sqldb2crosstabdatepart
CarrieK
CarrieK

Reputation: 1

DB2 SQL - How to display a count for the past 12 weeks, each week being its own column

Using DB2 SQL, I need to pull data for the past 12 full weeks. (This report will be ran each week, and each week I need the past 12 full weeks to be pulled). I need the weeks to be in columnar format, with a count in each week. So two issues: I'm not sure how to pull the past 12 weeks. And I'm not sure how to get these weeks in their own columns.

Sample Desired Output: 

ACCOUNT  WEEK1_CT  WEEK2_CT  WEEK3_CT....   WEEK12_CT
 123       2          2         3              5
 789       6          3         6              6
 333       18         20        15             20

Sample Query with logic I am trying to use to pull back the past 12 full weeks. Receiving error that reads : 'WEEK' is not valid in the context where it is used. Not sure if this is the correct path, however. 

SELECT 
C.ACCOUNT_NUM,
DATEPART(WEEK,D.LST_SENT_DT) AS WEEK,
COUNT(DISTINCT A.SPECIMEN_NUM) AS COUNT

FROM 
FACT    A, 
FIN_ORG B, 
ACCOUNT C, 
DATE    D

WHERE 
A.FINANCIAL_SRGT   = B.FINANCIAL_SRGT AND 
A.CLIENT_SRGT      = C.CLIENT_SRGT AND 
A.LST_SENT_DT_SRGT = D.LST_SENT_DT_SRGT AND 
B.DIVISION_NUM = 'W' AND 
datepart(week, D.LST_SENT_DT) between (date_trunc('week', current_date) 
-12) 
AND date_trunc('week', current_date)  
GROUP BY 
C.ACCOUNT_NUM, DATEPART(WEEK, D.LST_SENT_DT)

Sample Table Data from DATE table: 

LST_SENT_YR   LST_SENT_MTH_NUM   LST_SENT_WK  LST_SENT_DT
2019             1                 3           1/16/2019
2019             1                 4           1/24/2019
2019             4                 14          4/2/2019
2019             4                 14          4/6/2019

Upvotes: 0

Views: 383

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

WITH 
  T (WEEK_NUM, WEEK_END_DT) AS 
(
VALUES (1, DATE('2020-01-02') - DAYOFWEEK_ISO(DATE('2020-01-02')))
  UNION ALL
SELECT WEEK_NUM + 1, WEEK_END_DT - 7
FROM T
WHERE WEEK_NUM < 12
)
, WEEKS AS
(
SELECT WEEK_NUM, WEEK_END_DT - 6 AS WEEK_START_DT, WEEK_END_DT
FROM T
)
, ACCOUNT_INFO (ACCOUNT, DT, CT) AS 
(
VALUES 
  (1, DATE('2019-12-25'), 5)
, (1, DATE('2019-12-26'), 10)
, (2, DATE('2019-12-01'), 20)
)
SELECT 
  D.ACCOUNT
, SUM(CASE W.WEEK_NUM WHEN  1 THEN A.CT ELSE 0 END) AS WEEK1_CT
, SUM(CASE W.WEEK_NUM WHEN  2 THEN A.CT ELSE 0 END) AS WEEK2_CT
, SUM(CASE W.WEEK_NUM WHEN  3 THEN A.CT ELSE 0 END) AS WEEK3_CT
, SUM(CASE W.WEEK_NUM WHEN  4 THEN A.CT ELSE 0 END) AS WEEK4_CT
, SUM(CASE W.WEEK_NUM WHEN  5 THEN A.CT ELSE 0 END) AS WEEK5_CT
, SUM(CASE W.WEEK_NUM WHEN  6 THEN A.CT ELSE 0 END) AS WEEK6_CT
, SUM(CASE W.WEEK_NUM WHEN  7 THEN A.CT ELSE 0 END) AS WEEK7_CT
, SUM(CASE W.WEEK_NUM WHEN  8 THEN A.CT ELSE 0 END) AS WEEK8_CT
, SUM(CASE W.WEEK_NUM WHEN  9 THEN A.CT ELSE 0 END) AS WEEK9_CT
, SUM(CASE W.WEEK_NUM WHEN 10 THEN A.CT ELSE 0 END) AS WEEK10_CT
, SUM(CASE W.WEEK_NUM WHEN 11 THEN A.CT ELSE 0 END) AS WEEK11_CT
, SUM(CASE W.WEEK_NUM WHEN 12 THEN A.CT ELSE 0 END) AS WEEK12_CT
FROM WEEKS AS W, (SELECT DISTINCT ACCOUNT FROM ACCOUNT_INFO) D
LEFT JOIN ACCOUNT_INFO A ON A.DT BETWEEN W.WEEK_START_DT AND W.WEEK_END_DT AND A.ACCOUNT = D.ACCOUNT
GROUP BY D.ACCOUNT;

|ACCOUNT    |WEEK1_CT   |WEEK2_CT   |WEEK3_CT   |WEEK4_CT   |WEEK5_CT   |WEEK6_CT   |WEEK7_CT   |WEEK8_CT   |WEEK9_CT   |WEEK10_CT  |WEEK11_CT  |WEEK12_CT  |
|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|
|1          |15         |0          |0          |0          |0          |0          |0          |0          |0          |0          |0          |0          |
|2          |0          |0          |0          |0          |20         |0          |0          |0          |0          |0          |0          |0          |

You may run this query as is.
We generate a table WEEKS with past 12 weeks based on any given date in the form of:
Week_number, Monday_Date, Sunday_Date
Then we join this table to our table (ACCOUNT_INFO) with account, date and a column to aggregate (CT).
Finally, we group by account pivoting the result.

Upvotes: 1

Related Questions