platformaerp.com

Magazyn - SQL podstawowe zapytania

Ważne skrypty

• [document].[documentOUT_conditionalyDelete] - Usuwanie zatwierdzonego dokumentu rozchodowego - jest zabezpieczony na to, żeby nie usunąć korygowaneg

Pomocne skrypty SQL

Podstawowe złaczenie - dokumenty magazynowe

select
    d.documentdate,
    d.documentFullNR,
  w.locationCode warehouseCode,
    l.locationCode,
    c.commisionFullNR,
    dl.itemQuantity,
    dl.itemQuantityLeft,
    dl.itemPrice,
  dl.sysItemPrice,
    dl.itemValue,
    dl.itemValueLeft,
    dl.sysItemValue,
    dl.sysItemValueLeft,
    d.warehouseID,
    d.documentID, -- w systemie  http://lisicki.platformacrm.pl/sales-met/documentIn/86bb2f0f-50f9-e911-8424-7ed30a52f64f
    dl.documentLineID, -- id pozycji
    dl.initialsourcedocumentLineID, -- zrodlowy documentLineID (czyli z ktorej partii pochodzi wydanie)
    i.itemName,
  dt.documentCategory --warehouse, warehouseC, warehouseH
from
    document.documentLine dl
    inner join document.document d on d.documentID =dl.documentID
  inner join wms.location w on d.warehouseID = w.locationID   --magazyn
  left join wms.location l on l.locationID  = dl.locationid   --lokacja
    inner join wms.item i on dl.itemID =i.itemID
  inner join document.documenttype dt on dt.documentType = d.documentType
    left join wms.commisionLine cl on dl.commisionLineID = cl.commisionLineID
    left join wms.commision c on c.commisionID =cl.commisionID
where
    dl.itemID  ='29994D4D-509A-EA11-80E1-9C8E994DC647'
  --and dt.baseDocumentType = 'wmm'     --typ zrodlowy pz,pzi
    and dl.status >0  -- tylko zatwierdzone pozycje
    and d.status >0   -- tylko zatwierdzone dokumenty
  and d.documentDate >= '2023-11-01'
    and d.documentDate <= '2023-11-30'
ORDER BY 
  documentDate ,   dl.signvalue * -1  ,d.documentNR 

Sortowanie

Opcja nr 1: ORDER BY documentDate , dl.signvalue * -1 ,d.documentNR Wady - nie posortuje dobrze dokumentow WM- i WM+ w tym samym dniu

Opcja nr 2: order by d.documentDate ,d.fulltextid (chyba najlepsza opcja na razie ??)

Ostatnia data i numer dokumentu wydania

• update: 20240605

select * from (
    select
        row_number() OVER(PARTITION BY i.itemid ORDER BY d.documentdate DESC) row,
        dl.itemid,
      d.documentdate,
        d.documentFullNR
    from
        document.document d
        inner join document.documenttype dt on dt.documentType = d.documentType
      inner join document.documentline dl on d.documentid=dl.documentid
        inner join wms.item i on dl.itemid = i.itemid
    where
        dl.status >=0
      and d.status>=0
        and dt.basedocumenttype in ('rw','wz','RWCO','rwp') --wydania bez korekt,wm,mm itp
      --and d.warehouseid =@warehouseID
) x where row=1

Pierwsza cena

--pierwsza cena
select itemid,sysItemPrice from (
select
    row_number() OVER(PARTITION BY i.itemid ORDER BY d.documentdate DESC) row,
            i.itemID,
            dl.sysItemPrice
        from document.document d
            inner join document.documenttype dt on dt.documentType = d.documentType
            inner join document.documentline dl on d.documentid=dl.documentid
            inner join wms.item i on dl.itemid = i.itemid
        where
            dl.status >=0
            and d.status>=0
            and dt.baseDocumentType = 'pz' --ex. tylko pz, pzi
            and d.documentDate >=@date
) x where row=1) pierwszacena  on datasummary.itemID = pierwszacena.itemid

Identify lots

SELECT
  dl.itemID,
  i.itemCode,
  i.itemName,
  w.locationCode warehouseCode,
  dt.baseDocumentType,
  d.documentDate,
  d.documentFullNR,
  dl.documentLineID,
  dl.[sign],
  dl.itemQuantity,
  dl.itemQuantityLeft,
  dl.itemPrice,
  dl.sysItemPrice,
  dl.sysItemValue,
  dl.sysItemValueLeft,
  dl.initialIncomeSourceLineID,
  dl.outcomeShiftLineID,
  dl.fullTextID
FROM document.documentLine dl
  INNER JOIN document.document d ON d.documentID = dl.documentID
  INNER JOIN wms.location w ON d.warehouseID = w.locationID  
  INNER JOIN wms.item i ON i.itemID = dl.itemID
  INNER JOIN document.documentType dt ON dt.documentType = d.documentType
WHERE dl.[status] > 0
  AND d.[status] > 0
  AND dl.itemID ='44e75b34-197d-e411-940f-d89d6719e0c9'
  AND dt.documentCategory in ('warehouse','warehousec','warehouseh') -- ZW czasem jest w fakturach
