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

Question

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' 

Answer 1

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.

Answer 2

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'

Answer 3

Use a calendar table:

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/