CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Eraniichan
Eraniichan

Reputation: 121

How to query only rows that will exceed to the max quantity

So this is table A

   +------------+--------+----------+
   | LineNumber | Pallet | Location |
   +------------+--------+----------+
   | 1          | a      | X        |
   +------------+--------+----------+
   | 2          | a      | X        |
   +------------+--------+----------+
   | 3          | b      | Y        |
   +------------+--------+----------+
   | 4          | b      | Y        |
   +------------+--------+----------+
   | 5          | b      | Y        |
   +------------+--------+----------+
   | 6          | c      | Z        |
   +------------+--------+----------+
   | 7          | c      | Z        |
   +------------+--------+----------+
   | 8          | c      | Z        |
   +------------+--------+----------+
   | 9          | d      | Q        |
   +------------+--------+----------+
   | 10         | d      | Q        |
   +------------+--------+----------+

and this is table b

   +-------------+----------+
   | MaxPalCount | Location |
   +-------------+----------+
   | 2           | X        |
   +-------------+----------+
   | 2           | Y        |
   +-------------+----------+
   | 2           | Z        |
   +-------------+----------+
   | 2           | Q        |
   +-------------+----------+

What I want to show is linenumbers that will exceed to the maxpalcount per location. So for example line 3 4 5 will go to location Y but as you see the max pal count of the location is only 2. And same goes to line 6 7 8.

So the query result that I want would be something like this.

   +------------+--------+----------+
   | LineNumber | Pallet | Location |
   +------------+--------+----------+
   | 5          | b      | Y        |
   +------------+--------+----------+
   | 8          | c      | Z        |
   +------------+--------+----------+

The last line that will exceed will be shown. I know it is possible to do this with fetching per row. But is it possible without using any looping method?

Upvotes: 1

Views: 89

Answers (3)

Zhang Peng
Zhang Peng

Reputation: 61

Same data as @Backs

;WITH cte AS 
(
    SELECT 
        *,
        ROW_NUMBER()OVER (PARTITION BY t.Location ORDER BY t.LineNumber) AS RowId
    FROM @Test1 t
)
SELECT t.LineNumber, t.Pallet, t.Location FROM cte t
INNER JOIN @Tsst2 t2 ON t.Location = t2.Location
WHERE t.RowId = t2.MaxPalCount + 1

Upvotes: 0

KumarHarsh
KumarHarsh

Reputation: 5094

my dummy data are same as @Backs.

Also i think it should partition on Location.

I have use exists clause instead of inner join.

;WITH Cte
AS (
    SELECT LineNumber
        ,Pallet
        ,Location
        ,ROW_NUMBER() OVER (
            PARTITION BY t.Location ORDER BY t.LineNumber
            ) AS RowId
    FROM @Test1 t
    )
SELECT *
FROM CTE C
WHERE EXISTS (
        SELECT MaxPalCount
        FROM @Tsst2 t1
        WHERE t1.location = c.Location
            AND c.rowid > t1.MaxPalCount
        )

Upvotes: 1

Backs
Backs

Reputation: 24913

DECLARE @Test1 TABLE (
    LineNumber int,
    Pallet nvarchar(10),
    Location nvarchar(10)
)

DECLARE @Tsst2 TABLE (
    MaxPalCount int,
    Location nvarchar(10)
)

INSERT INTO @Test1
(LineNumber,Pallet,Location)
VALUES
(1, 'a', 'X'),
(2, 'a', 'X'),
(3, 'b', 'Y'),
(4, 'b', 'Y'),
(5, 'b', 'Y'),
(6, 'c', 'Z'),
(7, 'c', 'Z'),
(8, 'c', 'Z'),
(9, 'd', 'Q'),
(10, 'd', 'Q')

INSERT INTO @Tsst2
(MaxPalCount, Location)
VALUES
(2, 'X'),
(2, 'Y'),
(2, 'Z'),
(2, 'Q')


SELECT *
FROM
(
    SELECT 
        *,
        ROW_NUMBER()OVER (PARTITION BY t.Pallet ORDER BY t.LineNumber) AS RowId
    FROM @Test1 t
) AS t
INNER JOIN @Tsst2 t2 ON t.Location = t2.Location
WHERE t.RowId > t2.MaxPalCount

Upvotes: 5

Related Questions