SQL Server How to offset 1 record in SQL?

hl0ma9xz 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(134)

I have this record in my SQL Server database, I can't find a way to offset a record or get this expected result:
| transDate | transTime | termId | transNumber | barcode | itemDescription | price | quantity | netTotal | transType |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 2023-10-03 | 03:26PM | 1 | 2496 | 4801010504207 | JBabyOilAloe&VitE50m | 52.25 | 1 | 52.25 | Standard Sale |
| 2023-10-03 | 03:26PM | 1 | 2496 | 4801010504207 | JBabyOilAloe&VitE50m | 52.25 | 1 | 52.25 | Standard Sale |
| 2023-10-03 | 03:26PM | 1 | 2496 | 4809011681439 | BoyBawanhiccorn100g | 25.25 | 1 | 25.25 | Standard Sale |

This is the current content of the table:

transDate	transTime	termId	transNumber	barcode	itemDescription	price	quantity	netTotal	transType
2023-10-03	03:26PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	1	52.25	Standard Sale
2023-10-03	03:26PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	1	52.25	Standard Sale
2023-10-03	03:26PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	1	52.25	Standard Sale
2023-10-03	03:26PM	1	2496	4809011681439	BoyBawanhiccorn100g	25.25	1	25.25	Standard Sale
2023-10-03	03:27PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	-1	-52.25	Item Voided

In my current table: The requirement is to get the detailed sales, where Item Voided is not included, and there's no identifier to identify which record was the link where a record has transType = Item Voided. It is a raw data inserted and the owner only wants to get the Standard Sale but there will be a discrepancy since 1 record should be offset by Item Voided. This can be fixed easily by SUM and GROUP BY but the problem is the owner wants to see the detailed lines of transaction.

I'm out of idea's on how to achieve what he wants. Can anybody please help me?

I've tried this, but it did not work or displayed the expected result that I wanted:

WITH SaleCounts AS (
    SELECT
        *,
        CASE
            WHEN transType= 'Item Voided' THEN 1
            ELSE ROW_NUMBER() OVER (PARTITION BY barcode, itemDescription, quantity ORDER BY (SELECT NULL))
        END AS RowNum
    FROM
        sales_details 
)

SELECT transDate, transTime, termId, transNumber, barcode, itemDescription, price, quantity, netTotal, transType
FROM
    SaleCounts
WHERE
    RowNum > 1
    OR transType<> 'Item Voided' and transNumber='2496'
ORDER by transDate, transTime, termId, transNumber;

Result:

transDate	transTime	termId	transNumber	barcode	itemDescription	price	quantity	netTotal	transType
2023-10-03	03:26PM	1	2496	4809011681439	BoyBawanhiccorn100g	25.25	1	25.25	Standard Sale
2023-10-03	03:26PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	1	52.25	Standard Sale
2023-10-03	03:26PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	1	52.25	Standard Sale
2023-10-03	03:26PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	1	52.25	Standard Sale

I've also tried to present this result but he did not want this:

SELECT 
    transDate, transTime, termId, transNumber, barcode, 
    itemDescription, price, 
    SUM(CAST(quantity AS float)) quantity, 
    SUM(CAST(netTotal AS float)) netTotal, transType
FROM 
    sales_details
WHERE 
    transNumber = '2496'
GROUP BY 
    transDate, transTime, termId, transNumber, barcode, 
    itemDescription, price, quantity, netTotal, transType;

Result:

transDate	transTime	termId	transNumber	barcode	itemDescription	price	quantity	netTotal	transType
2023-10-03	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	2	104.5
2023-10-03	1	2496	4809011681439	BoyBawanhiccorn100g	25.25	1	25.25

sql-server

来源：https://stackoverflow.com/questions/77226768/how-to-offset-1-record-in-sql

2条答案

按热度按时间

cigdeys31#

WITH
  A AS (
      SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY transDate, 
                                       transTime, 
                                       termId, 
                                       transNumber,  
                                       barcode, 
                                       itemDescription, 
                                       ABS(price),
                                       transType 
                          ORDER BY transDate) AS row_num
      FROM
          table1 ),

  B AS (
      SELECT
        *,
        COUNT(*) OVER(PARTITION BY transDate, 
                                   termId, 
                                   transNumber, 
                                   barcode, 
                                   itemDescription, 
                                   ABS(price),
                                   row_num
                      ORDER BY transDate) AS count1
      FROM
        A)

SELECT
    transDate,
    transTime,
    termId,
    transNumber,
    barcode,
    itemDescription,
    price,
    quantity,
    netTotal,
    transType
FROM
   B
WHERE
  count1 <2

Result:

transDate	transTime	termId	transNumber	barcode	itemDescription	price	quantity	netTotal	transType
2023-10-03	03:26PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	1	52.25	Standard Sale
2023-10-03	03:26PM	1	2496	4801010504207	JBabyOilAloe&VitE50m	52.25	1	52.25	Standard Sale
2023-10-03	03:26PM	1	2496	4809011681439	BoyBawanhiccorn100g	25.25	1	25.25	Standard Sale

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

kq4fsx7k2#

Perform a matching between active transaction ( quantity > 0 ) with void transaction ( quantity < 0 ) based on transNumber , barcode , price , quantity (negate of the void transaction). And ignore those with matches ( v.transNumber is null )

This is on the assumption that the void transaction should have the same barcode & price value but with quantity value negative.

The matching is also based on price , because there might be situation that same barcode & quantity but at different price

select a.*
from   (   
           select *,
                  rn = row_number() over (partition by transNumber, barcode, price, quantity
                                              order by transtime)
           from   sales_details
           where  quantity > 0 -- Active
       ) a
       left join
       (   
           select *,
                  rn = row_number() over (partition by transNumber, barcode, price, quantity
                                              order by transtime)
           from   sales_details
           where  quantity < 0  -- Void 
       ) v on  a.transNumber = v.transNumber
           and a.barcode     = v.barcode
           and a.price       = v.price
           and a.quantity    = -v.quantity
           and a.rn          = v.rn
where v.transNumber is null

db<>fiddle demo

Side Note: Don't cast the quantity or NetTotal to float ( CAST(quantity AS float) ). Floating point value are approximate value. You should be storing quantity in numeric data type like integer or decimal or numeric

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

我来回答

SQL Server How to offset 1 record in SQL?

2条答案

相关问题

热门标签

最新问答