SQL Server Create 3 new columns based off of transaction period (year, month, max year)

zhte4eai 于 2023-04-10 发布在其他

关注(0)|答案(1)|浏览(138)

Column [TRAN_YRNO] contains month/year data in the format YYYYMM (ex. 202304 ). I am trying to split this into two columns, one for month and one for year. I then want to create a third column that contains the max year. (should be 2023 for all rows)

SELECT 
       [TNET]
      ,[SORTCODE]    
      ,[LOADDATE]
      ,[TRAN_YRMO]
  FROM [dbo].[tbl_941_Monthly_Cumulative]

SELECT c.*, c.[year]
from  c
LEFT JOIN b
ON c.[year] = b.[Max Year]

(SELECT
*,
LEFT([TRAN_YRMO],4) as 'Year' ,
RIGHT([TRAN_YRMO],2) as 'Month'
FROM [dbo].[tbl_941_Monthly_Cumulative])c

(SELECT
MAX(a.[year]) AS 'Max Year'

from 

(SELECT *,
LEFT([TRAN_YRMO],4) as 'Year' ,
RIGHT([TRAN_YRMO],2) as 'Month'
FROM [dbo].[tbl_941_Monthly_Cumulative])a)b

Here is what I have tried so far, which obviously errors out.

The goal is to have something that looks like:
| TNET | SORTCODE | LOADDATE | TRAN_YRMO | YEAR | MONTH | MAX YEAR |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| | | | 202304 | 2023 | 04 | 2023 |
| | | | 202301 | 2023 | 01 | 2023 |
| | | | 202208 | 2022 | 08 | 2023 |
| | | | 202107 | 2021 | 07 | 2023 |

sql-server

来源：https://stackoverflow.com/questions/75952023/create-3-new-columns-based-off-of-transaction-period-year-month-max-year

1条答案

按热度按时间

iqjalb3h1#

Just another approach using the window function max() over() and a Cross Apply to calculate the expressions once

Optional: wrap the left/right in a convert(int,...)

Select A.*
      ,B.Year
      ,B.Month
      ,MaxYear = max(B.Year) over()
 From  YourTable A
 Cross Apply ( values ( left([TRAN_YRMO],4),right([TRAN_YRMO],2) ) )B(Year,Month)

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server Create 3 new columns based off of transaction period (year, month, max year)

1条答案

相关问题

热门标签

最新问答