Am nevoie de ajutor la conceperea unei interogari qsl prin care sa pot determina stocul de marfa la o anumita data. Tabelele care intra in aceasta problema au urmatoarele caracteristici:

CREATE TABLE [dbo].[INTRARI](
[ID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[NRDOC] [decimal](18, 0) NULL,
[DATA] [smalldatetime] NULL,
[TIP] [nvarchar](10) COLLATE Romanian_CI_AS NULL,
[ID_FURNIZOR] [decimal](18, 0) NULL,
[VALOARE] [numeric](18, 2) NULL,
[TVA] [numeric](18, 2) NULL,
[ID_USER] [decimal](18, 0) NULL
) ON [PRIMARY]

Voi lua un exemplu bazat be tabelele din AdventureWorks, deoarece mi-e greu să înţeleg în tabelele tale care e tabela cu produse (şi ce legătură au partenerii în treaba asta). Să zicem că vrem să calculăm stocul la 31 dec 2002, iar pentru teste ne vom concentra pe produsul cu ID-ul 948 ("Front Brakes"). Datele relevante pot fi furnizate de următoarele query-uri:

DECLARE @InventoryDate datetime
DECLARE @ProductID int
SET @ProductID=948
SET @InventoryDate='20021231'

SELECT p.ProductID, p.Name, poh.OrderDate as PurchaseOrderDate, pod.OrderQty as PurchaseQty
FROM Production.Product p
INNER JOIN Purchasing.PurchaseOrderDetail pod ON pod.ProductID=p.ProductID
INNER JOIN Purchasing.PurchaseOrderHeader poh ON poh.PurchaseOrderID=pod.PurchaseOrderID
WHERE poh.OrderDate<=@InventoryDate
AND (p.ProductID=@ProductID OR @ProductID IS NULL)
ORDER BY p.ProductID, poh.OrderDate

ProductID   Name                PurchaseOrderDate       PurchaseQty
----------- ------------------- ----------------------- -----------
948         Front Brakes        2002-02-25 00:00:00.000 550
948         Front Brakes        2002-05-13 00:00:00.000 550
948         Front Brakes        2002-09-25 00:00:00.000 550
(3 row(s) affected)

SELECT p.ProductID, p.Name, soh.OrderDate as SalesOrderDate, sod.OrderQty as SalesQty
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod ON sod.ProductID=p.ProductID
INNER JOIN Sales.SalesOrderHeader soh ON soh.SalesOrderID=sod.SalesOrderDetailID
WHERE soh.OrderDate<=@InventoryDate
AND (p.ProductID=@ProductID OR @ProductID IS NULL)
ORDER BY p.ProductID, soh.OrderDate

ProductID   Name                SalesOrderDate          SalesQty
----------- ------------------- ----------------------- --------
948         Front Brakes        2002-07-11 00:00:00.000 8
948         Front Brakes        2002-08-01 00:00:00.000 4
948         Front Brakes        2002-08-01 00:00:00.000 5
948         Front Brakes        2002-08-04 00:00:00.000 1
948         Front Brakes        2002-08-13 00:00:00.000 10
948         Front Brakes        2002-08-21 00:00:00.000 2
948         Front Brakes        2002-09-01 00:00:00.000 2
948         Front Brakes        2002-09-08 00:00:00.000 2
948         Front Brakes        2002-09-12 00:00:00.000 6
948         Front Brakes        2002-09-13 00:00:00.000 2
948         Front Brakes        2002-10-03 00:00:00.000 4
948         Front Brakes        2002-10-13 00:00:00.000 1
948         Front Brakes        2002-10-15 00:00:00.000 8
948         Front Brakes        2002-12-01 00:00:00.000 2
948         Front Brakes        2002-12-05 00:00:00.000 2
948         Front Brakes        2002-12-14 00:00:00.000 3
948         Front Brakes        2002-12-29 00:00:00.000 6
(17 row(s) affected)

Dacă facem join pur şi simplu, astfel:

SELECT p.ProductID, p.Name,
      poh.OrderDate as PurchaseOrderDate, pod.OrderQty as PurchaseQty,
      soh.OrderDate as SalesOrderDate, sod.OrderQty as SalesQty
FROM Production.Product p
INNER JOIN Purchasing.PurchaseOrderDetail pod ON pod.ProductID=p.ProductID
INNER JOIN Purchasing.PurchaseOrderHeader poh ON poh.PurchaseOrderID=pod.PurchaseOrderID
INNER JOIN Sales.SalesOrderDetail sod ON sod.ProductID=p.ProductID
INNER JOIN Sales.SalesOrderHeader soh ON soh.SalesOrderID=sod.SalesOrderDetailID
WHERE soh.OrderDate<=@InventoryDate AND poh.OrderDate<=@InventoryDate
AND (p.ProductID=@ProductID OR @ProductID IS NULL)
ORDER BY p.ProductID, poh.OrderDate, soh.OrderDate

atunci obţinem: 

ProductID   Name           PurchaseOrderDate PurchaseQty SalesOrderDate SalesQty
----------- -------------------------------- ----------- -------------- --------
948         Front Brakes   2002-02-25        550         2002-07-11     8
948         Front Brakes   2002-02-25        550         2002-08-01     4
948         Front Brakes   2002-02-25        550         2002-08-01     5
948         Front Brakes   2002-02-25        550         2002-08-04     1
948         Front Brakes   2002-02-25        550         2002-08-13     10
948         Front Brakes   2002-02-25        550         2002-08-21     2
948         Front Brakes   2002-02-25        550         2002-09-01     2
948         Front Brakes   2002-02-25        550         2002-09-08     2
948         Front Brakes   2002-02-25        550         2002-09-12     6
948         Front Brakes   2002-02-25        550         2002-09-13     2
948         Front Brakes   2002-02-25        550         2002-10-03     4
948         Front Brakes   2002-02-25        550         2002-10-13     1
948         Front Brakes   2002-02-25        550         2002-10-15     8
948         Front Brakes   2002-02-25        550         2002-12-01     2
948         Front Brakes   2002-02-25        550         2002-12-05     2
948         Front Brakes   2002-02-25        550         2002-12-14     3
948         Front Brakes   2002-02-25        550         2002-12-29     6
948         Front Brakes   2002-05-13        550         2002-07-11     8
948         Front Brakes   2002-05-13        550         2002-08-01     4
948         Front Brakes   2002-05-13        550         2002-08-01     5
948         Front Brakes   2002-05-13        550         2002-08-04     1
948         Front Brakes   2002-05-13        550         2002-08-13     10
948         Front Brakes   2002-05-13        550         2002-08-21     2
948         Front Brakes   2002-05-13        550         2002-09-01     2
948         Front Brakes   2002-05-13        550         2002-09-08     2
948         Front Brakes   2002-05-13        550         2002-09-12     6
948         Front Brakes   2002-05-13        550         2002-09-13     2
948         Front Brakes   2002-05-13        550         2002-10-03     4
948         Front Brakes   2002-05-13        550         2002-10-13     1
948         Front Brakes   2002-05-13        550         2002-10-15     8
948         Front Brakes   2002-05-13        550         2002-12-01     2
948         Front Brakes   2002-05-13        550         2002-12-05     2
948         Front Brakes   2002-05-13        550         2002-12-14     3
948         Front Brakes   2002-05-13        550         2002-12-29     6
948         Front Brakes   2002-09-25        550         2002-07-11     8
948         Front Brakes   2002-09-25        550         2002-08-01     4
948         Front Brakes   2002-09-25        550         2002-08-01     5
948         Front Brakes   2002-09-25        550         2002-08-04     1
948         Front Brakes   2002-09-25        550         2002-08-13     10
948         Front Brakes   2002-09-25        550         2002-08-21     2
948         Front Brakes   2002-09-25        550         2002-09-01     2
948         Front Brakes   2002-09-25        550         2002-09-08     2
948         Front Brakes   2002-09-25        550         2002-09-12     6
948         Front Brakes   2002-09-25        550         2002-09-13     2
948         Front Brakes   2002-09-25        550         2002-10-03     4
948         Front Brakes   2002-09-25        550         2002-10-13     1
948         Front Brakes   2002-09-25        550         2002-10-15     8
948         Front Brakes   2002-09-25        550         2002-12-01     2
948         Front Brakes   2002-09-25        550         2002-12-05     2
948         Front Brakes   2002-09-25        550         2002-12-14     3
948         Front Brakes   2002-09-25        550         2002-12-29     6
(51 row(s) affected)

şi se vede clar că datele au fost duplicate.

Dacă facem SUM pe aceste date, vom obţine rezultate incorecte:

ProductID   Name                     PurchaseQty SalesQty
----------- ------------------------ ----------- -----------
948         Front Brakes             28050       204
 

O soluţie ar fi să folosim subquery-uri:

SELECT p.ProductID, p.Name,
      COALESCE((
            SELECT SUM(pod.OrderQty)
            FROM Purchasing.PurchaseOrderDetail pod
            INNER JOIN Purchasing.PurchaseOrderHeader poh
            ON poh.PurchaseOrderID=pod.PurchaseOrderID
            WHERE pod.ProductID=p.ProductID AND poh.OrderDate<=@InventoryDate
      ),0) as PurchaseQty, COALESCE((
            SELECT SUM(sod.OrderQty)
            FROM Sales.SalesOrderDetail sod
            INNER JOIN Sales.SalesOrderHeader soh
            ON soh.SalesOrderID=sod.SalesOrderDetailID
            WHERE sod.ProductID=p.ProductID AND soh.OrderDate<=@InventoryDate
      ),0) as SalesQty
FROM Production.Product p
WHERE (p.ProductID=@ProductID OR @ProductID IS NULL)
ORDER BY p.ProductID
 

iar altă soluţie ar fi să facem GROUP BY-uri pe rând:
 

SELECT p.ProductID, p.Name,
      COALESCE(po.PurchaseQty,0) AS PurchaseQty,
      COALESCE(so.SalesQty,0) AS SalesQty
FROM Production.Product p
LEFT JOIN (
      SELECT pod.ProductID, SUM(pod.OrderQty) as PurchaseQty
      FROM Purchasing.PurchaseOrderDetail pod
      INNER JOIN Purchasing.PurchaseOrderHeader poh
      ON poh.PurchaseOrderID=pod.PurchaseOrderID
      WHERE poh.OrderDate<=@InventoryDate
      GROUP BY pod.ProductID
) po ON po.ProductID=p.ProductID
LEFT JOIN (
      SELECT sod.ProductID, SUM(sod.OrderQty) as SalesQty
      FROM Sales.SalesOrderDetail sod
      INNER JOIN Sales.SalesOrderHeader soh
      ON soh.SalesOrderID=sod.SalesOrderDetailID
      WHERE soh.OrderDate<=@InventoryDate
      GROUP BY sod.ProductID
) so ON so.ProductID=p.ProductID
WHERE (p.ProductID=@ProductID OR @ProductID IS NULL)
ORDER BY p.ProductID

În ambele cazuri, obţinem rezultatul dorit, anume:

ProductID   Name                     PurchaseQty SalesQty
----------- ------------------------ ----------- -----------
948         Front Brakes             1650        68

Răzvan 

ThePuiu: prima intrebare de baraj este unde e nomenclatorul de produse ?

Mai precis, ai o tabela in baza de date care sa memoreze nomenclatorul de produse ? 

Cand spui pret, la care pret te referi: la cel de intrare sau la cel de iesire ? 

Normalizarea e un concept total necunoscut.

O solutie ar putea fi:

      SELECT QueryI.PRODUS,

            QueryI.PRET_IN,

            CantI,

            ISNULL(CantE,0) AS CantE,

            CantI - ISNULL(CantE,0) AS Stoc

      FROM

      (

            SELECT I.PRODUS, I.PRET_IN, SUM(I.CANTITATE) AS CantI

            FROM INTRARI_DETAIL AS I

            GROUP BY I.PRODUS, I.PRET_IN

      ) AS QueryI

      LEFT OUTER JOIN

      (

            SELECT I.PRODUS, I.PRET_IN, SUM(E.CANTITATE) AS CantE

            FROM INTRARI_DETAIL AS I

                  INNER JOIN IESIRI_DETAIL AS E ON I.ID_INTRARI = E.ID_INTRARI

            GROUP BY I.PRODUS, I.PRET_IN

      ) AS QueryE

      ON QueryI.PRODUS = QueryE.PRODUS AND QueryI.PRET_IN = QueryE.PRET_IN

 sau

            SELECT I.PRODUS, I.PRET_IN,

SUM(E.CANTITATE) AS CantE,

SUM( ISNULL(I.CANTITATE,0) ) AS CantI,

SUM(E.CANTITATE) - SUM( ISNULL(I.CANTITATE,0) ) AS Stoc

            FROM INTRARI_DETAIL AS I

                 LEFT OUTER JOIN IESIRI_DETAIL AS E ON I.ID_INTRARI = E.ID_INTRARI

            GROUP BY I.PRODUS, I.PRET_IN

Poti sa  modifici interogarea pentru a calcula stocul la o anumita data.

De asemenea, gruparea ar putea sa fie facuta si in functie de codul produsului (banuiesc ca e vb. de codul produsului) + pret.

Nu exista o tabela separata pt produse (nu imi mai aduc aminte daca a fost facut asa dintr-un anumit considerent sau din greseala (omu' cit traieste invata!) - nici intr-un caz din cauza ca, conceptul de normalizare imi era total necunoscut!). Am reusit intre timp sa gasesc o rezolvare (era f simplu defapt, dar nu vedeam eu solutia!). Solutia ta Razvane nu ma ajuta pt ca - cred ca nu m-am exprimat eu bine - vreau sa obtin o lista cu toate produsele din stoc si cantitatea lor intr-o anumita zi. Solutia data de B_gd_n[ ]Sahlean nu returna un rezultat corect - cantitatea iesita este tot timpul 0(neavind tabelele cu ceva concret in ele nu e de mirare) desi era sa zicem ceea ce vroiam eu. 

Multumesc inca o data pt timpul si rabdarea aratata!
Puiu

ThePuiu:

Nu exista o tabela separata pt produse (nu imi mai aduc aminte daca a fost facut asa dintr-un anumit considerent sau din greseala (omu' cit traieste invata!) - nici intr-un caz din cauza ca, conceptul de normalizare imi era total necunoscut!). Am reusit intre timp sa gasesc o rezolvare (era f simplu defapt, dar nu vedeam eu solutia!). Solutia ta Razvane nu ma ajuta pt ca - cred ca nu m-am exprimat eu bine - vreau sa obtin o lista cu toate produsele din stoc si cantitatea lor intr-o anumita zi. Solutia data de B_gd_n[ ]Sahlean nu returna un rezultat corect - cantitatea iesita este tot timpul 0(neavind tabelele cu ceva concret in ele nu e de mirare) desi era sa zicem ceea ce vroiam eu. 

Multumesc inca o data pt timpul si rabdarea aratata!
Puiu

Puţină normalizare nu ar fi rău.

Acuma am obs. existenţa cîmpului Stoc în INTRARI_DETAIL. Vroiai să calculezi stocul pentru fiecare produs intrat ?