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 |
1条答案
按热度按时间iqjalb3h1#
Just another approach using the window function
max() over()
and aCross Apply
to calculate the expressions onceOptional: wrap the left/right in a convert(int,...)