Fachry Dzaky's questions

这是我的小提琴

我有一张这样的桌子

create table CTE1 (
    [CC Receiver] VARCHAR(10),
    Name VARCHAR(255),
    [Division] varchar(10),
    [Old Block] varchar(10),
    [Date] Date,
    Round VARCHAR(10),
    Platform VARCHAR(10),
    block_type VARCHAR(255),
    tph_type VARCHAR(255)
);

insert into CTE (
    [CC Receiver],
    Name,
    [Division],
    [Old Block],
    [Date],
    Round,
    Platform,
    block_type,
    tph_type
)
values
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '013', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '017', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '010', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '024', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '46', '013', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '46', '016', 'Bukit', 'Collection Road');

SELECT *
FROM CTE;

目的是根据相同的 [CC 接收器]、年份 (日期)、月份 (日期)、名称、部门、旧区块并按回合排序来给出 row_number。

根据要求，这是我的查询

SELECT *, 
    ROW_NUMBER() OVER (
        PARTITION BY YEAR(Date), MONTH(Date), [CC Receiver], Name, Division, [Old Block] 
        ORDER BY Round
    ) AS rotasi
FROM CTE;

结果如下：

这不符合我的要求，因为代码根据行号（包括平台）排序，而我想要的是排除平台。

预期结果：

这是我的小提琴

我有一张这样的桌子

月份以 yyyymm 为单位，01 02 代表日期。

我想让格式像这样

预期结果：

使用此查询

SELECT 
    [Employee Code], 
    [Period Month],
    -- Menyusun tanggal berdasarkan kolom tanggal (01, 02, ..., 31) sesuai dengan 'Period Month'
    CASE 
        WHEN [Date] = '01' AND TRY_CAST(CAST([Period Month] AS CHAR(4)) + '-01' AS DATE) IS NOT NULL THEN CAST(CAST([Period Month] AS CHAR(4)) + '-01' AS DATE)
        WHEN [Date] = '02' AND TRY_CAST(CAST([Period Month] AS CHAR(4)) + '-02' AS DATE) IS NOT NULL THEN CAST(CAST([Period Month] AS CHAR(4)) + '-02' AS DATE)
    END AS [Date],
    [Value]
FROM 
    (SELECT [Employee Code], [Period Month], 
            [01], [02]
     FROM CTE1) AS SourceTable
UNPIVOT
    ([Value] FOR [Date] IN 
        ([01], [02])) AS UnpivotedTable
ORDER BY [Employee Code], [Period Month], [Date];

但它返回的内容如下：

我有一列GL_DESCRIPTION，其值如下

SELECT DISTINCT GL_DESCRIPTION 
FROM database_actual_pemupukan;


PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - TRANSPORT
PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - UPAH
PEMUPUKAN - CPD HIK ROTASI 2  - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1  - TRANSPORT
PEMUPUKAN - CPD HIK ROTASI 3  - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1  - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - TRANSPORT

我在 INT 中有一个名为的新列Rotasi，它可以在“ROTASI”之后存储任意数字字符的值

因此对于该要求，我的代码将是这样的：

UPDATE database_actual_pemupukan 
SET Rotasi = SUBSTRING(GL_DESCRIPTION, 
                       PATINDEX('%[0-9]%', GL_DESCRIPTION), 
                       LEN(GL_DESCRIPTION));

返回错误：

消息 245，级别 16，状态 1，第 1 行将
varchar 值 '1 - MATERIAL' 转换为数据类型 int 时转换失败

我也尝试过这个查询

UPDATE database_actual_pemupukan
SET rotasi = CASE 
                 WHEN PATINDEX('%[0-9]%', GL_DESCRIPTION) > 0 
                     THEN CAST(
                               -- Extract the number starting from the first digit
                               SUBSTRING(GL_DESCRIPTION, 
                                         PATINDEX('%[0-9]%', GL_DESCRIPTION), 
                                         CASE 
                                             WHEN PATINDEX('%[^0-9]%', GL_DESCRIPTION + 'a') = 0 
                                                 THEN LEN(GL_DESCRIPTION) - PATINDEX('%[0-9]%', GL_DESCRIPTION) + 1
                                                 ELSE PATINDEX('%[^0-9]%', GL_DESCRIPTION + 'a') - PATINDEX('%[0-9]%', GL_DESCRIPTION)
                                         END) AS INT)
                 ELSE NULL -- If no number is found, set rotasi to NULL
             END;

此查询返回此错误：

消息 537，级别 16，状态 2，第 1 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

我错在哪儿了？