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

nononame2021 261 Reputation points
2022-10-18T06:52:32.383+00:00

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

Developer technologies | Transact-SQL
SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 31,576 Reputation points
    2022-10-18T07:19:25.307+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 47,516 Reputation points
    2022-10-18T07:05:59.327+00:00

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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.