-1

I have three tables in my ERP and I need a query, by SQL Server, for join all rows. Here are the three tables I have with its properties, filtering by product code (SL.CodProduct = 'AL4301') to simplify:

TABLE PRODUCTS:

Table Products

  • CodProduct
  • DescProduct
  • Family

TABLE STOCKS:

Table Stocks

  • CodProduct
  • Color
  • Number
  • UnitsStock

TABLE SELLS:

Table Sells

  • CodProduct
  • DateSold
  • Color
  • Number
  • UnitsSold

I need to get a list of all item combinations (color - number), showing sales per month and total stock currently. The problem is that for some combinations of items there have been no sales.

I have tried it with this query:

SELECT DatePart(mm, SELLS.DateSold) As Month,
       SELLS.CodProduct, PRODUCTS.DescProduct,
       SELLS.Color, SELLS.Number,
       SUM(SELLS.UnitsSold,
       (SELECT STOCKS.UnitsStock
        FROM STOCKS WITH(NOLOCK)
        WHERE STOCKS.CodProduct = SELLS.CodProduct
        AND STOCKS.Color = SELLS.Color AND STOCKS.Number = SELLS.Number) As Stock
FROM SELLS WITH(NOLOCK)
JOIN PRODUCTS WITH (NOLOCK) ON PRODUCTS.CodProduct = SELLS.CodProduct
WHERE PRODUCTS.Family = 05 AND SELLS.CodProducto = 'AL4301'
GROUP BY DatePart(mm, SELLS.DateSold), SELLS.CodProduct, SELLS.DescProduct, SELLS.Color, SELLS.Number
ORDER BY SELLS.CodProduct, SELLS.Color, SELLS.Number

With this query I obtain this:

Query result

But I would need to get this:

Query objetive

Thanks to @Gordon Linoff, I get solution. Finally I used this:

SELECT ST.CODPRODUCT, ST.DESPRODUCT, ST.COLOR, ST.NUMBER,
       P.FAMILY, ST.UNITSSTOCK STOCK, SLL.MONTH, SLL.UNITSSOLD       
FROM STOCKS ST
JOIN PRODUCTS P ON P.CODPRODUCT = ST.CODPRODUCT
LEFT OUTER JOIN
        (SELECT SL.CODPRODUCT, SL.COLOR, SL.NUMBER,
                DatePart(mm, SL.DATESOLD) MONTH,
                SUM(SL.UNITSSOLD) UNITSSOLD
         FROM SELLS SL
         GROUP BY SL.CODPRODUCT, DatePart(mm, SL.DATESOLD),
                  SL.COLOR, SL.NUMBER) SLL
ON SLL.CODPRODUCT = ST.CODPRODUCT
   AND SLL.COLOR = ST.COLOR
   AND SLL.NUMBER = ST.NUMBER
ORDER BY ST.CODPRODUCT, ST.COLOR, ST.NUMBER
antoine
  • 5
  • 2
  • I added the SQL Server tag based on the use of `NOLOCK`. There is no reason to use `NOLOCK` -- unless you really, really know what you are doing. But it suggests SQL Server. – Gordon Linoff Sep 26 '20 at 13:52
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Sep 26 '20 at 22:35
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Sep 26 '20 at 22:35
  • [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/q/3969452/3404097) – philipxy Sep 26 '20 at 22:37
  • Thanks for your note. It is in fact a query for sql server, specifically for an ERP, sorry for not mentioning it at the beginning. I have used "NOLOCK" because in all the queries I have seen from the ERP they always use this parameter. I wasn't 100% sure if I should use it. – antoine Sep 26 '20 at 22:41
  • Please clarify via edits, not comments. See [How do comment @replies work?](https://meta.stackexchange.com/questions/43019/how-do-comment-replies-work) to learn to use @x to notify one non-sole non-poster commenter x re a comment. Please don't just dump code that is wrong. Besides lacking research it makes a question that is of no use to anyone else. Please act on my earlier comments. – philipxy Sep 26 '20 at 22:51
  • @philipxy I have edited my question for include that I was working with SQL Server, title for tables and I wrote code with query I executed, its results and what I desired to get. – antoine Sep 27 '20 at 16:29

1 Answers1

2

Use a cross join to generate all combinations of stock and months. Then use left join and aggregation:

select s.codproduct, s.color, s.number, year(v.month), month(v.month),
       s.unitsstock, sum(sa.unitssold)
from stocks s cross join
     (values ('2020-09-01')) v(month) left join
     sales sa
     on s.codproduct = sa.codproduct and
        s.color = sa.color and
        s.number = sa.number and
        sa.datesold >= v.month and
        sa.datesold < dateadd(month, 1, v.month)
group by s.codproduct, s.color, s.number, year(v.month), month(v.month),
         s.unitsstock;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786