CODEWITHSUNDEEP
mysqlsql
karim_fci
karim_fci

Reputation: 1242

Showing week date range along with week summary in mysql

I am working to prepare a report for my project where i am showing previous 4 week summary based on single column. Here is my query.

`SELECT `TBL33_ORGID`,
/* Trend DAK calcualation */
SUM(IF(WEEK(`tbl33_date`) = '12', `TBL33_DAK`, 0)) AS week_1st_dak,
SUM(IF(WEEK(`tbl33_date`) = '13', `TBL33_DAK`, 0)) AS week_2nd_dak,
SUM(IF(WEEK(`tbl33_date`) = '14', `TBL33_DAK`, 0)) AS week_3rd_dak,
SUM(IF(WEEK(`tbl33_date`) = '15', `TBL33_DAK`, 0)) AS week_4th_dak,
FROM `tbl33_snapshotorg`
WHERE   YEAR(`tbl33_date`) = '2015'
AND  (WEEK(`tbl33_date`) = '12' OR WEEK(`tbl33_date`) = '13' OR WEEK(`tbl33_date`) = '14'  OR WEEK(`tbl33_date`) = '15' )
GROUP BY `TBL33_ORGID``

My this query output is quite fine but now i need to show week start date and end date. I have been tried in following way but not worked perfectly.

`IF(WEEK(`tbl33_date`) = '12',MIN(`tbl33_date`),null) AS week_1st_start, 
IF(WEEK(`tbl33_date`) = '12',MAX(`tbl33_date`),null) AS week_1st_end,

IF(WEEK(`tbl33_date`) = '13',MIN(`tbl33_date`),null) AS week_2nd_start, 
IF(WEEK(`tbl33_date`) = '13',MAX(`tbl33_date`),null) AS week_2nd_end,

IF(WEEK(`tbl33_date`) = '14',MIN(`tbl33_date`),null) AS week_3rd_start, 
IF(WEEK(`tbl33_date`) = '14',MAX(`tbl33_date`),null) AS week_3rd_end,

IF(WEEK(`tbl33_date`) = '15',MIN(`tbl33_date`),null) AS week_4th_start, 
IF(WEEK(`tbl33_date`) = '15',MAX(`tbl33_date`),null) AS week_4th_end `

How can i achieve that without doing any extra query.

Upvotes: 0

Views: 144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

You wan the conditional expression inside the aggregation function -- "conditional aggregation":

SELECT MIN(CASE WHEN tbl33_date = 12 THEN tbl33_date END) as week_1st_start,
       MAX(CASE WHEN tbl33_date = 12 THEN tbl33_date END) as week_1st_end,
       . . .

Upvotes: 1

Related Questions