I am counting the records for a specific project Value (cprj) from the table [MatchingRowsOutput-CI-BOM-ProjectCumulative] and cross-referencing the number to a [DWRowCount] value in another table, [OrderEntry], for the same project 'cprj'.
If these values match, I would like to take all the records for that project (cprj) from the [MatchingRowsOutput-CI-BOM-ProjectCumulative] table and output them into another table called [CI-BM-CPRJ-Transfer].
I have tried many different methods, trying to use CASE clause, WHERE clause, HAVING clause but none of these have worked.
The code pasted below is the most successful that I can achieve, returning 0 rows affected. I know for a fact there should be a return of 26 rows for the TEK#7000 cprj.
Also, after I have pulled the records from the [MatchingRowsOutput-CI-BOM-ProjectCumulative] table and placed into the [CI-BM-CPRJ-Transfer] table I would like to delete the records that was used for the reference
out of the [OrderEntry] table. I have not tried to code that part yet in the code below.
Any help is much appreciated.
Here are samples of the 2 tables with the information.
[OrderEntry] Table
ID cprj TotalRowCount DWRowCount
1 TEK#7000 80 26
2 TEK#4444 50 30
3 TEK#88888 120 24
4 TEK#4700 37 13
5 TEK#99999 53 42
[MatchingRowsOutput-CI-BOM-ProjectCumulative] table
ID cprj item PartNumber SWITEM QTY rutm
196 TEK#7000 A23-3171-X A23-3171\2583 1.2 2 NULL
197 TEK#5555 N02-0846-X N02-0846A5\59\2583 2 1 NULL
198 TEK#7000 N02-0846-X N02-0846A5\59\2583 2 1 NULL
199 TEK#5555 A23-1354-X A23-1354\2583 2.3 2 NULL
200 TEK#7000 A23-1354-X A23-1354\2583 2.3 2 NULL
201 TEK#7000 B11-0304-X B11-0304\25 2.4 2 NULL
202 TEK#7000 B11-0304-X B11-0304\25 1.4 2 NULL
203 TEK#5555 B11-0304-X B11-0304\25 2.4 2 NULL
204 TEK#5555 B11-0304-X B11-0304\25 1.4 2 NULL
205 TEK#5555 B11-0305-X B11-0305\59 1.5 2 NULL
206 TEK#5555 B11-0305-X B11-0305\59 2.5 2 NULL
207 TEK#7000 B11-0305-X B11-0305\59 1.5 2 NULL
I could not show all the columns for this 2nd table.
[CI-BM-CPRJ-Transfer] is a new table that the results would be written into.
What I have tried:
Insertinto SSIS.dbo.[CI-BM-CPRJ-Transfer](cprj, item, PartNumber, SWITEM, QTY, rutm, trid, mitm, pono, sitm, opol, qana, scpf, cwar, opno, cpha, exin, itlu, ssta, dsca, dscb, dscd, suno, ctyo, eitm)
SELECT
PartList.[cprj],
XMLLines.[item],
XMLLines.[PartNumber],
XMLLines.[SWITEM],
XMLLines.[QTY],
XMLLines.[rutm],
XMLLines.[trid],
XMLLines.[mitm],
XMLLines.[pono],
XMLLines.[sitm],
XMLLines.[opol],
XMLLines.[qana],
XMLLines.[scpf],
XMLLines.[cwar],
XMLLines.[opno],
XMLLines.[cpha],
XMLLines.[exin],
XMLLines.[itlu],
XMLLines.[ssta],
XMLLines.[dsca],
XMLLines.[dscb],
XMLLines.[dscd],
XMLLines.[suno],
XMLLines.[ctyo],
XMLLines.[eitm]
FROM
OrderEntry.dbo.OrderEntry as PartList
INNERJOIN
SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative]AS XMLLines ON
XMLLines.cprj = PartList.cprj
(SELECT cprj FROM SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative])GroupBy XMLLines.cprj, PartList.DWRowCount, XMLLines.[item], XMLLines.[PartNumber], XMLLines.[SWITEM], XMLLines.[QTY], XMLLines.[rutm], XMLLines.[trid], XMLLines.[mitm], XMLLines.[pono], XMLLines.[sitm],
XMLLines.[opol], XMLLines.[qana], XMLLines.[scpf], XMLLines.[cwar], XMLLines.[opno], XMLLines.[cpha], XMLLines.[exin], XMLLines.[itlu], XMLLines.[ssta], XMLLines.[dsca], XMLLines.[dscb], XMLLines.[dscd],
XMLLines.[suno], XMLLines.[ctyo], XMLLines.[eitm], PartList.[cprj]
Having count(XMLLines.cprj)= PartList.DWRowCount