Write a SQL at the beginning of the quarter for this date with hive

Because the project needs to get (the sum of occurrences in this quarter)/(number of days in the quarter) data (plus monthly average, average annual data).

Judging the time of the season is a difficult point, the beginning of the time to write a bunch of cases when to judge the month, and then write the discovery, it is too stupid. So I began to think of ways.

Suddenly brainwave, a quarter is 3 months for one body. Why not start with the first quarter of the day, then multiply by 3 and start the quarter.

Because March divided by 3 is equal to 1. You must have a number that is greater than 3, and the number of months is only 12. Don't worry about dividing by 3.1.

The following is the calculation of the first quarter of SQL

Select Floor (substr (' 2017-02-04 ', 6,2)/3.1) +1

It was easy after the first quarter. The initial date of the quarter is the direct one.

Select (Floor (substr (' 2017-02-04 ', 6,2)/3.1) +1

Specific source code:

Select Bill_no,

SUM (case if Part_dt >= concat (substr (' ${start_date} ', 1,7), ' -01 ') and part_dt<= ' ${start_date} ' then NVL (amt,0) else 0 End)/((DATEDIFF (' ${start_date} ', CONCAT (SUBSTR (' ${start_date} ', 1,7), ' -01 ')) +1)) Month_da,

SUM (case when Part_dt >= concat (substr (' ${start_date} ', 1,4), '-', substr (concat (' xx '), Floor (substr (' ${start_date} ' , 6,2)/3.1) *3+1), -2), ' -01 ') and part_dt<= ' ${start_date} ' then NVL (amt,0) Else 0 End)/((DATEDIFF (' ${start_date} ', CONCAT (SUBSTR (' ${start_date} ', 1,4), '-', SUBSTR (CONCAT (' xx ', Floor (SUBSTR (' ${start_date} ', 6,2)/3.1) *3+1),-2), '-01 ')) (+1)) Quarter_da,

SUM (case if Part_dt >= concat (substr (' ${start_date} ', 1,4), ' -01-01 ') and part_dt<= ' ${start_date} ' then Nvl (AMT, 0) Else 0 end)/((DATEDIFF (' ${start_date} ', CONCAT (SUBSTR (' ${start_date} ', 1,4), ' -01-01 ')) +1) Year_da

From Xxxx_table_xxxx DLT

GROUP BY Bill_no

Write a SQL at the beginning of the quarter for this date with hive