How to make my query more dynamic to pull quarters of the year without hardcoing the Dates

pizaro 101 Reputation points
2022-04-12T07:16:25.133+00:00

How do I write a query to be more dynamic to pull all 4 quarters in a year instead of hard coding the Start dates in the Where clause.

--1st Quarter

  SELECT 
         VENDOR,
         TYPE,
    start_date

FROM Filemain
 where start_date = '2022-01-01'

    union all

--2nd Quarter

SELECT
VENDOR,
TYPE,
start_date

FROM Filemain
where start_date = '2022-04-01'

union all

``--3rd Quarter

SELECT
VENDOR,
TYPE,
start_date

FROM Filemain
where start_date = '2022-07-01'

union all

--4th Quarter

SELECT
VENDOR,
TYPE,
start_date

FROM Filemain
where start_date = '2022-10-01'

The second part is I want to add a dynamic column that will have the name + year + quarter
something like this:

--4th Quarter

       SELECT 

         'Detail_2022_Q4' as DynamicColumn

         FROM Filemain
         where start_date = '2022-10-01' 
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,111 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
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points
    2022-04-12T08:20:34.72+00:00

    Hi,@pizaro

    Welcome to Microsoft T-SQL Q&A Forum!

    Please check this:

       SELECT VENDOR,TYPE,start_date  
        CASE  
            WHEN MONTH(start_date) IN (1,2,3)  THEN 'Detail'+'_'+convert(char(4), YEAR(start_date)) +'_' + 'Q1'  
            WHEN MONTH(start_date) IN (4,5,6)  THEN  'Detail'+'_'+convert(char(4), YEAR(start_date))+'_' + 'Q2'  
            WHEN MONTH(start_date) IN (7,8,9)  THEN  'Detail'+'_'+convert(char(4), YEAR(start_date)) +'_'+ 'Q3'  
            WHEN MONTH(start_date) IN (10,11,12) THEN  'Detail'+'_'+convert(char(4), YEAR(start_date))+'_' + 'Q4'  
        END AS DynamicColumn    
        FROM Filemain  
    where year(start_date )= 2022  
    

    Best regards,
    Bert Zhou


    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.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Naomi 7,366 Reputation points
    2022-04-12T12:49:38.73+00:00

    Try:

    SELECT VENDOR,TYPE,start_date
         CASE
             datepart(quater, start_date) WHEN 1  THEN 'Detail'+'_'+convert(char(4), YEAR(start_date)) +'_' + 'Q1'
             WHEN 2  THEN  'Detail'+'_'+convert(char(4), YEAR(start_date))+'_' + 'Q2'
             WHEN 3  THEN  'Detail'+'_'+convert(char(4), YEAR(start_date)) +'_'+ 'Q3'
             WHEN 4 THEN  'Detail'+'_'+convert(char(4), YEAR(start_date))+'_' + 'Q4'
         END AS DynamicColumn  
         FROM Filemain
     where start_date >= '20210101' and end_date <'20220101'
    
    1 person found this answer helpful.
    0 comments No comments

  2. Tom Phillips 17,716 Reputation points
    2022-04-12T12:30:59.477+00:00
    0 comments No comments