Блог

Главная - Вопросы по программированию - SQL выполняет вычитание

SQL выполняет вычитание

#tsql #sql-server-2000

#tsql #sql-server-2000

Вопрос:

Для получения хорошей квитанции была создана таблица бизнес-сценария is. Просто краткое описание. Итак, здесь у нас есть хорошая ожидаемая строка с PurchaseOrder (PO) в первых нескольких строках. И затем мы получаем каждую ожидаемую строку физически, и в это время это количество может отличаться из-за бизнес-кейса, такого как количество, которое может повредить и такое короткое количество. Таким образом, мы сохраняем статус для этого, например: OK, повреждение, также мы должны рассчитать недостающее количество на основе общего ожидаемого количества каждого элемента и общей суммы полученной строки.

     if object_id('DEV..Temp','U') is not null
    drop table Temp

    CREATE TABLE Temp 
    (        
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,        
    Item VARCHAR(32),
    PO VARCHAR(32) NULL,        
    ExpectedQty INT NULL,
    ReceivedQty INT NULL,
    [STATUS] VARCHAR(32) NULL,
    BoxName VARCHAR(32) NULL
    )

Пожалуйста, смотрите, что первые несколько строк с данными PO будут ожидаемыми строками,
и тогда оставшаяся строка будет полученной строкой

     INSERT INTO TEMP (Item,PO,ExpectedQty,ReceivedQty,[STATUS],BoxName)
    SELECT 'ITEM01','PO-01','30',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM01','PO-02','20',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM02','PO-01','40',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM03','PO-01','50',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM03','PO-02','30',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM03','PO-03','20',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM04','PO-01','30',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM01',NULL,NULL,'20','OK','box01' UNION ALL 
    SELECT 'ITEM01',NULL,NULL,'25','OK','box02' UNION ALL 
    SELECT 'ITEM01',NULL,NULL,'5','DAMAGE','box03' UNION ALL 
    SELECT 'ITEM02',NULL,NULL,'38','OK','box04' UNION ALL 
    SELECT 'ITEM02',NULL,NULL,'2','DAMAGE','box05' UNION ALL 
    SELECT 'ITEM03',NULL,NULL,'30','OK','box06' UNION ALL 
    SELECT 'ITEM03',NULL,NULL,'30','OK','box07' UNION ALL 
    SELECT 'ITEM03',NULL,NULL,'30','OK','box08' UNION ALL 
    SELECT 'ITEM03',NULL,NULL,'10','DAMAGE','box09' UNION ALL
    SELECT 'ITEM04',NULL,NULL,'25','OK','box10'

В таблице ниже приведен мой ожидаемый результат, основанный на приведенных выше данных.
Мне нужно показать эти данные следующим образом.
Поэтому я ценю, если вы можете дать мне соответствующий запрос для этого.
Примечание: первая строка пуста, и это фактически заголовок моей таблицы. 🙂 

     SELECT  '' as 'ITEM', '' as 'PO#', '' as 'ExpectedQty',
            '' as 'ReceivedQty','' as 'DamageQty' ,'' as 'ShortQty' UNION ALL 
    SELECT 'ITEM01','PO-01','30','30','0' ,'0'  UNION ALL 
    SELECT 'ITEM01','PO-02','20','15','5' ,'0'  UNION ALL 
    SELECT 'ITEM02','PO-01','40','38','2' ,'0'  UNION ALL 
    SELECT 'ITEM03','PO-01','50','50','0' ,'0'  UNION ALL 
    SELECT 'ITEM03','PO-02','30','30','0' ,'0'  UNION ALL 
    SELECT 'ITEM03','PO-03','20','10','10','0' UNION ALL 
    SELECT 'ITEM04','PO-01','30','25','0' ,'5'

Примечание: мы получили не больше, чем ожидалось.
решение должно быть основано на SQL 2000

Комментарии:

1. 1. Может ли быть более 1 строки с Status = 'DAMAGE' для элемента? 2. Может ли общее количество «OK» общее количество «DAMAGE» превышать общее ожидаемое количество (для одного и того же товара)?

2. 3. Возможно ли когда-либо для определенного элемента, что общее количество «ПОВРЕЖДЕНИЙ» превышает его ожидаемое количество в последней строке? (Например, может ли общее значение ‘DAMAGE’ быть равно 21 для ‘ITEM03’ в вашем примере? Я пытаюсь выяснить, как общее количество повреждений должно быть распределено между строками.)

Ответ №1:

Вам следует пересмотреть способ хранения этих данных. Разделите Expected и Received Damaged в разных таблицах (у вас много неиспользуемых ( null ) ячеек). Таким образом, любой запрос должен стать более читаемым.

Я думаю, что то, что вы пытаетесь сделать, может быть достигнуто более легко с помощью хранимой процедуры.

В любом случае, попробуйте этот запрос:

 SELECT Item, PO, ExpectedQty, 
       CASE WHEN [rec-consumed] > 0 THEN ExpectedQty 
            ELSE CASE WHEN [rec-consumed]   ExpectedQty > 0
                      THEN [rec-consumed]   ExpectedQty
                      ELSE 0
                 END
       END ReceivedQty,
       CASE WHEN [rec-consumed] < 0
                 THEN CASE WHEN DamageQty >= -1*[rec-consumed]
                           THEN -1*[rec-consumed]
                           ELSE DamageQty
                      END
            ELSE 0
       END DamageQty,
       CASE WHEN [rec_damage-consumed] < 0
            THEN DamageQty - [rec-consumed]
            ELSE 0
       END ShortQty
FROM (
    select t1.Item,
           t1.PO,
           t1.ExpectedQty,
           st.sum_ReceivedQty_OK 
                  - (sum(COALESCE(t2.ExpectedQty,0)) 
                      t1.ExpectedQty)
               [rec-consumed],
           st.sum_ReceivedQty_OK   st.sum_ReceivedQty_DAMAGE
                  - (sum(COALESCE(t2.ExpectedQty,0))
                      t1.ExpectedQty)
               [rec_damage-consumed],
           st.sum_ReceivedQty_DAMAGE DamageQty
    from #tt t1
    left join #tt t2 on t1.Item = t2.Item
                    and t1.PO > t2.PO
                    and t2.PO is not null
    join (select Item
           , sum(CASE WHEN status = 'OK' THEN ReceivedQty ELSE 0 END)
                 sum_ReceivedQty_OK
           , sum(CASE WHEN status != 'OK' THEN ReceivedQty ELSE 0 END)
                 sum_ReceivedQty_DAMAGE
        from #tt where PO is null
        group by Item) st on t1.Item = st.Item
    where t1.PO is not null
    group by t1.Item, t1.PO, t1.ExpectedQty,
             st.sum_ReceivedQty_OK,
             st.sum_ReceivedQty_DAMAGE
) a
order by Item, PO