Future dates query

Question

Hi Team

i have few orders which are getting expired in coming months. I have attached the sample data as input and I would need the output for next twelve months how many orders are getting expire based on Month wise.

kindly provide some solution. It has to be in MMM-YY format ( like Oct-20, feb-21 so many order are going to expire etc.

please refer the attached file for input and output details.36083-future-dates.txt

Answer 1

Check this variation:

declare @table as table ([Date] date, [expiring orders] int)  
  
insert @table values  
( '11/1/2020 ', 234 ),  
( '11/21/2020 ', 765 ),  
( '11/30/2020 ', 127 ),  
( '12/1/2020 ', 145 ),  
( '12/18/2020 ', 234 ),  
( '1/5/2021     ', 456 ),  
( '1/15/2021 ', 312 ),  
( '2/2/2021     ', 12 ),  
( '2/12/2021 ', 23 ),  
( '3/2/2021     ', 13 ),  
( '3/12/2021 ', 43 ),  
( '3/30/2021 ', 54 ),  
( '4/9/2021     ', 67 ),  
( '4/27/2021 ', 87 ),  
( '5/7/2021     ', 98 ),  
( '5/25/2021 ', 34 ),  
( '6/4/2021     ', 23 ),  
( '6/22/2021 ', 21 ),  
( '7/2/2021     ', 21 ),  
( '7/20/2021 ', 34 ),  
( '7/15/2021 ', 23 ),  
( '8/2/2021     ', 43 ),  
( '7/28/2021 ', 23 ),  
( '8/15/2021 ', 5 ),  
( '8/10/2021 ', 4 ),  
( '8/28/2021 ', 6 ),  
( '8/23/2021 ', 7 ),  
( '9/10/2021 ', 8 ),  
( '9/5/2021     ', 9 ),  
( '9/23/2021 ', 6 ),  
( '9/18/2021 ', 4 ),  
( '10/6/2021 ', 3 ),  
( '10/1/2021 ', 2 ),  
( '10/19/2021 ', 4 ),  
( '10/14/2021 ', 4 ),  
( '11/1/2021 ', 324 ),  
( '10/27/2021 ', 43 ),  
( '11/14/2021 ', 23 ),  
( '11/9/2021 ', 4 ),  
( '11/27/2021 ', 234 ),  
( '11/22/2021 ', 23 ),  
( '12/10/2021 ', 4 ),  
( '12/5/2021 ', 234 ),  
( '12/23/2021 ', 23   )  
  
select * from @table  
  
---  
  
select concat(SUBSTRING('JanFebMarAprMayJunJulAugSepOctNovDec', MONTH([Date]) * 3 - 2, 3), '-', YEAR([Date]) % 100) as [Date],   
    SUM([expiring orders]) as [Total expiring orders]  
from @table  
group by YEAR([Date]), MONTH([Date])  
order by YEAR([Date]), MONTH([Date])  

or:

select FORMAT(MIN([Date]), 'MMM''-''yy') as [Date],   
    SUM([expiring orders]) as [Total expiring orders]  
from @table  
group by YEAR([Date]), MONTH([Date])  
order by YEAR([Date]), MONTH([Date])  


  

Answer 2

Hi @Fareed Shaik ,

Please also check another method：

     declare @table as table ([Date] date, [expiring orders] int)  
          
     insert @table values  
     ( '11/1/2020 ', 234 ),  
     ( '11/21/2020 ', 765 ),  
     ( '11/30/2020 ', 127 ),  
     ( '12/1/2020 ', 145 ),  
     ( '12/18/2020 ', 234 ),  
     ( '1/5/2021     ', 456 ),  
     ( '1/15/2021 ', 312 ),  
     ( '2/2/2021     ', 12 ),  
     ( '2/12/2021 ', 23 ),  
     ( '3/2/2021     ', 13 ),  
     ( '3/12/2021 ', 43 ),  
     ( '3/30/2021 ', 54 ),  
     ( '4/9/2021     ', 67 ),  
     ( '4/27/2021 ', 87 ),  
     ( '5/7/2021     ', 98 ),  
     ( '5/25/2021 ', 34 ),  
     ( '6/4/2021     ', 23 ),  
     ( '6/22/2021 ', 21 ),  
     ( '7/2/2021     ', 21 ),  
     ( '7/20/2021 ', 34 ),  
     ( '7/15/2021 ', 23 ),  
     ( '8/2/2021     ', 43 ),  
     ( '7/28/2021 ', 23 ),  
     ( '8/15/2021 ', 5 ),  
     ( '8/10/2021 ', 4 ),  
     ( '8/28/2021 ', 6 ),  
     ( '8/23/2021 ', 7 ),  
     ( '9/10/2021 ', 8 ),  
     ( '9/5/2021     ', 9 ),  
     ( '9/23/2021 ', 6 ),  
     ( '9/18/2021 ', 4 ),  
     ( '10/6/2021 ', 3 ),  
     ( '10/1/2021 ', 2 ),  
     ( '10/19/2021 ', 4 ),  
     ( '10/14/2021 ', 4 ),  
     ( '11/1/2021 ', 324 ),  
     ( '10/27/2021 ', 43 ),  
     ( '11/14/2021 ', 23 ),  
     ( '11/9/2021 ', 4 ),  
     ( '11/27/2021 ', 234 ),  
     ( '11/22/2021 ', 23 ),  
     ( '12/10/2021 ', 4 ),  
     ( '12/5/2021 ', 234 ),  
     ( '12/23/2021 ', 23   )  
          
      
    ;with cte   
    as( select format([Date], 'MMM''-''yy') as [Date],[expiring orders]  
    from @table)  
    select [Date],sum([expiring orders]) [Total expiring orders]  
    from cte  
    group by [Date]  

For more details, please refer to:FORMAT (Transact-SQL)

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html