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

nononame2021 256 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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,104 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 24,766 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 42,561 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