razza_b
asked on
sql table1 rows Vs table2 rows
Hi
need help on a sql query. my problem is i have table (table1) that has say 3 rows (3 col's) and i have this other table (table2) that is empty, but only gets filled up when i enter data into it and the data i do enter into it matches the data that is in table 1.
table 1
----------
Job Component ReqQty
1. test1, comp1, 10
2. test2, comp2, 10
3. test3, comp3, 10
when i enter in the data to this table2 below, comp1 can have one insertion with a qty of 10, comp2 can have 2 insertions each with a qty of 5 making table2 have 3 rows - now is my problem, when i run my query it sees that there are 3 rows in each table and i havnt scanned in comp3, can someone help me amend my query or think of a better way to achieve this?
as i want this select statement to bring me back row 3 in table1(test3, comp3, 10), so i know that i have data to scan into my table2. so effectivley table2 can grow more rows, but based on qty in table1 over table2 i can still bring back table1 data that has not be entered into table2.
Table2
---------
1. test1, comp1, 10
2. test2, comp2, 5
3. test2, comp2, 5
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
select row_number() over (partition by Job,Component ORDER BY ReqQty) rownum, *
from table1 WITH (NOLOCK)) TB
LEFT JOIN (
select row_number() over (partition by WORKORDER,PARTNUMBER ORDER BY QTY) rownum, *
from table2) CKV ON ((TB.Job = CKV.[WORKORDER])
AND TB.Component = CKV.[PARTNUMBER])
AND CKV.rownum = TB.rownum
WHERE ((CKV.[WORKORDER] IS NULL)
AND (CKV.[PARTNUMBER]) IS NULL
AND (TB.Job = @JOB))
I am using sql server 2012.
hope makes sense...
Thanks
need help on a sql query. my problem is i have table (table1) that has say 3 rows (3 col's) and i have this other table (table2) that is empty, but only gets filled up when i enter data into it and the data i do enter into it matches the data that is in table 1.
table 1
----------
Job Component ReqQty
1. test1, comp1, 10
2. test2, comp2, 10
3. test3, comp3, 10
when i enter in the data to this table2 below, comp1 can have one insertion with a qty of 10, comp2 can have 2 insertions each with a qty of 5 making table2 have 3 rows - now is my problem, when i run my query it sees that there are 3 rows in each table and i havnt scanned in comp3, can someone help me amend my query or think of a better way to achieve this?
as i want this select statement to bring me back row 3 in table1(test3, comp3, 10), so i know that i have data to scan into my table2. so effectivley table2 can grow more rows, but based on qty in table1 over table2 i can still bring back table1 data that has not be entered into table2.
Table2
---------
1. test1, comp1, 10
2. test2, comp2, 5
3. test2, comp2, 5
SELECT
TB.Job as 'JOB'
, TB.Component as 'SERIAL'
, TB.ReqQty as 'QTY'
FROM (
select row_number() over (partition by Job,Component ORDER BY ReqQty) rownum, *
from table1 WITH (NOLOCK)) TB
LEFT JOIN (
select row_number() over (partition by WORKORDER,PARTNUMBER ORDER BY QTY) rownum, *
from table2) CKV ON ((TB.Job = CKV.[WORKORDER])
AND TB.Component = CKV.[PARTNUMBER])
AND CKV.rownum = TB.rownum
WHERE ((CKV.[WORKORDER] IS NULL)
AND (CKV.[PARTNUMBER]) IS NULL
AND (TB.Job = @JOB))
I am using sql server 2012.
hope makes sense...
Thanks
Job, Component, ReqQty :: table1
WORKORDER, PARTNUMBER, QTY :: table2
and you want to compare table1.ReqQty to SUM(table2.QTY)
WORKORDER, PARTNUMBER, QTY :: table2
and you want to compare table1.ReqQty to SUM(table2.QTY)
SELECT
Job
, Component
, ReqQty
, sum_qty
FROM table1
LEFT JOIN (
SELECT
WORKORDER
, PARTNUMBER
, SUM(QTY) AS sum_qty
FROM table2
GROUP BY
WORKORDER
, PARTNUMBER
) AS t2
ON table1.Job = t2.WORKORDER
AND table1.Component = t2.PARTNUMBER
WHERE t2.sum_qty <> table1.ReqQty
OR t2.WORKORDER IS NULL
ORDER BY
Job
, Component
ASKER
Thanks
if i where to pass in a param @JOB into my SP how would i then filter it out?
if i where to pass in a param @JOB into my SP how would i then filter it out?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
if i have this below with data entered into table2, i still need to bring back comp2 from table1 as there is still qty to be inserted into table2
Table2
---------
1. test1, comp1, 10
2. test2, comp2, 5 <--- still to enter a qty of 5
3. test3, comp3, 10
i should also mention that i can enter in data into table2 with a qty greater than or equal to reqQty. and if lower than the reqQty then thats how multi rows happen but as long as it reaches it reqQty.