How to display negative value on comparing and matching the table rows in SQL Server-CodePudding

I am loading an Excel file into SQL Server and storing the data as per requirement as per the query shared by converting multiple rows in to single based on criteria.

I have the SQL query shown here, which is working as expected but instead of positive results, I would like to check if the prev/first row as per query has the - symbol and append it to the quantity.

Here is the sample query I am trying out and expected output explained in image

DECLARE @tmpTable TABLE
(
    RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    InvoiceDate NVARCHAR(20),
    Description NVARCHAR(MAX),
    Quantity NVARCHAR(20)
)
INSERT INTO @tmpTable
SELECT * FROM (VALUES
('07/18/2021','MedName','-18'),
(NULL,'18 Patient1',''),
('07/18/2021','MedName','-5'),
(NULL,'2 Patient2',''),
(NULL,'3 Patient3',''),
('07/18/2021','MedName','7'),
(NULL,'1 Patient2',''),
(NULL,'6 Patient3','')
) AS temp
(InvoiceDate,
    Description,
    Quantity)

    SELECT * FROM @tmpTable

DECLARE @tblTmp TABLE
    (
        RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
        InvoiceDate NVARCHAR(20),
        Description NVARCHAR(MAX),
        Quantity NVARCHAR(20)
    )
    DECLARE @tblTmp1 TABLE
    (
        [RowID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
        [InvoiceDate] DATETIME,
        [MedDescription] [NVARCHAR](MAX),
        [Quantity] [DECIMAL](18,2)
    )
    BEGIN TRY
    

    --SELECT * FROM @invoiceTable

    INSERT INTO @tblTmp
    SELECT InvoiceDate,   Description, Quantity FROM @tmpTable
    ; WITH
        CTE (InvoiceDate , Description,  Quantity) AS (
    SELECT t1.InvoiceDate,  t1.Description, t2.Quantity
    FROM (
       SELECT *
       , ISNULL((SELECT MIN(RowID) FROM @tblTmp st WHERE InvoiceDate is NOT NULL and st.RowId > t.RowId) -1, (SELECT MAX(RowID) FROM @tblTmp)) NewVal
       FROM @tblTmp t
       WHERE InvoiceDate is NOT NULL
       ) t1
       JOIN (
       SELECT RowID, LTRIM(RIGHT(Description, LEN(Description) - PATINDEX('%[0-9][^0-9]%', Description ))) CustomerPo,
       CASE WHEN ISNUMERIC(LEFT(Description, CHARINDEX(' ', Description) -1)) = 1 THEN
      LEFT( Description, CHARINDEX (' ', Description) -1) ELSE 0 END Quantity
       FROM @tblTmp
       WHERE InvoiceDate is NULL
       --AND Description <> 'NON RETURNABLE'
       ) t2 ON t2.RowId BETWEEN t1.RowID and t1.NewVal
       )


    --SELECT * FROM CTE;
    INSERT INTO @tblTmp1 (InvoiceDate , MedDescription,  Quantity)
    SELECT CONVERT(DATETIME,InvoiceDate, 101)  , Description,
    Quantity
    
    FROM CTE;

    SELECT * FROM @tblTmp1
    
 END TRY
 BEGIN CATCH
 SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_STATE() AS ErrorState,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

CodePudding user response：

Your query is returning the wrong value. You wanted sign(t1.Quantity) * t2.Quantity rather than just t2.Quantity.

If you'd like an alternative that eliminates the subqueries you might consider approaching it with analytic functions. These would require a somewhat newer version of SQL Server in case that's a consideration.

with data as (
    -- use gaps and islands to gather rows into groups
    select *, count(InvoiceDate) over (order by RowId) as Grp
    from @tmpTable
), lookBack as (
    select
        -- grab the corresponding invoice date, medname, sign and row id
        RowId, min(RowId) over (partition by grp) as MinRowId,
        min(InvoiceDate) over (partition by grp) as InvoiceDate,
        first_value(Description) over (partition by grp order by RowId) as MedName,
        Description,
        sign(try_parse(min(Quantity) over (partition by grp) as int)) as Sgn
    from data
)
select InvoiceDate, MedName, Quantity
from lookBack cross apply (
    select Sgn * try_parse(value as int) as Quantity
    from string_split(Description, ' ') s
) v
where RowId <> MinRowId and Quantity is not null -- assumes one numeric per description
order by RowId;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8639811541064781083329757459054f