find out the date to belong in which quarter by sql ,case 1 script

Question

I have many different case, 1 of the case is here.the other case, will ask in another post.

I have a date (e.g. 2022-2-15 00:00:00:000), and I have amount in customer table
how can i find out a date belong to which quarter in 2022 ?

customer table (
customerid numeric,
policy_no varchar(16),
start_date SWDATE (datetime ),
amount numeric)
date field with datatype start_date , SWDATE (DATETIME)

AND I have a temp table (
customerid (numeric),
policy_no varchar(16),
start_date swdate(datetime),
Q1 NUMERIC,
Q2 NUMERIC,
Q3 NUMERIC
)
how can I find out the start_date is belong which and insert amount into that column. (q1,q2,or q3)

IF BELONG 2022 Q1, THEN INSERT AMOUNT TO Q1 COLUMN
ELSE IF
START DATE BELONG 2022 Q2, THEN INSERT AMOUNT INTO Q2 COLUMN
ELSE IF
START DATE BELONG 2022 Q3, THEN INSERT AMOUNT INTO Q2 COLUMN
ELSE
DO NOTHING

Answer 1

Hi @nononame2021
Please check this sample:

create table customer(  
customerid numeric,  
policy_no varchar(16),  
start_date datetime ,  
amount numeric)  
;  
create table temp (  
customerid numeric,  
policy_no varchar(16),  
start_date datetime,  
Q1 NUMERIC,  
Q2 NUMERIC,  
Q3 NUMERIC,  
Q4 NUMERIC  
)  
insert into customer values  
(111,'aa','2022-2-15 00:00:00:000',1000),  
(111,'aa','2022-5-15 00:00:00:000',2000),  
(111,'aa','2022-8-15 00:00:00:000',3000),  
(111,'aa','2022-11-15 00:00:00:000',4000)  
  
select * from customer;select * from temp;  

insert into temp  
select customerid,policy_no,start_date  
      ,case when datepart(QUARTER,start_date)=1 then amount else null end as Q1  
	  ,case when datepart(QUARTER,start_date)=2 then amount else null end as Q2  
	  ,case when datepart(QUARTER,start_date)=3 then amount else null end as Q3  
	  ,case when datepart(QUARTER,start_date)=4 then amount else null end as Q4  
from customer  

select * from temp;  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can use DATEPART (Transact-SQL) with parameter QUARTER.
You know, we have 4 quarters, nor only 3?