ORDER BY 
  dl.itemID, d.documentDate, dl.[sign] DESC, dl.fullTextID;

Export datasetu na potrzeby analizy danych data-integrity-sqlite

/*Cel tego datasetu sa podstawowe dane do analizy dla indeksu bez noise*/
declare @itemid uniqueidentifier 

select @itemid = itemid from wms.item where itemcode= 'CE00000028'
print @itemid

--zqname: wms.item
select * from wms.item where itemid = @itemid 

--zqname: document.documentline
select 
  -- excluded [documentNR],[documentMonth],[addUsername], [modDate], [modUsername],[fullTextCat],
  [documentLineID], [documentID], [sourceDocumentLineID], [orderLineID],  [documentFullNR],  [itemID], [itemQuantity], [itemQuantityLeft], [itemPrice], [itemValue], [itemValueLeft], [itemSerialNumber], [locationID], [sign], [addDate],  [fullTextKeyword],  [fullTextID], [status], [documentOrdinalNumber], [itemConvertUnit], [itemConvertQuantity], [itemConvertPrice], [commisionLineID], [sysItemPrice], [sysItemValue], [sysItemValueLeft], [invoiceLineId], [outcomeShiftLineId], [locationIdB], [itemIDB], [isCorrectiveLine], [correctedItemQuantity], [correctedItemPrice], [initialSourceDocumentLineId], [inventorySheetLineId], [additionalValue], [CNCCode], [correctedItemValue], [additionalPrice], [sysAdditionalPrice], [sysCorrectedItemPrice], [relPriceOutShLineID], [agrItemQuantity], [agrItemPrice], [agrItemValue], [commisionID], [manualCommisionLineID], [initialIncomeSourceLineID], [signValue], [matchedRequisitionID], [matchedRequisitionQuantity], [matchedQuantityDifference], [hasInitialIncomeQuantityLeft], [pwDocumentLineID], [structureLineID], [testDocumentLineID], [commitTimeMs], [sysCorrectedItemValueQcorrExcluded], [importRelationID], [archive]
from document.documentline where itemid = @itemid and status>0 order by adddate

--zqname: document.document
select 
  -- excluded [barcodeCollectorCommisionLineIdList], [manualAccounting], [commitException], [commitTimeMs], [productStockVirtualLocationID], [commitTaskID], [commitIssuedDate], [importID], [documentMonth],[addUsername], [modDate], [modUsername], [fullTextKeyword], [fullTextCat], [financialSystemExport], [accDate], [accUsername], 
  [documentID], [documentDate], [warehouseID], [warehouseIDB], [documentType], [documentNR], [documentFullNR], [documentCode], [firmID],  [invoiceNR], [orderID], [commisionID], [addDate], [status], [documentOutNR], [fullTextID], [commisionLineID], [organisationUnitID], [documentMemo], [currency], [currencyExchangeRate], [currencyExchangeDate], [outcomeShiftId], [correctedDocumentId], [initialCorrectedDocumentId], [outcomeItemId], [CNCCode], [trashItemId], [trashItemQuantity], [outcomeTrashLocationId], [incomeTrashLocationId], [lastCorrectionDocumentID], [sumItemQuantity], [sumItemValue], [sumSysItemValue], [barcodeCollectorCommisionIDlist], [currencyExchangeTableNumber], [trashItemPrice]
from document.document where documentid in (select documentid from document.documentline where itemid = @itemid and status>0) order by adddate


--zqname: wms.stockBalance
select * from 
  wms.stockBalance where itemid =@itemid

TEST czy indeks moze byc usuniety

SET NOCOUNT ON;

DECLARE @id uniqueidentifier = '49DB1A0A-E5C9-EB11-956C-D89D6719E0CB';
DECLARE @sql nvarchar(max), @tbl nvarchar(300), @col sysname;

CREATE TABLE #hits (tbl nvarchar(300), col sysname, cnt int);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name), c.name
    FROM sys.columns c
    JOIN sys.tables t  ON t.object_id = c.object_id
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE c.system_type_id = TYPE_ID('uniqueidentifier')
      AND c.name like '%itemID%'
      AND t.is_ms_shipped = 0
AND s.name !='archive';

OPEN c;
FETCH NEXT FROM c INTO @tbl, @col;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'INSERT INTO #hits SELECT @tbl, @col, COUNT(*) FROM ' + @tbl
             + N' WITH (NOLOCK) WHERE ' + QUOTENAME(@col) + N' = @id HAVING COUNT(*) > 0';
    EXEC sp_executesql @sql, N'@id uniqueidentifier, @tbl nvarchar(300), @col sysname', @id, @tbl, @col;
    FETCH NEXT FROM c INTO @tbl, @col;
END
CLOSE c; DEALLOCATE c;

SELECT tbl, col, cnt,
       'SELECT * FROM ' + tbl + ' WHERE ' + QUOTENAME(col) + ' = ''' + CAST(@id AS varchar(36)) + '''' AS [select]
FROM #hits ORDER BY tbl, col;
DROP TABLE #hits;