• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

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
0
razza_b
Asked:
razza_b
  • 2
  • 2
1 Solution
 
razza_bAuthor Commented:
and...another example

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.
0
 
PortletPaulCommented:
Job, Component, ReqQty   :: table1
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

Open in new window

0
 
razza_bAuthor Commented:
Thanks

if i where to pass in a param @JOB into my SP how would i then filter it out?
0
 
PortletPaulCommented:
you would just add a where clause that require the data to be for that job/WORKORDER

between lines 12 & 13

and amend the existing where clause to include that logic

this I don't need to do for you I hope
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now