CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-server-2017
Rookie
Rookie

Reputation: 11

Select distinct values from one table and join with another table

I have a problem that I've spent way to much time trying to figure out, with close to no success at all.. I'll try to describe the problem as good as I can, and use an example, which is the solution I use right now.

I have two different MS SQL tables.

Table 1:

Table 2:

So what I want to do is to get DISTINCT itemNumber between two dates from Table 1. Once I have those item numbers, I would like to join Table 2 on item number, and also between the same dates that I use in the query for Table 1. I also need to only get the values from Table 2 where MTTYP = 11 and MTTRQT < 0 and SUM MTTRQT.

I've sorted this by using loops in java code, which isn't that good to be honest. What I do is this:

  1. SELECT DISTINCT itemNumber "itemNumber"
    FROM Table 1
    WHERE date BETWEEN @fromDate AND @toDate;

  2. Take the top value from this result (that is the first item number) and then:

  3. SELECT Sum(MTTRQT) "SUM_MTTRQT_"
    FROM Table 2
    WHERE MTITNO = "the first item number from the result query from above"
    AND MTTTYP = 11
    AND MTTRDT BETWEEN @fromDate AND @toDate
    AND MITTRA.MTTRQT < 0

  4. Add the result to a new list. Remove the item number used

  5. Loop through all the item numbers in the list and run step 3 and 4 for every single item number (this is the bad part).

Surely there must be a SQL query that produces the same result!?

Appreciate any help I can get!

Update: This is the data I have.
Table 1

|Item number | Quantity | date
 192031      |    1     | 20190521
 192031      |    1     | 20190522
 19192301    |    2     | 20190521
 19189507    |    1     | 20190523
 19189507    |    1     | 20190521
 19189507    |    1     | 20190524

Table 2

|MTITNO    | MTTRDT   | MTTTYP | MTTRQT
 192031    | 20190520 | 11     | -1
 192031    | 20190520 | 11     | -1
 192031    | 20190520 | 11     | -1
 192031    | 20190520 | 11     | -1
 19189507  | 20190520 | 11     | -1
 19189507  | 20190520 | 11     | -1
 19189507  | 20190520 | 11     | -1
 19189507  | 20190520 | 11     | -1
 19189507  | 20190521 | 11     | -1
 19189507  | 20190521 | 11     | -1
 19189507  | 20190521 | 11     | -1

Table 2 contains all sorts of item numbers (that is item numbers that you can find in Table 2, but not in Table 1), and many more posts. There can be posts in Table 1 and no posts in Table 2 for one or more item numbers.

I want to summarise the MTTRQT for all items where the item number is in both Table 1 and Table 2 and within the date span I have set. The "amount used" in the desired result below is MTTRQT added up for every single item number.

Desired result
So if I look for all the item numbers with date between 20190520 - 20190524, I should get the list below.
"Item number" is supposed to be DISTINCT item numbers from Table 1.

"Amount used" is the SUM function, that sums MTTRQT where all the conditions are met. 

|Item Number | Amount used
 192031      | -4     
 19189507    | -7

Upvotes: 1

Views: 4786

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If I am following your logic correctly:

select sum(mttrqt)
from table2 t2
where t2.mtitno in (select t1.itemno
                    from table1 t1
                    where t1.date >= @date1 and t1.date <= @date2
                   ) and
      t2.mttrdt >= @date1 and
      t2.mttrdt <= @date1 and
      t2.mttype = 11 and
      t2.mttrqt < 10;

Upvotes: 1

Thom A
Thom A

Reputation: 95554

Reading through the lines a bit, but is this not what you're after?

SELECT SUM(T2.MTTRQT) AS [SUM_MTTRQT_]
FROM [Table 2] T2
     LEFT JOIN (SELECT TOP (1)
                       T1.ItemNumber
                FROM [Table 1] T1
                WHERE T1.[date] BETWEEN @fromDate AND @toDate --Note, if [date] has a time portion, this is unlikely to work as you expect
                ORDER BY T1.ItemNumber) T1 ON T2.MTITNO = T1.ItemNumber --Assumed ORDER BY clause
WHERE T2.MTTTYP = 11
  AND T2.MTTRDT BETWEEN @fromDate AND @toDate --Note, if MTTRDT has a time portion, this is unlikely to work as you expect
  AND T2.MITTRA.MTTRQT < 0;

Upvotes: 1

zip
zip

Reputation: 4061

Have you tried this:

SELECT Sum(MTTRQT) "SUM_MTTRQT_"
FROM Table 2
WHERE MTITNO in (SELECT DISTINCT itemNumber "itemNumber"
FROM Table 1
WHERE date BETWEEN @fromDate AND @toDate;)
AND MTTTYP = 11
AND MTTRDT BETWEEN @fromDate AND @toDate
AND MITTRA.MTTRQT < 0

Upvotes: 0

Related Questions