Grouping Dates By Year, Month, And Continuity In SQL And SPL
We have a database table TBLDATES, we want to group dates by year & month, separate continuous dates with hyphen and discontinuous ones with comma. SQL in MySQL uses tricky method, but esProc SPL makes it easy!
We have a database table TBLDATES as follows: We are trying to group the dates by year and month, and in each group, separate continuous dates with the hyphen and the discontinuous dates with the comma. Below is the desired result: The result table is ordered by dates which are grouped by year and month. Continuous dates are connected by the hyphen (-) and discontinuous ones are connected by the comma. SQL in MySQL: with_counter AS ( SELECT * , CASE WHEN LAG(DATES) OVER(PARTITION BY MONTH(DATES) ORDER BY DATES) + 1 < DATES OR LAG(DATES) OVER(PARTITION BY MONTH(DATES) ORD...